Date Arithmetic and Truncation

Calculating with Dates

Extracting parts is just the beginning. Real analytics requires calculating differences (“days since last purchase”), adding intervals (“30 days from now”), and grouping by periods (“weekly revenue”). These operations power cohort analysis, retention metrics, and every time-series report you’ll ever build.

Dialect Comparison: Date Arithmetic

This is where dialects diverge dramatically. Know your target:

• Add 7 days:
  • PostgreSQL uses d + INTERVAL '7 days'
  • MySQL uses DATE_ADD(d, INTERVAL 7 DAY)
  • MSSQL uses DATEADD(day, 7, d)
  • Oracle uses d + 7
• Add 1 month:
  • PostgreSQL uses d + INTERVAL '1 month'
  • MySQL uses DATE_ADD(d, INTERVAL 1 MONTH)
  • MSSQL uses DATEADD(month, 1, d)
  • Oracle uses ADD_MONTHS(d, 1)
• Subtract 30 days:
  • PostgreSQL uses d - INTERVAL '30 days'
  • MySQL uses DATE_SUB(d, INTERVAL 30 DAY)
  • MSSQL uses DATEADD(day, -30, d)
  • Oracle uses d - 30
• Add 1 year:
  • PostgreSQL uses d + INTERVAL '1 year'
  • MySQL uses DATE_ADD(d, INTERVAL 1 YEAR)
  • MSSQL uses DATEADD(year, 1, d)
  • Oracle uses ADD_MONTHS(d, 12)

Calculating Date Differences

Questions like “How many days since last purchase?” and “What’s the average time between orders?” come up constantly. PostgreSQL makes it simple:

-- PostgreSQL: subtract dates directly
SELECT
  order_date,
  CURRENT_DATE - order_date AS days_ago
FROM orders;

-- Days between signup and first order
SELECT
  u.id,
  MIN(o.order_date) - u.joining_date AS days_to_first_order
FROM users u
JOIN orders o
  ON u.id = o.user_id
GROUP BY u.id, u.joining_date;

Dialect Comparison: Date Differences

• Days between dates:
  • PostgreSQL uses d1 - d2
  • MySQL uses DATEDIFF(d1, d2)
  • MSSQL uses DATEDIFF(day, d2, d1)
  • Oracle uses d1 - d2
• Months between:
  • PostgreSQL uses AGE(d1, d2)
  • MySQL uses TIMESTAMPDIFF(MONTH, d2, d1)
  • MSSQL uses DATEDIFF(month, d2, d1)
  • Oracle uses MONTHS_BETWEEN(d1, d2)

Truncating Dates with DATE_TRUNC

DATE_TRUNC is essential for time-series analysis. It rounds a date down to the start of a period — the first day of the month, the Monday of that week, the first of the quarter.

-- Truncate to month start
SELECT
  DATE_TRUNC('month', order_date) AS month_start
FROM orders;
-- Example:
-- Input: 2024-03-15
-- Output: 2024-03-01 (first of the month)

Common truncation periods:

• ‘day’ — Start of day (strips the time component from timestamps)
• ‘week’ — Start of week (Monday in PostgreSQL by default)
• ‘month’ — First day of month
• ‘quarter’ — First day of quarter (Jan 1, Apr 1, Jul 1, Oct 1)
• ‘year’ — First day of year

Grouping by Time Period

DATE_TRUNC’s real power is in GROUP BY for time-series aggregation:

Dialect Comparison: Date Truncation

• Truncate to month:
  • PostgreSQL uses DATE_TRUNC('month', d)
  • MySQL uses DATE_FORMAT(d, '%Y-%m-01')
  • MSSQL uses DATETRUNC(month, d)
  • Oracle uses TRUNC(d, 'MM')
• Truncate to week:
  • PostgreSQL uses DATE_TRUNC('week', d)
  • MySQL uses DATE(d - INTERVAL WEEKDAY(d) DAY)
  • MSSQL uses DATETRUNC(week, d)
  • Oracle uses TRUNC(d, 'IW')
• Truncate to year:
  • PostgreSQL uses DATE_TRUNC('year', d)
  • MySQL uses DATE_FORMAT(d, '%Y-01-01')
  • MSSQL uses DATETRUNC(year, d)
  • Oracle uses TRUNC(d, 'YYYY')

Number of Shipments Per Month

Use DATE_TRUNC('month') or TO_CHAR(date, 'YYYY-MM') to group shipments by month, then COUNT.

Combining Date Functions

Real analysis chains multiple operations. Here’s a pattern for calculating average session time:

-- Step 1: Calculate duration per session
-- Step 2: Average by user
WITH session_durations AS (
  SELECT
    user_id,
    session_id,
    MAX(timestamp) - MIN(timestamp) AS duration
  FROM page_loads
  GROUP BY user_id, session_id
)
SELECT
  user_id,
  AVG(duration) AS avg_session_time
FROM session_durations
GROUP BY user_id;

Users By Average Session Time

Calculate the time difference between page loads per session, then average by user. A CTE makes this cleaner.

Filtering by Date Range

Combine date arithmetic with filtering for dynamic ranges:

-- Orders from the last 30 days
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- This month's data (dynamic, works any day)
SELECT *
FROM events
WHERE event_date >= DATE_TRUNC('month', CURRENT_DATE);

-- Last complete month (useful for monthly reports)
SELECT *
FROM events
WHERE event_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND event_date < DATE_TRUNC('month', CURRENT_DATE);

Key Takeaways

• Use INTERVAL to add or subtract time from dates
• Date subtraction gives you the difference in days (PostgreSQL) or use DATEDIFF
• DATE_TRUNC rounds dates to period starts — essential for time-series grouping
• Combine DATE_TRUNC with GROUP BY for weekly/monthly/quarterly aggregations
• Date arithmetic syntax varies dramatically by dialect — this is a major gap to watch

What’s Next

You’ve mastered date manipulation. Next, we’ll switch to strings: concatenation, extraction, trimming whitespace, and all the text cleanup operations you’ll need for messy real-world data.