SQL Date and Time — Basics of Ranges, Truncation, Gap Filling

QUESTION 1

Filtering a period — Cut out one month from a TIMESTAMP column with a half-open interval

TIMESTAMPRange conditionHalf-open intervalBoundary values

Background ▼

When you filter a date column by range, the way you write the upper bound changes the result. If you give a bare date literal to a TIMESTAMP column, the time part is read as 00:00:00.

SELECT * FROM table_name
WHERE  ts_col >= '2025-01-01'   -- lower bound: included
  AND  ts_col <  '2025-02-01';  -- upper bound: excluded (start of the next month)

Half-open interval [start, end) : the lower bound is included and the upper bound is not. BETWEEN '2025-01-01' AND '2025-01-31' makes the upper bound 2025-01-31 00:00:00, so every row recorded during the daytime of January 31 is dropped.

Problem ▼

From the access_logs table, retrieve the accesses that happened in March 2026. accessed_at is a TIMESTAMP column. Return log_id, user_id, accessed_at, sorted by accessed_at ascending.

Tables used

▸ access_logs

log_id	user_id	accessed_at
1	101	2026-03-01 09:15:00
2	102	2026-03-15 23:59:59
3	103	2026-03-31 00:00:00
4	104	2026-03-31 18:20:00
5	105	2026-04-01 00:00:00
6	106	2026-02-28 21:05:00

Expected Output ▼

log_id	user_id	accessed_at
1	101	2026-03-01 09:15:00
2	102	2026-03-15 23:59:59
3	103	2026-03-31 00:00:00
4	104	2026-03-31 18:20:00

Model Answer ▼

SELECT
  log_id, user_id, accessed_at
FROM   access_logs
WHERE  accessed_at >= '2026-03-01'    -- lower bound: includes March 1 00:00:00
  AND  accessed_at <  '2026-04-01'    -- upper bound: up to just before April 1 00:00:00
ORDER BY accessed_at;

/*
  Execution order (logical evaluation order):
  1. FROM access_logs             → Read 6 rows
  2. WHERE half-open March range  → Narrow to 4 rows
  3. SELECT log_id, user_id, ...  → Select 3 columns
  4. ORDER BY accessed_at         → Ascending by timestamp
  */

Explanation (table transitions & key points) ▼

SELECT
  log_id, user_id, accessed_at
FROM   access_logs
WHERE  accessed_at >= '2026-03-01'
  AND  accessed_at <  '2026-04-01'
ORDER BY accessed_at;

LEGEND ▼

Rows read / loaded

① FROM access_logs ▼

FROM access_logsRead all 6 rows of access_logs. Note the rows adjacent to March on both sides: 2026-02-28 and 2026-04-01 00:00:00.

1 / 3

log_id	user_id	accessed_at
1	101	2026-03-01 09:15:00
2	102	2026-03-15 23:59:59
3	103	2026-03-31 00:00:00
4	104	2026-03-31 18:20:00
5	105	2026-04-01 00:00:00
6	106	2026-02-28 21:05:00

All 6 rows read

LEARNING POINTS

The half-open interval is the default form: write a period as >= start AND < start of the next period. It needs no rewriting whether the month ends on the 28th or the 31st, leap year or not.

DATE versus TIMESTAMP: if the column is DATE, even BETWEEN '2026-03-01' AND '2026-03-31' returns the correct 4 rows. The trap appears with TIMESTAMP, where the same expression silently loses the last day.

Compare the column as-is: because no function is applied to accessed_at, an index on accessed_at can still be used.

ANTI-PATTERNS

Writing the upper bound as the last day of the month: BETWEEN '2026-03-01' AND '2026-03-31' reads the upper bound as 2026-03-31 00:00:00, so rows like log 4 quietly disappear. The count only drops slightly, which makes the loss hard to notice.

Writing the upper bound as 23:59:59: <= '2026-03-31 23:59:59' misses values with sub-second precision (microseconds). A half-open interval does not depend on precision.

Field Notes

Time zones and the boundary of "that day": if the column is TIMESTAMPTZ, "the start of March 1" depends on the time zone of the session. To aggregate in Japan time, either state the offset explicitly, as in accessed_at >= TIMESTAMPTZ '2026-03-01 00:00+09', or pin the session TimeZone. Most bugs where an aggregate is slightly off come from two people reading this boundary differently.

