Date and Time Functions

Last Updated: June 12, 2026 • 18 min read

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:

Category	Functions	Example
Current time	NOW(), CURRENT_DATE	NOW() → 2026-06-12 14:30:00-04
Extract	EXTRACT(field FROM ts)	EXTRACT(MONTH FROM ts) → 6
Truncate	DATE_TRUNC('field', ts)	DATE_TRUNC('month', ts) → 2026-06-01
Duration	AGE(), interval subtraction	AGE('2000-01-01') → 26 years
Arithmetic	INTERVAL, +, -	NOW() - INTERVAL '30 days'
Format	TO_CHAR, TO_DATE	TO_CHAR(NOW(), 'YYYY-MM-DD')

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:

6. Visual Explanation

7. Practical Example

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.

Interview Insight

"Write a query showing orders per month for the last 12 months." Use GROUP BY DATE_TRUNC('month', created_at). Follow-up: "Include months with zero orders?" — Use GENERATE_SERIES with LEFT JOIN.

Common Pitfall

Timezone confusion: TIMESTAMP stores literal values and ignores time zones. TIMESTAMPTZ normalizes to UTC on storage and converts on retrieval. Always use TIMESTAMPTZ — a TIMESTAMP value is ambiguous (is that UTC? EST? PST?).

9. Quick Quiz

Q1: What does DATE_TRUNC('week', '2026-06-12'::date) return?

A) Sunday June 7 B) Monday June 8 C) Thursday June 12

Answer: B — DATE_TRUNC('week', ...) returns Monday (ISO week start) of that week.

Q2: Inside a long transaction, what's the difference between NOW() and CLOCK_TIMESTAMP()?

A) They're identical B) NOW() is fixed at tx start, CLOCK_TIMESTAMP() updates C) CLOCK_TIMESTAMP() is fixed

Answer: B — NOW() returns the transaction start time. CLOCK_TIMESTAMP() returns the actual current time at each call.

10. Scenario-Based Challenge

Build a Support SLA Dashboard

Your support_tickets table has created_at, resolved_at (nullable), priority, and department. Build queries to: (1) compute average resolution time in hours per department, (2) find tickets open more than 48 hours using NOW(), (3) show a weekly trend of ticket volume for the last 8 weeks using DATE_TRUNC, and (4) count tickets created on weekends vs weekdays using EXTRACT(DOW).

11. Debugging Exercise

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

12. Interview Questions

Q: What's the difference between TIMESTAMP and TIMESTAMPTZ?

A: TIMESTAMP stores a raw date+time with no timezone context — "2026-06-12 14:00:00" could be any timezone. TIMESTAMPTZ converts input to UTC for storage and converts back to the session's timezone on output. Always use TIMESTAMPTZ in production to avoid ambiguity.

Q: How do you include months with zero data in a time-series report?

A: Use GENERATE_SERIES to create a complete range of periods, then LEFT JOIN your data table. Use COALESCE to replace NULL aggregates with 0. This ensures every month appears in the output even with no matching rows.

Q: How do you calculate the duration between two timestamps in hours?

A: Subtract timestamps to get an interval, then extract epoch: EXTRACT(EPOCH FROM (end_ts - start_ts)) / 3600.0. Don't just subtract — the result is an interval, not a number.

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'.