Getting a date list in a range in PostgreSQL

sql

prompt-engineering

best-practices

performance

byNikita Barsukov·Oct 20, 2024

In PostgreSQL we can generate_series for creating a date range series. To get daily dates from '2023-01-01' to '2023-01-10', use the following query:

SELECT * FROM generate_series(
  '2023-01-01'::date, 
  '2023-01-10', 
  '1 day'
) AS date_list;

Simple like a Sunday morning, the query pumps out a column of dates for your range, with one row for each day.

The flexibility of generate_series

The generate_series function is about as flexible as a contortionist. You can adapt it to get weekly intervals:

-- A week of goodness coming up!
SELECT generate_series(
  '2023-01-01'::date, 
  '2023-01-31', 
  '1 week'::interval
) AS weekly_list;

Or perhaps you prefer monthly intervals:

-- From zero to hero in a month!
SELECT generate_series(
  '2023-01-01'::date, 
  '2023-12-31', 
  '1 month'::interval
) AS monthly_list;

Just replace '1 day'::interval with any other time unit you need.

Power level: Over 9,000

For more intricate demands, like specific business days or custom sequences, you'd want to consider carving your own persistent date table and conjuring Custom Sequences with CTE. Persistent date tables are like your cup of strong coffee for dealing with complex, repeated operations.

-- Time travelers week ahead
CREATE TABLE date_table AS
SELECT generate_series(
   '2000-01-01'::date,
   '2050-12-31'::date,
   '1 day') AS date;

date_table makes operations like excluding weekends feel as though you're taking a walk in the park:

-- Yes, we're open!
SELECT date
FROM date_table
WHERE EXTRACT('dow' FROM date) NOT IN (0, 6);

For complex queries, a Common Table Expression (CTE) is the shining beacon in your code's structuring needs:

-- My ordered dates bring all the records to the yard
WITH ordered_dates AS (
  SELECT date
  FROM generate_series('2023-01-01'::date, '2023-01-31', '1 day') AS date
)
SELECT * FROM ordered_dates WHERE -- Your amazing conditions here

When data types throw a party

Always confirm if the data types are having a blast together. For timestamp, party like this:

-- Time-capsule ready
SELECT generate_series(
  '2023-01-01 00:00:00'::timestamp,
  '2023-01-01 23:59:59',
  '1 hour'
) AS hourly_list;

Here, the dates and the generate_series get along since they're both timestamps.

Special date ranges, special attention

For special ranges, such as holidays or working days, the query gets an added layer of logic:

-- Now, you see the holidays! Now, you don't!
SELECT date FROM generate_series(
  '2023-01-01'::date, 
  '2023-01-31', 
  '1 day'
) date
WHERE NOT EXISTS (
  SELECT 1 FROM holidays WHERE holiday_date = date
);

This query uses a subselect to excise dates that are holidays.

Time zone gotchas

Time zones and daylight saving changes can mess with date generation. Here's how to deal with that:

SET timezone = 'UTC';
SELECT generate_series(
  '2023-03-01'::timestamp with time zone,
  '2023-04-30',
  '1 day'
) AT TIME ZONE 'America/New_York' AS date_list;

Pop! Time zone problems are no more!

Analysis and performance? Level up!

A persistent dates table can supercharge your analysis and query performance. A caboose to your data analysis train.

-- The perfect brew for comprehensive analysis
SELECT o.order_date, COUNT(o.*)
FROM orders o
INNER JOIN date_table dt ON o.order_date = dt.date
GROUP BY o.order_date;