SQL date functions: DATE_TRUNC, EXTRACT and intervals

#Why it matters

Almost every business question has a date in it: orders per week, revenue this quarter, days to deliver, customers active last month. SQL date functions are where analysts lose the most silent accuracy, because a wrong week definition, a time zone or a BETWEEN on a timestamp gives numbers that look perfectly fine. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and date logic is in most of the SQL interview questions that follow. This lesson covers the functions and, more importantly, the traps.

#The concept

#Truncating and extracting

• DATE_TRUNC('month', ts) rounds a timestamp down to the start of its month (or 'day', 'week', 'quarter', 'year', 'hour'). Use it to group: every order in October 2017 gets 2017-10-01 00:00:00.

• EXTRACT(part FROM ts) pulls one number out: year, month, day, hour, dow, isodow, week, isoyear, quarter, epoch.

• TO_CHAR(ts, 'Dy DD Mon YYYY') formats for display. Format for people at the very end, never before grouping or sorting.

The names change by database:

#Differences: durations vs boundaries

There are two different questions hiding in "how many days between":

• Elapsed time: subtract timestamps to get an interval (8 days 10:28:40), then convert with EXTRACT(epoch FROM interval) / 86400 for fractional days.

• Calendar days crossed: cast both to dates and subtract. An order placed at 23:55 and delivered at 00:05 is 0.007 days but 1 calendar day.

SQL Server's DATEDIFF always counts boundaries crossed, not full periods: DATEDIFF(year, '2017-12-31', '2018-01-01') returns 1, although only one day passed. DATEDIFF(month, ...) has the same behaviour. Know which one your database does before you report "customer age in years".

Month arithmetic has its own edge: adding one month to 31 January gives 28 February, and AGE() in PostgreSQL returns years, months and days, not a number of days.

#Weeks: ISO and the rest

• ISO 8601 weeks start on Monday, and week 1 is the week containing the first Thursday of the year. So 1 January can belong to week 52 or 53 of the previous ISO year. Pair EXTRACT(week ...) with EXTRACT(isoyear ...), never with the calendar year.

• EXTRACT(dow ...) numbers Sunday as 0 (a US convention); EXTRACT(isodow ...) numbers Monday 1 to Sunday 7.

• DATE_TRUNC('week', ts) in PostgreSQL and DuckDB gives the Monday. MySQL's WEEK() and SQL Server's DATEPART(week, ...) default to Sunday-start weeks, which differ from ISO; use WEEK(d, 3) and DATEPART(iso_week, d) for ISO.

#Fiscal years

Indian companies report on an April to March financial year: FY2017-18 runs from 1 April 2017 to 31 March 2018. The trick is to shift the date back three months and then use normal calendar functions: EXTRACT(quarter FROM ts - INTERVAL '3 months') gives the fiscal quarter, and the year of the shifted date is the year the fiscal year starts.

#Time zones

• timestamp (without time zone) is a wall-clock reading with no zone attached. timestamptz stores an absolute instant.

• ts AT TIME ZONE 'America/Sao_Paulo' says "this wall-clock time was in São Paulo" and turns it into an instant; instant AT TIME ZONE 'Asia/Kolkata' shows that instant on an Indian clock.

• Use named zones (Asia/Kolkata), not fixed offsets (+05:30), so daylight saving rules elsewhere are applied for you. India has no daylight saving, but many source systems do.

• Store in UTC, convert at the edges, and convert before you group by day or hour, because the date itself can change.

#Parsing strings into dates

TO_DATE / TO_TIMESTAMP in PostgreSQL and Oracle, STR_TO_DATE in MySQL, PARSE_DATE in BigQuery, STRPTIME in DuckDB. Always give the format: 05/03/2011 is 5 March in India and the UK and 3 May in the US.

#Half-open ranges

BETWEEN '2017-11-01' AND '2017-11-30' on a timestamp means "up to 30 November at 00:00:00", so almost all of the last day is lost. Use >= start AND < next_start for every timestamp range. It also works for any granularity and never double counts a boundary.

#Calendars for missing days

GROUP BY day only returns days that have rows. Charts and moving averages need every day, so generate a calendar with generate_series (PostgreSQL, DuckDB), a numbers table, or GENERATE_DATE_ARRAY (BigQuery), and left join the data to it.

#Worked example: two years of Brazilian marketplace orders

