SQL dates and timestamps: filtering and working with time correctly

This lesson continues the shop example from lessons P07–P10. The customers are unchanged. The orders now include the time each order was placed, along with six newer orders from late May and early June. A new deliveries table records shipping and delivery times.

Until §09 introduces local reporting, dates and periods refer to UTC. Result tables show timestamps to the minute because every sample timestamp has zero seconds.

The sample data

customers is the same five-row table used in lesson P07.

In orders, ordered_at replaces the earlier order_date column. It records the order time in UTC.

A few details will matter throughout the lesson:

• Three orders fall on 31 May, including one placed exactly at midnight.
• 2 May 2026 is a Saturday. 3 May and 31 May are Sundays. 1 June is a Monday.
• The shop’s local reports use India Standard Time, five and a half hours ahead of UTC.
• No orders were placed on 2, 4, 6 or 7 June.

While an order happens at a particular moment, their reports usually ask about a larger period: the orders placed today, this week or last month.

Working with time in SQL means organising those moments into days, weeks and months for reporting. The first step is to understand what the column actually stores. The table below shows common SQL date and time data types and what each represents.

These type names are DuckDB and PostgreSQL compatible. Other databases use some type names differently; §13 explains the relevant distinctions.

The shop’s ordered_at, shipped_at and delivered_at columns are plain TIMESTAMP columns. The application writes UTC values into them, but the columns do not record that information. Anyone using them must know this.

A typed literal makes the intended type explicit by placing it before the value:

SELECT
    DATE '2026-05-31'                    AS a_date,
    TIMESTAMP '2026-05-31 15:30:00'       AS a_timestamp,
    CAST(TIMESTAMP '2026-05-31 15:30:00' AS DATE) AS its_date;

Casting this timestamp to DATE removes the time of day. The result keeps only 31 May.

An untyped string such as '2026-05-31' leaves more work to the database. In a comparison such as ordered_at >= '2026-05-01', databases interpret the string as a timestamp because it is being compared with a timestamp column.

Finance asks for every order placed in May. An analyst writes a filter from the first day of May to the last.

SELECT order_id, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2026-05-01' AND '2026-05-31'
ORDER BY order_id;

The same conversion applies to an equality comparison:

SELECT order_id, ordered_at
FROM orders
WHERE ordered_at = '2026-05-31'
ORDER BY order_id;

This requires orders at one exact timestamp: midnight. It does not ask for every order on that date. To include the whole month, use the start of the next month as an upper boundary:

SELECT COUNT(*) AS may_orders
FROM orders
WHERE ordered_at >= '2026-05-01'
  AND ordered_at <  '2026-06-01';

The pattern works cleanly for adjacent periods. May ends where June begins, so an order cannot fall into both months or into a gap between them. Order 1014, placed at 00:20 on 1 June, belongs only to June.

Why not use 23:59:59? Timestamps can contain fractions of a second. DuckDB’s ordinary TIMESTAMP and PostgreSQL timestamps support microseconds. An order at 23:59:59.5 comes after 23:59:59 and would be excluded. Using < '2026-06-01' includes every timestamp before June, regardless of its fractional precision.

You can also compare whole days by converting each timestamp to a date. After CAST(ordered_at AS DATE), all three orders from 31 May have the same date value:

SELECT order_id, ordered_at
FROM orders
WHERE CAST(ordered_at AS DATE) = '2026-05-31'
ORDER BY order_id;

A month filter with BETWEEN now works too, because the comparison is between dates:

SELECT COUNT(*) AS may_orders
FROM orders
WHERE CAST(ordered_at AS DATE) BETWEEN '2026-05-01' AND '2026-05-31';

This query now returns the intended rows. However, on a large table, the next question is how efficiently the database can find them. When a filter compares the original column directly with boundaries, the database may use an index to locate matching rows. It may also skip blocks or partitions whose values fall outside the range. DuckDB, for example, stores minimum and maximum values for groups of rows. A filter on ordered_at can use those summaries to avoid reading irrelevant data. Wrapping the column in a function can make that harder. The database must either evaluate the function or recognise that the condition can be rewritten as a range on the original column.