QUESTION 2

DATE_TRUNC — Truncate timestamps to the first of the month for a monthly rollup

DATE_TRUNCGROUP BYMonthly rollupTime series

Background ▼

DATE_TRUNC(unit, value) truncates a timestamp down to the start of the given unit. Units include 'year', 'month', 'week', 'day' and 'hour'.

SELECT DATE_TRUNC('month', ts_col)::date AS bucket
FROM     table_name
GROUP BY DATE_TRUNC('month', ts_col);
-- 2026-02-18 20:45 → truncated to 2026-02-01 00:00:00

The return type is TIMESTAMP: DATE_TRUNC('month', ...) returns a value with a time part, not a date. Cast it with ::date when you want to display the date alone.

Problem ▼

Aggregate the orders table by month and return the first day of the month, the order count and the total sales. Return month, orders, total_amount, sorted by month ascending. Display month as a date only.

Tables used

▸ orders

order_id	ordered_at	amount
1	2026-01-05 10:00:00	3000
2	2026-01-22 14:30:00	5000
3	2026-02-03 09:10:00	4000
4	2026-02-18 20:45:00	2500
5	2026-02-27 08:00:00	1500
6	2026-03-09 12:00:00	7000

Expected Output ▼

month	orders	total_amount
2026-01-01	2	8000
2026-02-01	3	8000
2026-03-01	1	7000

Model Answer ▼

SELECT
  DATE_TRUNC('month', ordered_at)::date AS month,  -- Truncate to the 1st and show it as a date
  COUNT(*)      AS orders,
  SUM(amount)  AS total_amount
FROM     orders
GROUP BY DATE_TRUNC('month', ordered_at)   -- The grouping key is the truncated value
ORDER BY month;

/*
  Execution order (logical evaluation order):
  1. FROM orders                      → Read 6 rows
  2. GROUP BY DATE_TRUNC('month', …)  → Split into 3 groups
  3. SELECT COUNT / SUM               → Aggregate per group
  4. ORDER BY month                   → Ascending by month
  */

Explanation (table transitions & key points) ▼

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

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM ordersRead all 6 rows of orders. Because ordered_at carries both a date and a time, every row currently holds a distinct value.

1 / 4

order_id	ordered_at	amount
1	2026-01-05 10:00:00	3000
2	2026-01-22 14:30:00	5000
3	2026-02-03 09:10:00	4000
4	2026-02-18 20:45:00	2500
5	2026-02-27 08:00:00	1500
6	2026-03-09 12:00:00	7000

All 6 rows read

LEARNING POINTS

Truncation is what creates the grouping key: values that carry a time are all distinct, so each row forms its own group. Only after DATE_TRUNC lowers the granularity does a unit like "per month" exist. Swap the unit for 'week' or 'day' and the same query changes granularity alone.

Sort by the truncated value: ordering by the first of the month gives chronological order. Grouping by a string such as TO_CHAR(ordered_at, 'Mon YYYY') may read nicely, but the sort follows string order and breaks as soon as the range crosses a year.

Repeat the expression in GROUP BY: PostgreSQL also accepts the output alias, as in GROUP BY month, but repeating the expression works on every database.

ANTI-PATTERNS

Applying a function to the column in WHERE: filtering with WHERE DATE_TRUNC('month', ordered_at) = '2026-02-01' prevents the index on ordered_at from being used. Filter with the half-open interval from Q1, and keep DATE_TRUNC for building the grouping key.

A key without the year: grouping by EXTRACT(MONTH FROM ordered_at) alone merges February 2025 and February 2026 into the same "2". A monthly key must always include the year.

Field Notes

Switching report granularity in one place: for a report that toggles between daily, weekly and monthly, parameterize only the unit, as in DATE_TRUNC(:granularity, ordered_at), and you can keep a single query. Note that 'week' always starts on Monday, which does not match a business rule that starts the week on Sunday. In that case shift it, as in DATE_TRUNC('week', ordered_at + INTERVAL '1 day') - INTERVAL '1 day', or hold a calendar table so the definition lives in one place.

QUESTION 3

