SQL — Running Dates in SQL

Have not been active on medium. The last two years of my life have been spent on work, and more work.

The long hours is the result of poor governance and politics. While the lower emphasis on governance and higher politics are within expectations when i made the decision to move to an organization at an earlier stage of data maturity, the long hours isn’t quite so. But hey, GSD (Get Shit Done) is part of staying professional too. We owe it to our reputations to still deliver our best even under the most oppressing and ridiculous circumstances.

On data migration, while it can be scary for some, but it is nothing new for most of us seasoned data professionals who have crossed industries and companies.

In fact, i have lost count of the number of migration jobs in the past. It’s yet another migration job. But hey, if there is no fire, they probably would not need to hire a fireman, right? Haha.

Anyway, keeping the long story short, i recently chance upon ‘senior, experienced’ data colleagues creating a running list of dates via csv uploads to the database; instead of a simple code that can be written in under 5 min. The image below depicts the reaction behind my calm, helpful, diplomatic facade at that moment of discovery.

Image credits at the end

Coupled with some recent email notifications on medium stats … Hence, the sudden motivation for a quick 20 min write-up on this*.

*The SQL used in this article is AWS Athena Presto SQL.

***

Example 01: list of dates (start of the month + 1 month interval)

Let’s create a running list of dates,
using the start of the month,
and incrementing the running list by 1 month’s interval.
Next, it’s having this list running over the period of X months before and after. In this example, let’s keep it as 3 months prior and 3 months after.

The desired output:

To get started, let’s get going with some basic sql of interest.

now()
This returns the date for today 2024–01–03 (YYYY-MM-DD)

date_trunc(‘month’, now())
This returns the start of the month, in this case, 1st Jan 2024, or 2024–01–01 (YYYY-MM-DD)

In this case, there will be 3 variables of interest:
variable_01: period start range — number of month(s). In this example, -3.
variable_02: period end range — number of month(s). In this example, +3.
variable_03: interval, number of month(s), in this example 1 month.

 WITH table_dim_dummy_dates AS 
 (
 SELECT
     CAST(date_column AS DATE) AS date_id
     FROM
     (VALUES
     (SEQUENCE(DATE(DATE_ADD('month', -3, date_trunc('month', NOW())
     )) , 
         DATE(DATE_ADD('month', 3, date_trunc('month', NOW())
         )) , 
         INTERVAL '1' MONTH)
     )
     ) AS t1(date_array)
 CROSS JOIN
 UNNEST(date_array) AS t2(date_column)
 )
 SELECT date_id AS month_id FROM table_dim_dummy_dates

Next, press run. It’s as simple as that.

***

Example 02: list of dates (specific week start date + 1 week interval)

What if we want to create a running list of dates in weeks,
and where the start date is a Saturday,
with a coverage period of 2 weeks prior and 2 weeks after.

Again, basic sql of interest:

now()
Rehashing this. This returns the date for today 2024–01–03 (YYYY-MM-DD).

date_trunc(‘week’, now())
This returns the start of the week, i.e. Monday. In this case, it’s 2024–01–01 (YYYY-MM-DD). We are using Athena presto sql. You might need to tweak this for other sql dialects.

date_add(‘day’,5,date_trunc(‘week’, now()))
Adding 5 days, this returns the Saturday date for the week. 2024–01–06.

date_add(‘day’,-2,date_trunc(‘week’, now()))
Deducting 2 days, this returns the Saturday date from the prior week. 2023–12–30.

day_of_week() or dow()
This returns the day of the week, more for validation purposes.
The value ranges from 1 (Monday) to 7 (Sunday) for presto sql.

To have a running list of dates on a weekly interval, take note of the choice of values in place of the variables introduced in the earlier example.
variable_01: period start range — number of week(s), in days — multiple of 7.
variable_02: period end range — number of week(s), in days — multiple of 7.
variable_03: interval, number of week(s), in days; in this example 7 days.

WITH table_dim_dummy_dates AS 
 (
 SELECT
     CAST(date_column AS DATE) AS date_id
     FROM
     (VALUES
     (SEQUENCE(DATE(DATE_ADD('day', -14, date_add('day',5,date_trunc('week', NOW()))
     )) , 
         DATE(DATE_ADD('day', 14, date_add('day',5,date_trunc('week', NOW()))
         )) , 
         INTERVAL '7' DAY)
     )
     ) AS t1(date_array)
 CROSS JOIN
 UNNEST(date_array) AS t2(date_column)
 )
 SELECT 
 date_id AS start_date_id
 , date_add('day',6,date_id) AS end_date_id
 , day_of_week(date_id) AS start_day_of_week 
 , day_of_week(date_add('day',6,date_id)) AS end_day_of_week 
 FROM table_dim_dummy_dates

Next, press run. It’s as simple as that.

***

Example 03: list of dates + country list (specific week start date + 1 week interval)

what if we want a cross join of the dates with a list of countries?

Here’s how it is done.

WITH table_dim_dummy_dates AS 
 (
 SELECT
     CAST(date_column AS DATE) AS date_id
     , country
     FROM
     (VALUES
     (SEQUENCE(DATE(DATE_ADD('day', -14, date_add('day',5,date_trunc('week', NOW()))
     )) , 
         DATE(DATE_ADD('day', 14, date_add('day',5,date_trunc('week', NOW()))
         )) , 
         INTERVAL '7' DAY)
     )
     ) AS t1(date_array)
 CROSS JOIN
 UNNEST(date_array) AS t2(date_column)
 CROSS JOIN (VALUES ('SG'),('ID')) AS t3(country)
 )
 SELECT
 date_id AS start_date_id
 , date_add('day',6,date_id) AS end_date_id
 , country
 , day_of_week(date_id) AS start_day_of_week 
 , day_of_week(date_add('day',6,date_id)) AS end_day_of_week 
 FROM table_dim_dummy_dates

Again, press run. It’s as simple as that.

***

What about 2 months interval, 3 years interval etc. Feel free to try it out yourself by tweaking the codes. It’s no rocket science. It is easy, yet it’s still terribly hard for many self-proclaimed technical SQL experts.

***

There’s a saying that

If I do a job in 5 minutes it’s because I spent 10 years learning how to do that in 5 minutes. You owe me for the years, not the minutes.

10 years is probably a tad bit overly dramatic in this case. But hey, it still take years to perfect some foundational skills. Some days, those old fundamental knowledge from early career days do come in handy. Of course, that depends on which organization or pool that we are in.

Last, but not least, never be haughty. Don’t be like some of those self-proclaimed experts who basks beneath their coconut shell. And always, always embrace a beginner’s mindset regardless of which career stage we are. There’s always something new for us. That’s how we learn, and progress.

The world never loses its charms when we are a beginner. Isn’t that true in some ways?

References:
https://dragoart.com/tut/how-to-draw-rage-face-rage-face-8933
https://steemit.com/story/@bkkshadow/the-frog-under-the-coconut-shell-a-malay-proverb