Working with Dates in PostgreSQL

PostgreSQL has excellent date and time support, including timezone-aware timestamps, interval arithmetic, and powerful extraction functions. Understanding these is critical for reporting and time-series queries.

Current Date and Time

SELECT
  NOW(),                    -- Full timestamp with timezone
  CURRENT_DATE,             -- Date only (no time)
  CURRENT_TIME,             -- Time only (no date)
  CURRENT_TIMESTAMP;        -- Same as NOW()

Interval Arithmetic

-- Orders from the last 30 days
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

-- Adding intervals
SELECT
  NOW() + INTERVAL '1 hour',
  NOW() - INTERVAL '3 months',
  CURRENT_DATE + INTERVAL '1 year';

-- Difference between two dates
SELECT AGE('2025-12-31', '2025-01-01');
-- Result: 11 mons 30 days

Intervals in PostgreSQL are flexible. You can write '1 year 2 months 3 days' or combine them with arithmetic operators on any date or timestamp column.

Extracting Parts of a Date

SELECT
  EXTRACT(YEAR FROM created_at)  AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  EXTRACT(DOW FROM created_at)   AS day_of_week,  -- 0=Sun, 6=Sat
  EXTRACT(EPOCH FROM created_at) AS unix_timestamp
FROM orders
LIMIT 5;

Truncating Dates with DATE_TRUNC

-- Monthly revenue report
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Weekly active users
SELECT
  DATE_TRUNC('week', last_login) AS week,
  COUNT(DISTINCT id) AS active_users
FROM users
GROUP BY DATE_TRUNC('week', last_login)
ORDER BY week DESC;

DATE_TRUNC rounds a timestamp down to the specified precision. It is the go-to function for time-series grouping and is more readable than combining EXTRACT with GROUP BY.

Common Use Cases

• Building time-series dashboards with DATE_TRUNC
• Filtering records by relative time windows (last 7 days, this month)
• Calculating user age or account tenure with AGE()
• Converting between timezones with AT TIME ZONE