Note: Some optimisers can turn filters that apply a function to a column into range comparisons on the original column. DuckDB supports this for certain filters using a cast to DATE or date_trunc. SQL Server can also use range searches for certain casts to date. Support depends on the expression, data type and database version.

As a rule of thumb, keep the column unchanged in your filter and compare it directly with the required date or time boundaries.

Finance now wants the number and total value of orders in each month.

date_trunc('month', ordered_at) moves each timestamp back to the start of its month. For example, 2026-05-31 20:10 becomes 2026-05-01 00:00.

Every May order therefore receives the same grouping value:

SELECT
    date_trunc('month', ordered_at) AS month,
    COUNT(*)                       AS orders,
    SUM(amount)                    AS total_amount
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY month;

This query includes all order statuses. The totals represent the value of the orders in each group. For these timestamp inputs, the grouping value is a timestamp at midnight on the first day of the month. It preserves the year and sorts chronologically.

A chart may need a label such as “May 2026.” Common databases, including DuckDB, PostgreSQL and MySQL, can turn a timestamp into a text label, but each uses different syntax:

In DuckDB and MySQL, %b means the abbreviated month name and %Y means the four-digit year. PostgreSQL uses Mon and YYYY for those parts.

An analyst uses that label for both grouping and sorting.

SELECT
    strftime(ordered_at, '%b %Y') AS month_label,
    COUNT(*)                     AS orders
FROM orders
GROUP BY month_label
ORDER BY month_label;

Now, if we keep the timestamp as the grouping and sorting value, then format it for display:

SELECT
    strftime(date_trunc('month', ordered_at), '%b %Y') AS month_label,
    COUNT(*)                                         AS orders
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY date_trunc('month', ordered_at);

Then we get below result table:

The query groups orders by the month’s start date. In SELECT, that date is formatted as a readable label. Labels such as 2026-05, created with '%Y-%m', also sort in date order even though they are text. Here, it works because the year comes first and the month always has two digits, such as 05 or 06.

Marketing wants the orders placed at the weekend.

EXTRACT(DOW FROM ordered_at) returns a number for the day of the week. The analyst assumes Saturday and Sunday are numbered 6 and 7.

SELECT order_id, ordered_at
FROM orders
WHERE EXTRACT(DOW FROM ordered_at) IN (6, 7)
ORDER BY order_id;

ISODOW uses the ISO 8601 convention: Monday is 1 and Sunday is 7. And the function dayname returns the day’s name.

SELECT
    order_id,
    ordered_at,
    dayname(ordered_at)             AS day_name,
    EXTRACT(DOW FROM ordered_at)    AS dow,
    EXTRACT(ISODOW FROM ordered_at) AS isodow
FROM orders
WHERE order_id IN (1001, 1002, 1004, 1013)
ORDER BY order_id;

The numbering agrees from Monday through Saturday. Sunday is the only difference, which makes this mistake easy to overlook. With ISODOW, the weekend filter works as intended:

SELECT order_id, ordered_at
FROM orders
WHERE EXTRACT(ISODOW FROM ordered_at) IN (6, 7)
ORDER BY order_id;

You can also find weekend orders by day name:

• DuckDB: dayname(ordered_at) IN ('Saturday', 'Sunday')
• PostgreSQL: to_char(ordered_at, 'FMDay') IN ('Saturday', 'Sunday')

In PostgreSQL, FM removes the extra spaces that day names normally include. This avoids weekday numbers, but check the language of the returned names.

A weekly report uses date_trunc('week', ordered_at) to assign each order to the start of its week.

Order 1012 was placed on Sunday 31 May. The following day, 1 June, is a Monday.

SELECT
    order_id,
    ordered_at,
    date_trunc('week', ordered_at) AS week_start
FROM orders
WHERE order_id = 1012;

The weekly counts show why week labels can fall outside the month containing an order:

SELECT
    CAST(date_trunc('week', ordered_at) AS DATE) AS week_start,
    COUNT(*)                                   AS orders
FROM orders
GROUP BY week_start
ORDER BY week_start;