The Olist dataset has 99,441 real orders from a Brazilian marketplace (September 2016 to October 2018, licensed CC BY-NC-SA 4.0) with purchase, delivery and promised dates. Timestamps are local São Paulo time. Everything below ran on PostgreSQL 16.

CREATE TABLE orders (
    order_id text PRIMARY KEY, customer_id text, order_status text,
    order_purchase_timestamp timestamp, order_approved_at timestamp,
    order_delivered_carrier_date timestamp, order_delivered_customer_date timestamp,
    order_estimated_delivery_date timestamp
);
\copy orders FROM 'olist_orders_dataset.csv' WITH (FORMAT csv, HEADER true)
SELECT count(*), min(order_purchase_timestamp), max(order_purchase_timestamp) FROM orders;

 count |         min         |         max
-------+---------------------+---------------------
 99441 | 2016-09-04 21:15:19 | 2018-10-17 17:30:18

#Step 1: truncating, extracting, formatting

SELECT order_purchase_timestamp AS ts,
       date_trunc('month', order_purchase_timestamp) AS month_start,
       date_trunc('week', order_purchase_timestamp)  AS week_start,
       extract(year FROM order_purchase_timestamp)   AS yr,
       extract(dow FROM order_purchase_timestamp)    AS dow,
       extract(isodow FROM order_purchase_timestamp) AS isodow,
       to_char(order_purchase_timestamp, 'Dy DD Mon YYYY HH24:MI') AS label
FROM orders
WHERE order_id = 'e481f51cbdc54678b7cc49136f2d6af7';

         ts          |     month_start     |     week_start      |  yr  | dow | isodow |         label
---------------------+---------------------+---------------------+------+-----+--------+-----------------------
 2017-10-02 10:56:33 | 2017-10-01 00:00:00 | 2017-10-02 00:00:00 | 2017 |   1 |      1 | Mon 02 Oct 2017 10:56

This order was placed on a Monday, so its week starts the same day. date_trunc keeps the type (a timestamp), which sorts and joins correctly; to_char returns text, which is for display only.

#Step 2: ISO weeks at the year boundary

SELECT d::date AS day, to_char(d, 'Dy') AS weekday,
       extract(dow FROM d) AS dow, extract(isodow FROM d) AS isodow,
       extract(week FROM d) AS iso_week, extract(isoyear FROM d) AS iso_year,
       extract(year FROM d) AS calendar_year
FROM generate_series('2016-12-30'::date, '2017-01-02'::date, interval '1 day') AS g(d);

    day     | weekday | dow | isodow | iso_week | iso_year | calendar_year
------------+---------+-----+--------+----------+----------+---------------
 2016-12-30 | Fri     |   5 |      5 |       52 |     2016 |          2016
 2016-12-31 | Sat     |   6 |      6 |       52 |     2016 |          2016
 2017-01-01 | Sun     |   0 |      7 |       52 |     2016 |          2017
 2017-01-02 | Mon     |   1 |      1 |        1 |     2017 |          2017

1 January 2017 is a Sunday, so it is the last day of ISO week 52 of 2016. Group by (calendar_year, iso_week) and that Sunday becomes a fake "week 52 of 2017" with one day in it. dow calls Sunday 0, isodow calls it 7: pick one and say which.

Orders by weekday for 2017, and by ISO week around Black Friday:

SELECT extract(isodow FROM order_purchase_timestamp) AS isodow,
       to_char(order_purchase_timestamp, 'Dy') AS weekday,
       count(*) AS orders
FROM orders
WHERE order_purchase_timestamp >= '2017-01-01' AND order_purchase_timestamp < '2018-01-01'
GROUP BY 1, 2
ORDER BY 1;

SELECT extract(isoyear FROM order_purchase_timestamp) AS iso_year,
       extract(week FROM order_purchase_timestamp) AS iso_week,
       min(order_purchase_timestamp)::date AS first_order_day,
       count(*) AS orders
FROM orders
WHERE order_purchase_timestamp >= '2017-11-13' AND order_purchase_timestamp < '2017-12-11'
GROUP BY 1, 2
ORDER BY 1, 2;

 isodow | weekday | orders
--------+---------+--------
      1 | Mon     |   7188
      2 | Tue     |   7170
      3 | Wed     |   6775
      4 | Thu     |   6516
      5 | Fri     |   6915
      6 | Sat     |   5051
      7 | Sun     |   5486
 
 iso_year | iso_week | first_order_day | orders