EXTRACT — Pull the day of week out of a date and aggregate by weekday

EXTRACTDOWWeekday rollupZero-based

Background ▼

EXTRACT(field FROM value) returns a component of a date or timestamp — year, month, day, day of week and so on — as a number. The day of week comes from DOW.

SELECT EXTRACT(DOW FROM date_col) AS dow
FROM   table_name;
-- DOW:    0=Sun 1=Mon 2=Tue 3=Wed 4=Thu 5=Fri 6=Sat
-- ISODOW: 1=Mon …… 7=Sun (Sunday last)

DOW makes Sunday 0: it is not one-based. For a report whose week starts on Monday, ISODOW (1=Mon … 7=Sun) puts the numbers in the same order as the week itself.

Problem ▼

Aggregate the reservations table by day of week and return the weekday number, the reservation count and the total number of seats. Express the weekday number with EXTRACT(DOW ...) (0 = Sunday). Return dow, reservations, total_seats, sorted by dow ascending.

Tables used

▸ reservations

reservation_id	reserved_on	seats
1	2026-06-01	2
2	2026-06-02	4
3	2026-06-06	6
4	2026-06-07	5
5	2026-06-08	3
6	2026-06-13	8

Expected Output ▼

dow	reservations	total_seats
0	1	5
1	2	5
2	1	4
6	2	14

Model Answer ▼

SELECT
  EXTRACT(DOW FROM reserved_on) AS dow,  -- 0=Sunday … 6=Saturday
  COUNT(*)     AS reservations,
  SUM(seats)  AS total_seats
FROM     reservations
GROUP BY EXTRACT(DOW FROM reserved_on)
ORDER BY dow;

/*
  Execution order (logical evaluation order):
  1. FROM reservations           → Read 6 rows
  2. GROUP BY EXTRACT(DOW …)     → Split into 4 groups
  3. SELECT COUNT / SUM          → Aggregate per group
  4. ORDER BY dow                → Ascending by weekday number
  */

Explanation (table transitions & key points) ▼

SELECT
  EXTRACT(DOW FROM reserved_on) AS dow,
  COUNT(*)    AS reservations,
  SUM(seats) AS total_seats
FROM     reservations
GROUP BY EXTRACT(DOW FROM reserved_on)
ORDER BY dow;

LEGEND ▼

Rows read / loaded

① FROM reservations ▼

FROM reservationsRead all 6 rows of reservations. The dates span two weeks of June 2026, so some weekdays appear more than once.

1 / 4

reservation_id	reserved_on	seats
1	2026-06-01	2
2	2026-06-02	4
3	2026-06-06	6
4	2026-06-07	5
5	2026-06-08	3
6	2026-06-13	8

All 6 rows read

LEARNING POINTS

EXTRACT returns components as numbers: YEAR, MONTH, DAY, HOUR, DOW, ISODOW, QUARTER and EPOCH are all available. Its main use is to build cyclical dimensions such as day of week or hour of day.

DOW versus ISODOW: DOW runs 0=Sunday to 6=Saturday, ISODOW runs 1=Monday to 7=Sunday. Using DOW in a Monday-first report puts Sunday at the top and breaks the reading order.

Weekdays with no rows produce no rows: aggregation only groups values that exist in the source. To show Wednesday, Thursday and Friday as 0, start from a list of weekdays and LEFT JOIN, exactly as in Q5.

ANTI-PATTERNS

Grouping by the weekday name: TO_CHAR(reserved_on, 'Day') changes with the locale setting and is padded with trailing spaces. It also sorts alphabetically, so use the numeric DOW / ISODOW as the key and attach display names at the very end.

Deriving the weekday by hand: home-grown arithmetic such as dividing a day count by 7 drifts around leap years and the choice of epoch. Always take the weekday from a date function.

Field Notes

A weekday rollup only means something next to a definition of "business day". When you average by weekday, the number changes depending on whether closed days and holidays are counted in the denominator. In practice you keep a calendar table with a holiday flag, match it against EXTRACT(DOW ...) and restrict the aggregation to business days. The weekday follows mechanically from the date, but a business day is a business rule: unless you hold it as data, you cannot reproduce it.

QUESTION 4

INTERVAL and date difference — Compute an expiry date and the days remaining