The first row is labelled 27 April, although all three orders occurred in May: one on Saturday 2 May and two on Sunday 3 May. Weeks can cross month boundaries. You therefore cannot generally reconstruct monthly totals by adding whole weekly buckets; a week may contain orders from two different months. The weeks beginning 11 and 18 May have no orders, so they have no rows here.

ISO weeks also have an ISO year, which can differ from the calendar year. Week 1 is the week containing the year’s first Thursday. As a result, late December can belong to week 1 of the next ISO year, and early January can belong to the previous ISO year.

SELECT
    EXTRACT(YEAR FROM DATE '2025-12-30')    AS calendar_year,
    EXTRACT(ISOYEAR FROM DATE '2025-12-30') AS iso_year,
    EXTRACT(WEEK FROM DATE '2025-12-30')    AS iso_week;

In DuckDB and PostgreSQL, EXTRACT(WEEK FROM ...) returns the ISO week number. The ISO year can differ from the calendar year near the start or end of a year. For example, 30 December 2025 belongs to week 1 of ISO year 2026.

When creating a week label, pair WEEK with ISOYEAR. Using YEAR would incorrectly label that date as “2025, week 1.” Another clear option is to label each week by its start date.

Customers must register their warranty within one month of placing an order. To calculate the deadline, add INTERVAL 1 MONTH to the order’s ordered_at timestamp.

Let’s apply this to order 1012, which was placed on 31 May, and see which date SQL returns.

SELECT
    order_id,
    ordered_at,
    CAST(ordered_at + INTERVAL 1 MONTH AS DATE) AS register_by
FROM orders
WHERE order_id = 1012;

Clamping means that repeated month additions can produce a different result from one larger addition:

SELECT
    CAST(DATE '2026-05-31' + INTERVAL 1 MONTH AS DATE)                   AS one_month,
    CAST(DATE '2026-05-31' + INTERVAL 1 MONTH + INTERVAL 1 MONTH AS DATE) AS one_month_twice,
    CAST(DATE '2026-05-31' + INTERVAL 2 MONTH AS DATE)                   AS two_months;

The first one-month step changes 31 May to 30 June. The second step starts from the 30th, so it produces 30 July. Adding two months directly starts from 31 May and produces 31 July, which is a valid date.

This matters for recurring schedules. Calculating every due date from the previous one can shift the schedule earlier. Calculate each occurrence from the original date instead: original date plus one month, plus two months, and so on.

Subtracting a month does not necessarily reverse an earlier addition:

SELECT CAST(DATE '2026-06-30' - INTERVAL 1 MONTH AS DATE) AS back_one_month;

Once the date has become 30 June, the arithmetic does not remember that it started on 31 May. If the business rule says “the last day of the month,” express that rule directly. last_day returns the final date of its argument’s month:

SELECT last_day(DATE '2026-06-30' - INTERVAL 1 MONTH) AS previous_month_end;

A month also differs from a fixed number of days:

SELECT
    CAST(DATE '2026-01-31' + INTERVAL 1 MONTH AS DATE) AS one_month,
    CAST(DATE '2026-01-31' + INTERVAL 30 DAY AS DATE)  AS thirty_days;

“Within one month” and “within 30 days” can set different deadlines.

The operations team wants to know how long each delivery took. Subtract shipped_at from delivered_at to get an interval representing the time between shipping and delivery:

SELECT
    order_id,
    shipped_at,
    delivered_at,
    delivered_at - shipped_at AS time_duration
FROM deliveries
ORDER BY order_id;

For these timestamp differences, the interval contains whole days and a remaining time part. 1 day 19:00:00 therefore represents 43 hours.

Only delivered orders appear in this sample table. If an order still in transit had a NULL delivery timestamp, subtraction would produce NULL. An ordinary comparison against that duration would evaluate to unknown and exclude the row from WHERE, as discussed in lesson P02.

The shop promises to deliver each order within 24 hours of shipping. An analyst uses date_diff to check which deliveries met that deadline.

SELECT order_id
FROM deliveries
WHERE date_diff('day', shipped_at, delivered_at) <= 1
ORDER BY order_id;

The same boundary-counting rule applies to hours:

