Home/Learn/SQL/Date/Time Functions

Date/Time Functions

Intermediate

DML & Querying

Date/time functions (NOW, DATEDIFF, DATE_TRUNC, EXTRACT, DATE_ADD) are essential for time-series analysis, reporting windows, and expiry/scheduling logic in backend systems.

Overview

Date/time handling is one of the most error-prone areas in SQL because of timezone differences, implicit casting, and dialect variation. PostgreSQL's TIMESTAMPTZ stores UTC and converts to the session timezone on display; TIMESTAMP stores no timezone. NOW() returns the current time at transaction start (consistent within a transaction); CLOCK_TIMESTAMP() returns the current wall-clock time. DATE_TRUNC (PostgreSQL) and DATE_FORMAT (MySQL) truncate timestamps to a given precision — essential for grouping events by day, week, or month. INTERVAL arithmetic is cleaner than DATEDIFF for date range calculations. Always work in UTC at the database layer and convert to local time in the application.

Core Date/Time Functions

These functions cover the most common operations: getting the current time, truncating to a period, extracting a field, and adding/subtracting intervals.

SQL — date/time functions (PostgreSQL + MySQL)

-- Current time
SELECT NOW();               -- transaction start time (consistent in tx)
SELECT CURRENT_TIMESTAMP;   -- ANSI equivalent of NOW()
SELECT CLOCK_TIMESTAMP();   -- actual wall-clock time (PostgreSQL)

-- DATE_TRUNC: truncate to start of period (PostgreSQL)
SELECT DATE_TRUNC('day',   created_at) AS day_start   FROM orders;
SELECT DATE_TRUNC('month', created_at) AS month_start FROM orders;
SELECT DATE_TRUNC('week',  created_at) AS week_start  FROM orders;

-- MySQL equivalent: DATE_FORMAT or DATE
SELECT DATE(created_at)                          AS day_start   FROM orders;
SELECT DATE_FORMAT(created_at, '%Y-%m-01')       AS month_start FROM orders;

-- EXTRACT: pull a single field out of a timestamp
SELECT EXTRACT(YEAR  FROM created_at) AS yr,
       EXTRACT(MONTH FROM created_at) AS mo,
       EXTRACT(DOW   FROM created_at) AS day_of_week  -- 0=Sunday (PostgreSQL)
FROM orders;

-- MySQL: YEAR(), MONTH(), DAYOFWEEK()
SELECT YEAR(created_at), MONTH(created_at) FROM orders;

-- Interval arithmetic: orders created in the last 30 days
SELECT id, created_at FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';          -- PostgreSQL
-- MySQL:
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Time-Series Grouping and DATEDIFF

Grouping events by time bucket (daily/monthly revenue, weekly active users) is a core reporting pattern. DATEDIFF calculates the gap between two dates. Always store and compare in UTC to avoid DST edge cases.

SQL — time-series grouping, DATEDIFF, and index pitfall

-- Daily order count and revenue (PostgreSQL)
SELECT
    DATE_TRUNC('day', created_at)::DATE   AS order_date,
    COUNT(*)                               AS order_count,
    SUM(total_amount)                      AS daily_revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
  AND status = 'completed'
GROUP BY 1
ORDER BY 1;

-- DATEDIFF: days between order creation and delivery
SELECT
    id,
    created_at,
    delivered_at,
    delivered_at::DATE - created_at::DATE     AS days_to_deliver  -- PostgreSQL
FROM orders
WHERE delivered_at IS NOT NULL;

-- MySQL DATEDIFF (days only)
SELECT id, DATEDIFF(delivered_at, created_at) AS days_to_deliver FROM orders;

-- AGE function (PostgreSQL): human-readable interval
SELECT id, AGE(NOW(), hired_at) AS tenure FROM employees;
-- e.g. "3 years 2 months 15 days"

-- Anti-pattern: using a function on an indexed timestamp in WHERE
SELECT * FROM orders WHERE YEAR(created_at) = 2024;  -- MySQL: index on created_at unused!
-- Fix: range condition
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Key Points to Remember

1Store all timestamps in UTC; convert to local time in the application layer.
2NOW() returns transaction start time — consistent within a transaction.
3DATE_TRUNC is the key function for grouping time-series data by day/week/month.
4Using functions on indexed date columns in WHERE (YEAR(col)=2024) prevents index use.
5Fix: use range predicates (col >= '2024-01-01' AND col < '2025-01-01').
6TIMESTAMPTZ (PostgreSQL) stores timezone offset; TIMESTAMP does not — prefer TIMESTAMPTZ.

Interview Questions

How would you write a query to get daily revenue for the past 30 days?

MediumAmazon

Why does WHERE YEAR(created_at) = 2024 prevent index usage in MySQL?

MediumFlipkart

What is the difference between NOW() and CLOCK_TIMESTAMP() in PostgreSQL?

HardGoogle

How do you handle timezone conversion for a multi-region application?

HardUber

Ask Aria about Date/Time Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

String Functions

INSERT, UPDATE & DELETE