INTERVALDate arithmeticExpiry managementCast

Background ▼

You can add an interval to, or subtract one from, a date. Subtracting one DATE from another gives a day count as an integer.

SELECT
  date_col + INTERVAL '1 year',   -- the result is a timestamp
  date_col - DATE '2025-01-01'  -- the result is an integer (days)
FROM table_name;

Watch the type change: DATE + INTERVAL returns a TIMESTAMP. Cast it with ::date when you want a date. Without the cast, subtracting a DATE yields an INTERVAL instead of a number of days.

Problem ▼

For the licenses table, treat one year after the issue date as the expiry date and compute the days remaining as of the reference date 2026-09-01. Exclude licenses that have already expired on the reference date (a license whose expiry date equals the reference date is kept, with 0 days remaining). Return license_id, issued_on, expires_on, days_left, sorted by days_left ascending.

Tables used

▸ licenses

license_id	user_id	issued_on
1	201	2025-09-15
2	202	2025-10-01
3	203	2025-08-20
4	204	2026-01-31

Expected Output ▼

license_id	issued_on	expires_on	days_left
1	2025-09-15	2026-09-15	14
2	2025-10-01	2026-10-01	30
4	2026-01-31	2027-01-31	152

Model Answer ▼

SELECT
  license_id,
  issued_on,
  (issued_on + INTERVAL '1 year')::date AS expires_on,  -- one year later, as a date
  (issued_on + INTERVAL '1 year')::date - DATE '2026-09-01' AS days_left  -- date minus date is a day count
FROM   licenses
WHERE  (issued_on + INTERVAL '1 year')::date >= DATE '2026-09-01'   -- drop the expired ones
ORDER BY days_left;

/*
  Execution order (logical evaluation order):
  1. FROM licenses                  → Read 4 rows
  2. WHERE expiry >= reference date → Narrow to 3 rows
  3. SELECT expiry and days left    → Output 4 columns
  4. ORDER BY days_left             → Ascending by days remaining
  */

Explanation (table transitions & key points) ▼

SELECT
  license_id,
  issued_on,
  (issued_on + INTERVAL '1 year')::date AS expires_on,
  (issued_on + INTERVAL '1 year')::date - DATE '2026-09-01' AS days_left
FROM   licenses
WHERE  (issued_on + INTERVAL '1 year')::date >= DATE '2026-09-01'
ORDER BY days_left;

LEGEND ▼

Rows read / loaded

① FROM licenses ▼

FROM licensesRead all 4 rows of licenses. The table only holds the issue date; the expiry date is produced by calculation.

1 / 4

license_id	user_id	issued_on
1	201	2025-09-15
2	202	2025-10-01
3	203	2025-08-20
4	204	2026-01-31

All 4 rows read

LEARNING POINTS

INTERVAL understands the calendar: + INTERVAL '1 year' means "the same month and day next year", not "365 days later". '1 month' behaves the same way: one month after January 31 is clamped to February 28 (29 in a leap year). Adding a fixed number of days drifts at every month end and year boundary.

The result type decides the subtraction: DATE - DATE is an integer number of days, while TIMESTAMP - TIMESTAMP is an INTERVAL. To compare and sort the days remaining as a number, bring both sides to DATE.

The repeated expression can be tidied up: the expiry expression appears three times here. Computing it once in a CTE or subquery and referring to it by name reads better (see the CTE theme).

ANTI-PATTERNS

Approximating a year as 365 days: issued_on + 365 is off by one day across any period that contains a leap day. Expiry dates and contract terms must be computed in calendar units such as INTERVAL '1 year'.

Hard-coding CURRENT_DATE as the reference: a query whose result changes with the day it runs cannot be tested or reproduced. Pass the reference date in as a parameter so it can be verified against a fixed value.

Field Notes

Decide the boundary day before writing the expiry check. "Can a license that expires on 2026-09-15 still be used on September 15?" is a specification question, not a SQL question. If the day itself counts as valid, write expires_on >= reference date; if it does not, write expires_on > reference date. Most expiry bugs come from that one day being read differently by different implementers. Fix the treatment of the boundary in prose first and leave it in a comment, so whoever reads the query next reproduces the same decision.