SELECT
    order_id,
    date_diff('day', shipped_at, delivered_at)  AS day_boundaries,
    date_diff('hour', shipped_at, delivered_at) AS hour_boundaries
FROM deliveries
ORDER BY order_id;

Order 1005 was shipped at 23:30 and delivered at 08:15 the next morning, taking 8 hours 45 minutes.

During that time, the clock reached nine whole-hour marks: 00:00, 01:00, 02:00, 03:00, 04:00, 05:00, 06:00, 07:00 and 08:00. DuckDB’s date_diff('hour', ...) counts those marks, so it returns 9.

To check whether a delivery arrived within 24 hours, subtract the timestamps and compare the resulting interval with 24 hours:

SELECT order_id
FROM deliveries
WHERE delivered_at - shipped_at <= INTERVAL 24 HOUR
ORDER BY order_id;

Only two deliveries meet the promise. SQL Server’s DATEDIFF also counts boundaries.

Now, the report also needs duration as a numeric number of hours. The analyst tries EXTRACT(HOUR FROM interval).

SELECT EXTRACT(HOUR FROM delivered_at - shipped_at) AS hours_taken
FROM deliveries
WHERE order_id = 1012;

To get the total duration, extract EPOCH, which expresses the interval in seconds, and divide by 3,600:

SELECT
    order_id,
    EXTRACT(EPOCH FROM delivered_at - shipped_at) / 3600 AS hours_taken
FROM deliveries
ORDER BY order_id;

The way an interval is created affects its components. INTERVAL 26 HOUR keeps all 26 hours in the time component, so extracting its hour field returns 26. Subtracting these timestamps produces 1 day 02:00:00, whose hour field is 2. Both intervals have the same total seconds.

The shop stores UTC timestamps, but its local reports use India Standard Time, or IST. An event near midnight can fall on different dates in UTC and India. The same applies to its week or month.

DuckDB and PostgreSQL support named time zones such as Asia/Kolkata for India and Europe/London for London. Both provide AT TIME ZONE for working with timestamps across time zones.

Order 1013 was placed at 20:10 UTC on 31 May. The analyst wants to show when that happened in India time and tries the following query:

SELECT
    order_id,
    ordered_at,
    ordered_at AT TIME ZONE 'Asia/Kolkata' AS in_india
FROM orders
WHERE order_id = 1013;

The two forms of AT TIME ZONE work as follows:

To convert the shop’s plain UTC values to India time, first identify their source zone, then request the local clock reading:

