Date and Time Functions

Working with dates and times is one of the most common — and most error-prone — tasks in SQL. PostgreSQL provides a powerful set of date and time functions for retrieving the current moment, calculating durations, extracting components, truncating to periods, and performing arithmetic with intervals. Understanding these functions is critical for any backend engineer working with scheduling, analytics, reporting, or audit trails.

1. Introduction

Date/time functions let you get the current moment (NOW, CURRENT_DATE), extract components (EXTRACT year/month/day), truncate to periods (DATE_TRUNC), calculate durations (AGE, interval subtraction), and do arithmetic (INTERVAL '30 days'). These are the backbone of reporting dashboards, SLA monitoring, user analytics, and scheduling systems. Misunderstanding timezones or timestamp types is one of the most common sources of production bugs.

2. Why It Matters

• Reporting: Aggregating orders per month, active users per day, revenue per quarter — all require DATE_TRUNC and EXTRACT.
• SLA monitoring: Computing response times, resolution durations, and uptime uses interval arithmetic.
• Timezone bugs: TIMESTAMP vs TIMESTAMPTZ confusion causes off-by-hours errors that only appear in production across time zones.
• Interview favorite: Date range filtering, duration calculations, and gap-filling with GENERATE_SERIES are heavily tested.

3. Real-World Analogy

Date/time functions are like a world clock + calendar + stopwatch combined. NOW() checks the current world clock. EXTRACT pulls one number off the calendar (what month is it?). DATE_TRUNC snaps to the start of a calendar page (first of the month). AGE() reads the stopwatch showing how long since an event. INTERVAL arithmetic is like adding "2 weeks" to a calendar date and flipping pages forward. The timezone piece is like knowing your colleague's 3 PM is your 9 AM.

4. How It Works

Core date/time function categories:

NOW() vs CLOCK_TIMESTAMP(): NOW() is fixed at transaction start. CLOCK_TIMESTAMP() returns the real-time clock value at each call — use it inside long transactions when you need the actual current time.

5. Internal Architecture

How PostgreSQL stores and processes timestamps:

Storage:
  TIMESTAMP      → Stores raw date+time (8 bytes, microsecond precision)
                    No timezone info — "2026-06-12 14:00:00" is ambiguous
  TIMESTAMPTZ    → Stores UTC internally (8 bytes)
                    Input: converts from session timezone to UTC
                    Output: converts from UTC to session timezone
                    Always unambiguous — use this in production!
NOW() Execution:
  1. Transaction starts → engine records start_timestamp
  2. NOW() called anywhere in transaction → returns start_timestamp
  3. Same value across entire transaction (consistent snapshot)
  4. CLOCK_TIMESTAMP() → reads system clock on each call (different!)
DATE_TRUNC('month', ts):
  1. Reads timestamp value
  2. Zeros out day to 1, hour/min/sec to 0
  3. Returns: 2026-06-01 00:00:00
  4. Result type is same as input (TIMESTAMP or TIMESTAMPTZ)
EXTRACT(EPOCH FROM interval):
  1. Takes interval (e.g., end_time - start_time)
  2. Converts to total seconds as DOUBLE PRECISION
  3. Divide by 60 for minutes, 3600 for hours, 86400 for days

6. Visual Explanation

7. Practical Example

-- Monthly order summary for last 12 months
SELECT
  TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM') AS month,
  COUNT(*) AS order_count,
  SUM(total) AS revenue,
  ROUND(AVG(total)::numeric, 2) AS avg_order_value
FROM orders
WHERE created_at >= DATE_TRUNC('month', NOW() - INTERVAL '12 months')
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
-- User cohort: days since signup
SELECT user_id, username,
  created_at::date AS signup_date,
  CURRENT_DATE - created_at::date AS days_active,
  AGE(created_at) AS account_age
FROM users
WHERE created_at >= NOW() - INTERVAL '90 days';
-- Average response time in minutes
SELECT department,
  ROUND(AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60)::numeric, 1)
    AS avg_response_minutes
FROM support_tickets
WHERE resolved_at IS NOT NULL
GROUP BY department;
-- 12-month series with gap filling (includes zero-order months)
WITH months AS (
  SELECT GENERATE_SERIES(
    DATE_TRUNC('month', NOW() - INTERVAL '11 months'),
    DATE_TRUNC('month', NOW()),
    INTERVAL '1 month'
  ) AS month_start
)
SELECT TO_CHAR(m.month_start, 'YYYY-MM') AS month,
  COALESCE(SUM(o.total), 0) AS revenue
FROM months m
LEFT JOIN orders o
  ON o.created_at >= m.month_start
  AND o.created_at < m.month_start + INTERVAL '1 month'
GROUP BY m.month_start ORDER BY m.month_start;
-- Business days between two dates (exclude weekends)
SELECT COUNT(*) AS business_days
FROM GENERATE_SERIES('2026-01-01'::date, '2026-06-12'::date, INTERVAL '1 day')
  AS d(dt)
WHERE EXTRACT(DOW FROM d.dt) NOT IN (0, 6);

8. Common Mistakes

• TIMESTAMP vs TIMESTAMPTZ: TIMESTAMP has no timezone awareness — '2026-06-12 14:00:00' is ambiguous. Always use TIMESTAMPTZ in production for unambiguous UTC storage.
• Comparing TIMESTAMPTZ with date literals: WHERE created_at = '2026-06-12' may miss records due to timezone conversion. Use WHERE created_at::date = '2026-06-12'.
• NOW() in long transactions: NOW() is fixed at transaction start. Use CLOCK_TIMESTAMP() if you need the actual current time during long-running transactions.
• Date subtraction returns INTERVAL, not a number: Use EXTRACT(EPOCH FROM (end - start)) / 86400 for days as a number.

9. Quick Quiz

10. Scenario-Based Challenge

11. Debugging Exercise

This query should find orders from "today" but misses orders placed earlier today in a different timezone. Why?

-- BUG: Misses orders depending on timezone
SELECT * FROM orders
WHERE created_at = CURRENT_DATE;
-- created_at is TIMESTAMPTZ, CURRENT_DATE is DATE
-- Comparison casts CURRENT_DATE to midnight in session timezone
-- Orders after midnight UTC but before midnight local are missed
-- FIX: Use range comparison with timezone awareness
SELECT * FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day';
-- BETTER: Explicit timezone
SELECT * FROM orders
WHERE created_at >= (CURRENT_DATE AT TIME ZONE 'UTC')
  AND created_at < (CURRENT_DATE + INTERVAL '1 day' AT TIME ZONE 'UTC');

12. Interview Questions

13. Production Considerations

• Always use TIMESTAMPTZ: Store all timestamps as TIMESTAMPTZ. Set server timezone to UTC. Convert to user's timezone only at the presentation layer.
• Index date ranges: For time-series queries, create B-tree indexes on timestamp columns. Range queries (WHERE ts >= ... AND ts < ...) use index range scans efficiently.
• Partition by time: For tables with billions of time-series rows, use range partitioning by month/week. Queries on recent data only scan relevant partitions.
• DATE_TRUNC('week') uses ISO weeks: Weeks start on Monday by default. If your business week starts on Sunday, adjust: DATE_TRUNC('week', ts + INTERVAL '1 day') - INTERVAL '1 day'.