QUESTION 5

generate_series — Build a run of dates and fill the missing days with 0

generate_seriesLEFT JOINGap fillingCOALESCE

Background ▼

generate_series(start, stop, step) produces a run of consecutive values as rows. Step it by day and you get a scaffold that holds every date, whether or not data exists for it.

SELECT d.day::date
FROM   generate_series(
         DATE '2025-01-01',
         DATE '2025-01-03',
         INTERVAL '1 day') AS d(day);
-- generates 3 rows (January 1, 2 and 3)

A "missing day" never shows up in an aggregate: aggregate the sales table alone and days with no sales vanish, because no row exists for them in the first place. Put a list of dates on the left and LEFT JOIN to keep the missing days as rows.

Problem ▼

From the daily_sales table, retrieve the sales for the five days from 2026-05-01 to 2026-05-05 with no date missing. Treat days with no sales as 0. Return day, amount, sorted by day ascending.

Tables used

▸ daily_sales

sold_on	amount
2026-05-01	12000
2026-05-02	8000
2026-05-05	15000

Expected Output ▼

day	amount
2026-05-01	12000
2026-05-02	8000
2026-05-03	0
2026-05-04	0
2026-05-05	15000

Model Answer ▼

SELECT
  d.day::date            AS day,
  COALESCE(s.amount, 0)  AS amount   -- unmatched days are NULL → replace with 0
FROM      generate_series(
            DATE '2026-05-01',
            DATE '2026-05-05',
            INTERVAL '1 day') AS d(day)   -- the date scaffold (5 rows)
LEFT JOIN daily_sales s
       ON s.sold_on = d.day::date              -- scaffold on the left, actuals on the right
ORDER BY day;

/*
  Execution order (logical evaluation order):
  1. generate_series(...)         → Generate 5 days of dates
  2. LEFT JOIN daily_sales        → Match sales onto the dates (NULL where absent)
  3. SELECT COALESCE(amount, 0)   → Replace NULL with 0
  4. ORDER BY day                 → Ascending by date
  */

Explanation (table transitions & key points) ▼

SELECT
  d.day::date           AS day,
  COALESCE(s.amount, 0) AS amount
FROM      generate_series(
            DATE '2026-05-01',
            DATE '2026-05-05',
            INTERVAL '1 day') AS d(day)
LEFT JOIN daily_sales s
       ON s.sold_on = d.day::date
ORDER BY day;

LEGEND ▼

Rows read / loaded

① generate_series builds the dates ▼

generate_series(DATE '2026-05-01', DATE '2026-05-05', INTERVAL '1 day')Before reading any table, produce 5 rows from May 1 to May 5. These 5 rows decide the row count of the final result, regardless of whether sales exist.

1 / 4

d.day
2026-05-01
2026-05-02
2026-05-03
2026-05-04
2026-05-05

5 rows generated

LEARNING POINTS

The left side decides the row count: start from the actuals table and days without data never reach the output. Put the list of dates on the left and LEFT JOIN, and the length of the period decides the number of result rows.

NULL and 0 are different things: columns from the unmatched side of a join come back as NULL. COALESCE exists to turn that "no actuals" NULL into a reportable 0. Remember that SUM ignores NULL while COUNT(*) still counts the row.

The step is free: INTERVAL '1 hour' gives an hourly scaffold, '1 month' a monthly one. The shape of the query stays the same and only the granularity changes.

ANTI-PATTERNS

Filtering the right side of a LEFT JOIN in WHERE: writing WHERE s.amount > 0 drops the NULL rows (May 3 and May 4) as UNKNOWN and turns the query back into an inner join. Conditions on the right table belong in the ON clause.

Filling the gaps in application code: padding dates after the fetch scatters the same logic across the screen, the batch job and the CSV export. Build the date scaffold in SQL and keep the definition of the period in one place.

Field Notes

generate_series versus a calendar table: building the scaffold with generate_series every time is convenient, but it cannot carry attributes such as holidays, business days or fiscal periods. For BI and reporting you run continuously, keep one calendar table keyed by date with a holiday flag and a fiscal month, and have every query start from it with a LEFT JOIN. Sharing the "definition of the period" as data is what prevents two departments from disagreeing about where the month ends.