----------+----------+-----------------+--------
     2017 |       46 | 2017-11-13      |   1313
     2017 |       47 | 2017-11-20      |   3008
     2017 |       48 | 2017-11-27      |   2098
     2017 |       49 | 2017-12-04      |   1692

Ordering by isodow gives Monday to Sunday; ordering by the weekday name would sort alphabetically (Fri, Mon, Sat...). Weekends are about 25% quieter than Mondays. ISO week 47 contains Black Friday (24 November 2017) and has more than double the week before.

#Step 3: durations, intervals and month edges

SELECT order_purchase_timestamp AS purchased,
       order_delivered_customer_date AS delivered,
       order_delivered_customer_date - order_purchase_timestamp AS took,
       round(extract(epoch FROM order_delivered_customer_date - order_purchase_timestamp) / 86400, 2) AS days_exact,
       order_delivered_customer_date::date - order_purchase_timestamp::date AS calendar_days,
       order_estimated_delivery_date AS promised,
       order_purchase_timestamp + interval '7 days' AS plus_7_days
FROM orders
WHERE order_id = 'e481f51cbdc54678b7cc49136f2d6af7';

SELECT date '2018-01-31' + interval '1 month' AS jan31_plus_month,
       date '2018-03-31' - interval '1 month' AS mar31_minus_month,
       age(date '2018-03-01', date '2017-12-31') AS age_result;

      purchased      |      delivered      |      took       | days_exact | calendar_days |      promised       |     plus_7_days
---------------------+---------------------+-----------------+------------+---------------+---------------------+---------------------
 2017-10-02 10:56:33 | 2017-10-10 21:25:13 | 8 days 10:28:40 |       8.44 |             8 | 2017-10-18 00:00:00 | 2017-10-09 10:56:33
 
  jan31_plus_month   |  mar31_minus_month  |  age_result
---------------------+---------------------+--------------
 2018-02-28 00:00:00 | 2018-02-28 00:00:00 | 2 mons 1 day

• Subtracting timestamps gives an interval; extract(epoch ...) turns it into seconds, and dividing by 86,400 gives 8.44 days. Subtracting dates gives a whole number of calendar days (8).

• The order arrived 8 days before the promised date: a comparison you can only make with both columns at the same precision.

• Month arithmetic clamps to the last day of the month: 31 January plus a month is 28 February, and so is 31 March minus a month. Adding a month and subtracting it again does not always return the starting date.

• age() gives "2 mons 1 day", a calendar description, not a count of days.

#Step 4: the BETWEEN trap

SELECT count(*) FILTER (WHERE order_purchase_timestamp BETWEEN '2017-11-24' AND '2017-11-24') AS between_same_day,
       count(*) FILTER (WHERE order_purchase_timestamp::date = '2017-11-24') AS cast_to_date,
       count(*) FILTER (WHERE order_purchase_timestamp >= '2017-11-24'
                          AND order_purchase_timestamp <  '2017-11-25') AS half_open
FROM orders;

SELECT count(*) FILTER (WHERE order_purchase_timestamp BETWEEN '2017-11-01' AND '2017-11-30') AS between_nov,
       count(*) FILTER (WHERE order_purchase_timestamp >= '2017-11-01'
                          AND order_purchase_timestamp <  '2017-12-01') AS half_open_nov
FROM orders;

 between_same_day | cast_to_date | half_open
------------------+--------------+-----------
                0 |         1176 |      1176
 
 between_nov | half_open_nov
-------------+---------------
        7277 |          7544

BETWEEN '2017-11-24' AND '2017-11-24' found zero Black Friday orders, because it only matches 00:00:00 exactly. For the whole of November, BETWEEN lost 267 orders, everything placed on 30 November after midnight. Casting to date works but stops an index on the timestamp from being used; the half-open range is correct and fast.

#Step 5: São Paulo time to Indian time

Suppose an Indian team reviews these orders on IST dashboards:

SELECT order_purchase_timestamp AS sao_paulo_local,
       (order_purchase_timestamp AT TIME ZONE 'America/Sao_Paulo') AT TIME ZONE 'UTC' AS utc,
       (order_purchase_timestamp AT TIME ZONE 'America/Sao_Paulo') AT TIME ZONE 'Asia/Kolkata' AS india_time,
       ((order_purchase_timestamp AT TIME ZONE 'America/Sao_Paulo') AT TIME ZONE 'Asia/Kolkata')
         - order_purchase_timestamp AS india_ahead_by
