Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Generating days between two dates

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
March 17, 2021

Generating days between twoย dates

How to generate a row/day using SQL in Oracle Database, along with other date generation tricks such as rows per week, month or year.

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

March 17, 2021
Tweet

Transcript

  1. Ask TOM Office Hours Generating days between two dates How

    to create a row per day in a time period Chris Saxon , Developer Advocate @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL Your SQL Office Hours begins soonโ€ฆ
  2. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Show me orders/day including days with no orders Ryan McGuire / Gratisography
  3. select dt, count ( id ) from dates left join

    orders on โ€ฆ group by dt How to create these? Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  4. Ask TOM Office Hours Generating days between two dates How

    to create a row per day in a time period Chris Saxon, Developer Advocate @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL
  5. select level as N from dual connect by level <=

    :N Returns :N rows Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  6. select date'2021-01-01' + level โ€“ 1 as dt from dual

    connect by level <= :N Note โ€“1! Can be any 1 row table Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  7. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Show a day from oldest โ€“ latest date Ryan McGuire / Gratisography
  8. with rws as ( select min ( dt ) mn_dt,

    max ( dt ) mx_dt from โ€ฆ ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) ) Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  9. with rws as ( select min ( dt ) mn_dt,

    max ( dt ) mx_dt from โ€ฆ ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) ) Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  10. with rws as ( select min ( dt ) mn_dt,

    max ( dt ) mx_dt from โ€ฆ ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  11. with rws as ( select min ( dt ) mn_dt,

    max ( dt ) mx_dt from โ€ฆ ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) Can be any subquery Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  12. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Show every Monday in 2021 Ryan McGuire / Gratisography
  13. with rws as ( select date'2021-01-01' + level - 1

    as dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select * from rws where to_char ( dt, 'FMDay' ) = 'Monday' Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  14. with rws as ( select date'2021-01-01' + level - 1

    as dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select * from rws where to_char ( dt, 'FMDay' ) = 'Monday' Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  15. with rws as ( select date'2021-01-01' + level - 1

    as dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select * from rws where to_char ( dt, 'FMDay' ) = 'Monday' Return 52 Generate 365 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  16. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only select what you need Photo by Gabrielle Henderson on Unsplash You can do better
  17. ๐‘ซ๐’‚๐’š๐’” ๐’Š๐’ ๐’“๐’‚๐’๐’ˆ๐’† = ๐’š๐’†๐’‚๐’“ ๐’†๐’๐’… โˆ’ ๐’‡๐’Š๐’“๐’”๐’• ๐‘ด๐’๐’๐’…๐’‚๐’š ๐‘พ๐’†๐’†๐’Œ๐’”

    ๐’Š๐’ ๐’“๐’‚๐’๐’ˆ๐’† = ๐‘ซ๐’‚๐’š๐’” ๐’Š๐’ ๐’“๐’‚๐’๐’ˆ๐’† ๐Ÿ• + 1 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  18. select next_day ( date'2021-01-01' - 1, 'Monday' ) + (

    level - 1 ) * 7 as dt from dual connect by level <= ( date'2021-12-31' โ€“ next_day ( date'2021-01-01' - 1, 'Monday' ) + 7 ) / 7 NLS_LANGUAGE! Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  19. select next_day ( date'2021-01-01' - 1, 'Monday' ) + (

    level - 1 ) * 7 as dt from dual connect by level <= ( date'2021-12-31' โ€“ next_day ( date'2021-01-01' - 1, 'Monday' ) + 7 ) / 7 Normalize to weeks Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  20. select next_day ( date'2021-01-01' - 1, 'Monday' ) + (

    level - 1 ) * 7 as dt from dual connect by level <= ( date'2021-12-31' โ€“ next_day ( date'2021-01-01' - 1, 'Monday' ) + 7 ) / 7 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  21. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL I want a row/month Ryan McGuire / Gratisography
  22. select add_months ( date'2021-01-01', level - 1 ) as dt

    from dual connect by level <= months_between ( date'2021-12-31', date'2021-01-01' ) + 1 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  23. select add_months ( date'2021-01-01', level - 1 ) as dt

    from dual connect by level <= months_between ( date'2021-12-31', date'2021-01-01' ) + 1 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  24. select add_months ( date'2021-01-01', level - 1 ) as dt

    from dual connect by level <= months_between ( date'2021-12-31', date'2021-01-01' ) + 1 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  25. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL โ€ฆwhat about years? Ryan McGuire / Gratisography
  26. select date'2021-01-01' + numtoyminterval ( level - 1, 'year' )

    as dt from dual connect by level <= ( months_between ( date'2021-12-31', date'2021-01-01' ) / 12 ) + 1 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  27. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Show period start/end Ryan McGuire / Gratisography
  28. Start date End date 15 Jan 2021 31 Jan 2021

    01 Feb 2021 28 Feb 2021 01 Mar 2021 31 Mar 2021 01 Apr 2021 15 Apr 2021 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  29. Start date End date 15 Jan 2021 31 Jan 2021

    01 Feb 2021 28 Feb 2021 01 Mar 2021 31 Mar 2021 01 Apr 2021 15 Apr 2021 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Period end Period start
  30. Start date End date 15 Jan 2021 31 Jan 2021

    01 Feb 2021 28 Feb 2021 01 Mar 2021 31 Mar 2021 01 Apr 2021 15 Apr 2021 Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Normal month start/end
  31. select add_months ( date'2021-01-01', level - 1 ) as dt

    from dual connect by level <= months_between ( date'2021-12-31', date'2021-01-01' ) + 1 with mths as ( ) Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  32. select case rownum when 1 then dt else trunc (

    dt, 'mm' ) end start_date, lead ( trunc ( dt, 'mm' ) - 1, 1, dt ) over ( order by dt ) end_date from mths Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  33. select case rownum when 1 then dt else trunc (

    dt, 'mm' ) end start_date, lead ( trunc ( dt, 'mm' ) - 1, 1, dt ) over ( order by dt ) end_date from mths Start date for 1st row Get month start Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  34. select case rownum when 1 then dt else trunc (

    dt, 'mm' ) end start_date, lead ( trunc ( dt, 'mm' ) - 1, 1, dt ) over ( order by dt ) end_date from mths Get next month Period end for last row Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  35. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Can I reuse that? Ryan McGuire / Gratisography
  36. SQL Macros! Copyright ยฉ 2021 Oracle and/or its affiliates |

    @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  37. create function generate_days ( start_date date, end_date date, day_increment integer

    default 1 ) return varchar2 sql_macro as stmt varchar2(4000); begin โ€ฆ Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  38. stmt := 'select start_date + ( level - 1 )

    * day_increment as dt from dual connect by level <= ( ( ( end_date - start_date ) + day_increment ) / day_increment )'; Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  39. DEMO Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  40. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL & I only want business days Ryan McGuire / Gratisography I'm not on 19c!
  41. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL "a code grinder is going to figure it out, while a database programmer is going to spell it out" - Ken Downs https://database-programmer.blogspot.com/ 2007/11/database-skills-introdution.html
  42. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL create
  43. create table cal_dates ( cal_date date check ( cal_date =

    trunc ( cal_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char), โ€ฆ ) organization index; Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  44. create table cal_dates ( cal_date date check ( cal_date =

    trunc ( cal_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char), โ€ฆ ) organization index; Ensure 1 row/day Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  45. create table cal_dates ( cal_date date check ( cal_date =

    trunc ( cal_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char), โ€ฆ ) organization index; Table is PK index Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  46. DEMO Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  47. Generate ๏ƒผ No objects needed ๏ƒผ Can create any date

    ๏ƒป Rule change = code change Store ๏ƒผ Give control to business ๏ƒผ Better stats ๏ƒป May have dates missing Generate vs store Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  48. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Further reading Live SQL Script https://livesql.oracle.com/apex/livesql/file/content _LIHBDFVF9IUU6AFZB4H6NVLWL.html How to Find the Next Business Day and Add or Subtract N Working Days with SQL https://blogs.oracle.com/sql/how-to-find-the- next-business-day-and-add-or-subtract-n- working-days-with-sql
  49. Generate connect by level < :N Reuse Create SQL macros

    Store Gives business control Ensure you have enough rows! Getting days in range Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  50. Copyright ยฉ 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Ryan McGuire / Gratisography See you soon! asktom.oracle.com #AskTOMOfficeHours