SELECT
    order_id,
    ordered_at AS ordered_at_utc,
    (ordered_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Kolkata' AS ordered_at_ist
FROM orders
WHERE order_id IN (1011, 1012, 1013, 1014)
ORDER BY order_id;

Order 1013 belongs to May in UTC and June in India.

At this offset, an event at or after 18:30 UTC on the last day of a month belongs to the next month in India. Converting before grouping changes the monthly counts:

SELECT
    date_trunc('month', (ordered_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Kolkata') AS month_ist,
    COUNT(*) AS orders
FROM orders
GROUP BY month_ist
ORDER BY month_ist;

The UTC counts were ten and three. The India counts are nine and four because order 1013 moves into June.

Weekdays change too. Order 1003, placed at 21:05 UTC on Sunday 3 May, occurred on Monday in India. Order 1013 also moves from Sunday to Monday. An India-time weekend report therefore excludes two orders that appeared in §05’s UTC weekend list.

For local reporting, convert to the reporting zone before extracting a weekday, truncating a period or assigning a label.

To filter a local period while keeping the column unchanged, convert the period’s boundaries to the storage time zone instead.

May in India begins at 18:30 UTC on 30 April and ends at 18:30 UTC on 31 May:

SELECT COUNT(*) AS may_orders_ist
FROM orders
WHERE ordered_at >= '2026-04-30 18:30'
  AND ordered_at <  '2026-05-31 18:30';

India uses the same UTC offset throughout the year. Many other zones change their offset for daylight saving time.

London, for example, uses UTC in winter and UTC+1 in summer:

SELECT
    (TIMESTAMP '2026-01-15 12:00' AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/London' AS january_in_london,
    (TIMESTAMP '2026-05-31 12:00' AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/London' AS may_in_london;

Adding INTERVAL 1 HOUR would reproduce the summer result but give the wrong winter result. A named zone applies the rules for each date.

The finance team wants a chart showing how many orders were placed each day from 1 to 7 June.

SELECT
    CAST(ordered_at AS DATE) AS order_day,
    COUNT(*)                AS orders
FROM orders
WHERE ordered_at >= '2026-06-01'
  AND ordered_at <  '2026-06-08'
GROUP BY order_day
ORDER BY order_day;

The result contains three rows for a seven-day period. GROUP BY creates groups from values that exist in the input. No orders occurred on 2, 4, 6 or 7 June, so those dates never become groups.

This can distort a chart or an average. A line chart may connect the populated dates and hide the zero-order days. Averaging the three returned counts gives one order per day, although the average across the full week is 3 / 7, or about 0.43.

To include every date, first create the dates and then attach the matching orders. generate_series(start, stop, step) produces values beginning at start and advancing by step.

SELECT COUNT(*) AS days
FROM generate_series(DATE '2026-06-01', DATE '2026-06-07', INTERVAL 1 DAY);

DuckDB also provides range, which excludes its stop value:

SELECT COUNT(*) AS days
FROM range(DATE '2026-06-01', DATE '2026-06-07', INTERVAL 1 DAY);

This series stops after 6 June.

Now use a LEFT JOIN to keep every generated day, including those without matching orders:

SELECT
    CAST(d.day AS DATE) AS order_day,
    COUNT(o.order_id)   AS orders
FROM generate_series(DATE '2026-06-01', DATE '2026-06-07', INTERVAL 1 DAY) AS d(day)
LEFT JOIN orders AS o
    ON o.ordered_at >= d.day
   AND o.ordered_at <  d.day + INTERVAL 1 DAY
GROUP BY d.day
ORDER BY d.day;

Three details make this work:

• The join uses a half-open range for each day, so every timestamp on that day can match.
• COUNT(o.order_id) counts matching orders. COUNT(*) would also count the placeholder row produced by the LEFT JOIN, giving an empty day a count of one.
• The requested dates come from generate_series. A filter on o.ordered_at in WHERE would reject unmatched rows and remove the empty days again.

A complete sequence of reporting dates is often called a date spine. Many warehouses keep a permanent calendar table, with one row per day and columns for week start, month, weekends and public holidays.

Dashboards often show data for the last seven days, the current month so far, or the previous full month. These date ranges change depending on when the report runs.

Queries commonly use CURRENT_TIMESTAMP or now() to get the current date and time. In DuckDB and PostgreSQL, both return the time the current transaction began. Repeated calls within that transaction therefore return the same value.

To keep our examples consistent, we use a fixed time for “now”: 10:00 UTC on Sunday 7 June 2026. Tests and published reports should also specify the cutoff time they use.

All examples in this section use UTC. For a report in another time zone, work out the start and end of the date range in that zone, as explained in §09.

A rolling window looks back a fixed amount of time from “now.” For example, a seven-day rolling window starts exactly seven days before our chosen reference time:

SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= TIMESTAMP '2026-06-07 10:00:00' - INTERVAL 7 DAY
ORDER BY order_id;

The lower boundary is 10:00 on 31 May. Order 1011, placed at midnight that day, falls outside the window. This sample contains no orders after the reference time. A production query should also apply its intended upper boundary, such as ordered_at < TIMESTAMP '2026-06-07 10:00:00', if future-dated rows could be present.

“The last seven days” can mean three different date ranges. Suppose “now” is 10:00 UTC on 7 June 2026:

1. The previous 168 hours: Go back exactly seven days from the current time. The range starts at 10:00 on 31 May and ends at 10:00 on 7 June.
2. Today and the six days before it: Start at midnight six days ago and include today’s activity so far. The range starts at 00:00 on 1 June and ends at 10:00 on 7 June. It covers seven calendar dates, but today is still incomplete.
3. The seven complete days before today: Leave today out and include the previous seven full days. The range starts at 00:00 on 31 May and ends at 00:00 on 7 June, covering 31 May through 6 June.

Use >= to include the start of each range and < to exclude its end. Before writing the filter, decide what “the last seven days” means and which time zone defines the dates.

For today and the six preceding days, start at midnight six days ago: CURRENT_DATE - INTERVAL 6 DAY. Use the report’s chosen “now” as the upper boundary.

For the seven complete days before today, start at midnight seven days ago and stop at midnight today:

ordered_at >= CURRENT_DATE - INTERVAL 7 DAY
AND ordered_at < CURRENT_DATE

Make sure CURRENT_DATE represents today in the intended reporting time zone. If the report uses local dates but ordered_at stores UTC values, convert the boundaries to UTC before comparing them.

Another issue arises when a report compares a full month with a month that is still in progress. Consider finance’s monthly report. It excludes cancelled orders and adds together the amounts for paid and pending orders. In this example, that total is labelled revenue.

The following query calculates each month’s total in a CTE. A scalar subquery retrieves May’s total, allowing the report to show each month’s revenue as a percentage of May’s. Both techniques were introduced in lesson P10:

WITH monthly AS (
    SELECT
        date_trunc('month', ordered_at) AS month,
        SUM(amount)                    AS revenue
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY month
)
SELECT
    month,
    revenue,
    ROUND(100.0 * revenue / (SELECT revenue FROM monthly WHERE month = '2026-05-01'), 1) AS pct_of_may
FROM monthly
ORDER BY month;

June appears to be about 65% below May. But May contains 31 complete days, while June contains six complete days and part of the seventh.

For a comparable month-to-date report, use the same portion of each month. Here, exclude the unfinished seventh day and compare days 1–6:

WITH month_to_date AS (
    SELECT
        date_trunc('month', ordered_at) AS month,
        SUM(amount)                    AS revenue
    FROM orders
    WHERE status <> 'cancelled'
      AND EXTRACT(DAY FROM ordered_at) < 7
    GROUP BY month
)
SELECT
    month,
    revenue,
    ROUND(100.0 * revenue / (SELECT revenue FROM month_to_date WHERE month = '2026-05-01'), 1) AS pct_of_may
FROM month_to_date
ORDER BY month;

Across the same six dates, June is about 8% below May.

To update the cutoff automatically, replace 7 with EXTRACT(DAY FROM CURRENT_DATE). On 7 June, this returns 7, so the comparison includes days 1–6 of each month. On the first day of a month, there are no complete days in the current month to compare yet.

The example uses EXTRACT on ordered_at to keep the query easy to read. For a large table, define a separate date range for each month using >= for the start and < for the end. This can help the database skip unnecessary data, as explained in §03.

Another option is to report only complete months. This condition excludes orders from the start of the current month onward:

ordered_at < date_trunc('month', CURRENT_DATE)

Many date and time mistakes produce valid queries and believable results. They become easier to find when you connect an unexpected result to the rule that could have produced it.

Before writing a time-based query, answer four questions:

1. What does the column store? Establish its type and precision, including whether timestamps can contain fractional seconds.
2. Which time zone defines the report? Know the storage convention and the zone used to assign days, weeks and months.
3. Where are the boundaries? Define the start and end, the first day of the week, and the meaning of relative phrases such as “last seven days.”
4. Which periods should appear, and are they complete? Decide how to represent empty periods and how to handle unfinished periods or late data.

This lesson uses DuckDB. The same ideas apply to other databases, but function names, syntax and some results differ.

Start by checking the column’s data type and time zone. Filter timestamps with >= the start and < the end. Group by date values that preserve the year, define where weeks start, and pair ISO week numbers with ISO years. Format labels afterward.

Date arithmetic must match the question. Adding months can adjust the day to fit a shorter month. Counting boundaries or extracting one part of an interval does not give the total elapsed time.

For reliable reports, use the reporting time zone, include days with no activity when needed, and compare complete dates or matching portions of each month. Check that the expected data has arrived before interpreting the results.

Next, lesson P12 covers string functions: cleaning, searching, combining and matching text.