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.

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