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

Generating days between two dates

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.

Chris

March 17, 2021
Tweet

More Decks by Chris

Other Decks in Technology

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