FROM orders
WHERE order_id IN ('e481f51cbdc54678b7cc49136f2d6af7', '00137e170939bba5a3134e2386413108')
ORDER BY 1;

   sao_paulo_local   |         utc         |     india_time      | india_ahead_by
---------------------+---------------------+---------------------+----------------
 2017-10-02 10:56:33 | 2017-10-02 13:56:33 | 2017-10-02 19:26:33 | 08:30:00
 2017-11-24 16:50:38 | 2017-11-24 18:50:38 | 2017-11-25 00:20:38 | 07:30:00

Two lessons in two rows. First, the gap to India is 8:30 in October but 7:30 in late November, because Brazil used daylight saving time that summer; a hard-coded + interval '8 hours 30 minutes' would be wrong for months of data. Second, the Black Friday order placed at 16:50 on 24 November in Brazil happened on 25 November in India. Group by day after converting, never before.

#Step 6: Indian fiscal quarters

SELECT 'FY' || extract(year FROM order_purchase_timestamp - interval '3 months')
       || '-' || lpad(((extract(year FROM order_purchase_timestamp - interval '3 months') + 1)::int % 100)::text, 2, '0') AS fiscal_year,
       'Q' || extract(quarter FROM order_purchase_timestamp - interval '3 months') AS fiscal_quarter,
       min(order_purchase_timestamp)::date AS first_order,
       count(*) AS orders
FROM orders
WHERE order_purchase_timestamp >= '2017-01-01' AND order_purchase_timestamp < '2018-07-01'
GROUP BY 1, 2
ORDER BY 1, 2;

 fiscal_year | fiscal_quarter | first_order | orders
-------------+----------------+-------------+--------
 FY2016-17   | Q4             | 2017-01-05  |   5262
 FY2017-18   | Q1             | 2017-04-01  |   9349
 FY2017-18   | Q2             | 2017-07-01  |  12642
 FY2017-18   | Q3             | 2017-10-01  |  17848
 FY2017-18   | Q4             | 2018-01-01  |  21208
 FY2018-19   | Q1             | 2018-04-01  |  19979

Shifting back three months turns April into "January", so ordinary year and quarter functions give the fiscal ones. Q1 starts on 1 April and Q4 on 1 January, as an Indian finance team expects. In a real warehouse you would put fiscal year and quarter as columns in a date dimension table instead of repeating this expression.

#Step 7: parsing strings safely

SELECT to_date('05/03/2011', 'DD/MM/YYYY') AS read_as_day_first,
       to_date('05/03/2011', 'MM/DD/YYYY') AS read_as_month_first,
       to_timestamp('12/1/2010 8:26', 'MM/DD/YYYY HH24:MI') AS online_retail_format;

SELECT to_date('13/01/2011', 'MM/DD/YYYY');

 read_as_day_first | read_as_month_first |  online_retail_format
-------------------+---------------------+------------------------
 2011-03-05        | 2011-05-03          | 2010-12-01 08:26:00+00
 
ERROR:  date/time field value out of range: "13/01/2011"

The same string is 5 March or 3 May depending on the format, and nothing warns you when both readings are valid. Only a day above 12 exposes the mistake. Before loading a file, check whether any value in the "day" position is greater than 12, which tells you the real order. The UCI Online Retail export, for example, is month first.

#Step 8: a calendar for missing days

WITH daily AS (
    SELECT order_purchase_timestamp::date AS day, count(*) AS orders
    FROM orders
    GROUP BY 1
),
calendar AS (
    SELECT d::date AS day
    FROM generate_series('2016-12-20'::date, '2017-01-08'::date, interval '1 day') AS g(d)
)
SELECT c.day, to_char(c.day, 'Dy') AS weekday, coalesce(d.orders, 0) AS orders
FROM calendar AS c
LEFT JOIN daily AS d ON d.day = c.day
ORDER BY c.day;

    day     | weekday | orders
