Date and Time Functions

EXTRACT pulls a single numeric component from a date — EXTRACT(MONTH FROM date) returns the number 1 through 12. DATE_TRUNC rounds a date down to the start of a specified period — DATE_TRUNC('month', date) returns a complete date value representing the first day of that month.

For time-series grouping, this distinction is critical. EXTRACT(MONTH FROM date) loses the year — January 2023 and January 2024 both return 1 from EXTRACT(MONTH). If you GROUP BY EXTRACT(MONTH FROM order_date), orders from different years collapse into the same group, producing incorrect multi-year totals. DATE_TRUNC('month', date) returns 2023-01-01 and 2024-01-01 respectively — distinct values that sort correctly and do not merge across years.

Always use DATE_TRUNC for time-series GROUP BY. Use EXTRACT only when you specifically want just the numeric component — for example, filtering by month number across all years (WHERE EXTRACT(MONTH FROM order_date) = 1 to get all Januaries), or for day-of-week analysis where you want to aggregate all Mondays together regardless of the specific date. The rule: if ordering and uniqueness across years matters, use DATE_TRUNC. If you want to aggregate across multiple years intentionally, use EXTRACT.

In PostgreSQL and DuckDB, subtract two DATE values directly — the result is an integer representing the number of days: delivery_date - order_date returns the number of days between them. For TIMESTAMP values, subtraction returns an INTERVAL — extract the day component with EXTRACT(DAY FROM (timestamp2 - timestamp1)) or convert to epoch seconds and divide: EXTRACT(EPOCH FROM (ts2 - ts1)) / 86400.

In MySQL, use DATEDIFF(end_date, start_date) — note the argument order is end then start, which returns positive values for end > start. DATEDIFF('2024-02-15', '2024-01-15') returns 31. In SQL Server, DATEDIFF(day, start_date, end_date) — note the unit is first and the argument order is reversed from MySQL.

For business days (excluding weekends), there is no standard SQL function — you need a calendar table or a more complex expression. A simple approximation: total_days - (total_weeks * 2) where total_weeks is FLOOR(total_days / 7) — this works for approximate business day counts. For precise business day calculations accounting for holidays, a calendar dimension table with an is_business_day flag is the correct approach. The AGE function provides a human-readable interval (years, months, days) rather than a raw day count — use AGE for display purposes (tenure display, customer age) and direct date subtraction for arithmetic calculations.

The standard pattern: WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'. This filters orders where the order_date is within the last 30 days from today. CURRENT_DATE gives today's date; subtracting INTERVAL '30 days' gives the cutoff date. The >= operator includes the cutoff date itself.

Variations for different databases: MySQL uses DATE_SUB(CURDATE(), INTERVAL 30 DAY) or NOW() - INTERVAL 30 DAY. SQL Server uses DATEADD(day, -30, GETDATE()). The PostgreSQL/DuckDB interval syntax (INTERVAL '30 days') is the most readable and closest to the SQL standard.

Important nuances: CURRENT_DATE - INTERVAL '30 days' is not the same as "one calendar month ago" — 30 days is always 30 days regardless of month length. For "same period last calendar month", use WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AND order_date < DATE_TRUNC('month', CURRENT_DATE). For "last N months", use INTERVAL 'N months' — this correctly handles month-length differences: CURRENT_DATE - INTERVAL '3 months' gives the date 3 calendar months ago, not 90 days ago. For performance, never apply functions to the indexed column — WHERE order_date >= CURRENT_DATE - INTERVAL '30 days' allows index range scan; WHERE EXTRACT(MONTH FROM order_date) = ... requires a full table scan.

DATE_TRUNC truncates a date or timestamp to the beginning of a specified time period — 'month' gives the first day of the month, 'year' gives January 1st, 'week' gives Monday of that week, 'hour' gives the top of the hour. The key behaviour: all dates within the same period truncate to the same value. Every day in January 2024 truncates to 2024-01-01. This makes DATE_TRUNC the foundation of time-series GROUP BY.

For time-series reporting: GROUP BY DATE_TRUNC('month', order_date) groups all January orders together, all February orders together, and produces one row per calendar month. The truncated date is a complete, sortable, comparable value — ORDER BY DATE_TRUNC('month', order_date) correctly sorts months chronologically. You can also join the truncated date to a calendar dimension table to fill in months with zero activity.

The practical reporting pattern: SELECT DATE_TRUNC('month', order_date)::DATE AS month, SUM(revenue) AS monthly_revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month. The ::DATE cast converts the TIMESTAMP result of DATE_TRUNC to a clean DATE for display. This pattern — DATE_TRUNC in both SELECT and GROUP BY — is the correct, idiomatic time-series aggregation in PostgreSQL and DuckDB. In MySQL, use DATE_FORMAT(date, '%Y-%m-01') to achieve the same effect: '%Y-%m-01' formats the date as the first day of its month, grouping all days in the same month to the same string value.

The foundational rule: store all timestamps in UTC. Use the TIMESTAMPTZ (timestamp with time zone) column type in PostgreSQL. When data is inserted, the database converts the local time to UTC for storage. When data is read, it can be converted back to any local timezone for display. Storing in UTC means the stored values are unambiguous — there is no confusion about which timezone an event occurred in, and daylight saving time transitions do not create duplicates or gaps.

To convert for display: SELECT created_at AT TIME ZONE 'America/Los_Angeles' AS pst_time FROM events. This converts the UTC-stored timestamp to Pacific time for display without modifying the stored value. In DuckDB: timezone('America/Los_Angeles', created_at). The AT TIME ZONE expression handles DST transitions automatically when using named timezone identifiers ('America/Los_Angeles') rather than fixed offsets ('-08:00') — named identifiers know about DST rules, fixed offsets do not.

For filtering: never apply timezone conversion to the stored column in WHERE — this prevents index usage. Instead, convert the filter boundary values to UTC: WHERE created_at >= '2024-01-15 00:00:00-08:00' AND created_at < '2024-01-16 00:00:00-08:00'. The database converts the literal values to UTC internally and compares against the UTC-stored timestamps — the index can be used for a range scan. For reports aggregated by local day: convert to local timezone first (in a CTE or derived table), then truncate and group. A platform operating out of a single US timezone can simplify this handling somewhat — the real complexity arises in platforms serving users across multiple timezones and DST rules, which is the norm for any US-wide platform.

Cause: GROUP BY EXTRACT(MONTH FROM order_date) uses only the month number (1-12) with no year. Both January 2023 and January 2024 extract to month = 1 and collapse into the same group. This is the most common date aggregation mistake — months from different years are silently merged.

Fix: Replace EXTRACT(MONTH) with DATE_TRUNC('month', order_date): GROUP BY DATE_TRUNC('month', order_date). DATE_TRUNC returns a complete date value (2023-01-01 vs 2024-01-01) that is unique per calendar month across all years. Cast to DATE for clean display: DATE_TRUNC('month', order_date)::DATE. If you must use EXTRACT, include the year: GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) — this prevents year-collapsing but the result is less convenient to sort and display.

Cause: A function is applied to the date column in the WHERE clause: WHERE EXTRACT(YEAR FROM order_date) = 2024 or WHERE DATE_TRUNC('month', order_date) = '2024-01-01'. Applying any function to an indexed column in WHERE prevents the database from using a B-tree index on that column — it must evaluate the function for every row (full table scan).

Fix: Rewrite the filter to compare the raw column against literal date boundaries. Instead of WHERE EXTRACT(YEAR FROM order_date) = 2024, use WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'. Instead of WHERE DATE_TRUNC('month', order_date) = '2024-01-01', use WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'. The raw column comparison allows the database to use an index range scan — orders of magnitude faster on large tables. For frequently used date columns, ensure an index exists: CREATE INDEX idx_orders_date ON orders(order_date).

Cause: Adding INTERVAL '1 month' to January 31 produces February 28 (or 29 in leap years) — the database clamps to the last valid day of the resulting month. This is correct behaviour but unexpected if you assumed months are always 30 or 31 days. Similarly, INTERVAL '30 days' and INTERVAL '1 month' are not the same — 1 month is always one calendar month regardless of actual day count.

Fix: Use INTERVAL '1 month' when you want one calendar month (January → February, regardless of day count). Use INTERVAL '30 days' when you want exactly 30 days. For month-end reporting, always derive the last day of month via DATE_TRUNC rather than adding a fixed interval: (DATE_TRUNC('month', date) + INTERVAL '1 month' - INTERVAL '1 day')::DATE gives the last day of any month correctly. Be explicit about which semantics you need — document in a comment whether your date arithmetic is calendar-month-based or fixed-day-based.

Cause: AGE(date1, date2) computes date1 - date2. If date1 is the earlier date (smaller value) and date2 is the later date, the result is a negative interval. Also, AGE can produce mixed signs within the interval for dates that cross month boundaries in complex ways — for example, AGE('2024-02-15', '2024-01-31') returns '0 years 0 mons 15 days' but AGE('2024-01-15', '2024-02-28') may return an unexpected result.

Fix: Always pass the later date as the first argument to AGE: AGE(end_date, start_date) or AGE(CURRENT_DATE, hire_date) — this gives a positive interval. For simple day-count differences, use date subtraction (end_date - start_date) rather than AGE — it is simpler and returns a plain integer. Use AGE only when you need a human-readable interval in years, months, and days. For tenure displays: EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) gives complete years; EXTRACT(MONTH FROM AGE(CURRENT_DATE, hire_date)) gives the remaining months.

Cause: Date format functions return month and day names in the database server's locale setting. A server configured with a non-English locale may return 'janvier' instead of 'January', or 'lunes' instead of 'Monday'. This is a server configuration issue, not a query bug, but it affects query output.

Fix: For locale-independent month labels, use EXTRACT to get the month number and a CASE statement to map to the desired language: CASE EXTRACT(MONTH FROM order_date) WHEN 1 THEN 'January' WHEN 2 THEN 'February' ... END. Alternatively, set the session locale before running the query: SET lc_time = 'en_US.UTF-8' in PostgreSQL. For date formats that avoid language entirely, use ISO format: strftime(date, '%Y-%m-%d') or TO_CHAR(date, 'YYYY-MM-DD') — these are locale-independent numbers and separators.

Write a comprehensive date analytics query that produces a store performance summary for January and February 2024. For each store show: store_id, city, the month (formatted as 'Jan 2024' or 'Feb 2024'), order_count, total_revenue, avg_order_value, avg_delivery_days (rounded to 1 decimal, NULL if no deliveries), fastest_delivery (min days), and a month_label that is 'January' or 'February'. Use DATE_TRUNC for the period grouping, strftime for the display label, and date arithmetic for delivery days. Only include delivered orders. Sort by store_id then month.