------------+---------+--------
 2016-12-20 | Tue     |      0
 2016-12-21 | Wed     |      0
 2016-12-22 | Thu     |      0
 2016-12-23 | Fri     |      1
 2016-12-24 | Sat     |      0
 2016-12-25 | Sun     |      0
 2016-12-26 | Mon     |      0
 2016-12-27 | Tue     |      0
 2016-12-28 | Wed     |      0
 2016-12-29 | Thu     |      0
 2016-12-30 | Fri     |      0
 2016-12-31 | Sat     |      0
 2017-01-01 | Sun     |      0
 2017-01-02 | Mon     |      0
 2017-01-03 | Tue     |      0
 2017-01-04 | Wed     |      0
 2017-01-05 | Thu     |     32
 2017-01-06 | Fri     |      4
 2017-01-07 | Sat     |      4
 2017-01-08 | Sun     |      6
(20 rows)

Without the calendar, a daily chart would draw a straight line from 23 December to 5 January as if nothing changed. With it, the gap is visible: the dataset has almost no orders before January 2017, and 140 of its 774 calendar days have no orders at all. That is a fact about the data's coverage, worth stating before anyone trends 2016 against 2017.

#Common mistakes

• BETWEEN on timestamps. Loses the last day. Use >= start AND < next_start.

• Pairing ISO week with calendar year. Use isoyear with week, or DATE_TRUNC('week', ...), which gives a real date.

• Grouping before converting time zones. The date of an event can change. Convert, then truncate.

• Fixed offsets instead of zone names. +08:30 was wrong for Brazil for four months of every year here. Use America/Sao_Paulo.

• Formatting too early. TO_CHAR(ts, 'Mon YYYY') sorts as text: Apr, Aug, Dec... Group and sort on the date, format at the end.

• Trusting DATEDIFF to mean "full periods". In SQL Server it counts boundaries crossed. Compute ages from full dates.

• Parsing without an explicit format. 05/03 is two different dates. Always pass the format, and test with a day above 12.

• Charting days that have rows only. Left join to a calendar so missing days show as zero.

#Interview angle

#Practice task

1. For delivered orders placed from September 2017 to March 2018, show per purchase month: the number delivered, the average delivery time in days (one decimal), and the percentage delivered after the promised date (compare calendar dates).

2. Find the three busiest purchase hours in São Paulo time, then the three busiest hours in India time, and explain why the second list is not just the first plus 8.5 hours.

Done when you can say which month had the worst late-delivery rate and why the hour lists differ.

SELECT to_char(date_trunc('month', order_purchase_timestamp), 'YYYY-MM') AS month,
       count(*) AS delivered,
       round(avg(extract(epoch FROM order_delivered_customer_date - order_purchase_timestamp) / 86400), 1) AS avg_days,
       round(100.0 * count(*) FILTER (WHERE order_delivered_customer_date::date > order_estimated_delivery_date::date)
             / count(*), 1) AS pct_late
FROM orders
WHERE order_status = 'delivered'
  AND order_delivered_customer_date IS NOT NULL
  AND order_purchase_timestamp >= '2017-09-01' AND order_purchase_timestamp < '2018-04-01'
GROUP BY 1
ORDER BY 1;

  month  | delivered | avg_days | pct_late
---------+-----------+----------+----------
 2017-09 |      4150 |     11.9 |      4.4
 2017-10 |      4478 |     11.9 |      4.2
 2017-11 |      7288 |     15.2 |     12.4
 2017-12 |      5513 |     15.4 |      7.5
 2018-01 |      7069 |     14.1 |      5.7
 2018-02 |      6555 |     16.9 |     14.1
 2018-03 |      7003 |     16.3 |     19.0

SELECT extract(hour FROM order_purchase_timestamp) AS sao_paulo_hour,
       count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY orders DESC
LIMIT 3;

SELECT extract(hour FROM (order_purchase_timestamp AT TIME ZONE 'America/Sao_Paulo')
                           AT TIME ZONE 'Asia/Kolkata') AS india_hour,
       count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY orders DESC
LIMIT 3;

 sao_paulo_hour | orders
----------------+--------
             16 |   6675
             11 |   6578
             14 |   6569
 
 india_hour | orders
------------+--------
         23 |   6582
          0 |   6552
         19 |   6551

#Further reading

• PostgreSQL: date/time functions: DATE_TRUNC, EXTRACT fields, AT TIME ZONE and AGE.

• PostgreSQL: date/time types: timestamp vs timestamptz and how zones are stored.

• SQL Server: DATEDIFF: the boundary-counting rule, in Microsoft's words.

• MySQL: date and time functions: WEEK modes, DATE_ADD and STR_TO_DATE.

• Olist dataset: source and CC BY-NC-SA 4.0 licence of the orders data.