SQL Date & Time Functions — NOW, DATE_TRUNC, EXTRACT, and Intervals

Dates are not strings

The single most important rule in SQL date handling: store timestamps as TIMESTAMPTZ, not as strings. String dates ('2026-06-20' in a VARCHAR column) cannot be compared with < / >, cannot be indexed efficiently, and break sorting. Use proper date/time types so the database can index, compare, and truncate them correctly.

Getting the current date and time

-- Current timestamp with time zone (use this for created_at / updated_at)
SELECT NOW();                  -- '2026-06-20 14:35:22.451+01'
SELECT CURRENT_TIMESTAMP;      -- same, SQL standard alias

-- Current date only (no time)
SELECT CURRENT_DATE;           -- '2026-06-20'

-- Current time only
SELECT CURRENT_TIME;           -- '14:35:22.451+01'

-- MySQL equivalents
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

-- SQL Server equivalents
SELECT GETDATE(), GETUTCDATE(), SYSDATETIMEOFFSET();

Always use NOW() (which returns the transaction start time) rather than CLOCK_TIMESTAMP() (current clock at the moment of the call) for created_at columns — all rows in one transaction get the same timestamp.

DATE_TRUNC — grouping by time period

DATE_TRUNC rounds a timestamp down to a specified unit. It is the key to grouping rows by hour, day, week, month, or year.

-- Daily revenue for the last 30 days
SELECT
    DATE_TRUNC('day', created_at) AS order_date,
    COUNT(*)                       AS orders,
    SUM(total_amount)              AS revenue
FROM orders
WHERE  created_at >= NOW() - INTERVAL '30 days'
  AND  status = 'completed'
GROUP  BY DATE_TRUNC('day', created_at)
ORDER  BY order_date;

-- Monthly active users (users who placed at least one order per month)
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(DISTINCT customer_id)      AS mau
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

DATE_TRUNC is Postgres syntax. MySQL equivalent: DATE_FORMAT(col, '%Y-%m-01') for month truncation. SQL Server: DATETRUNC('month', col) (SQL Server 2022+) or DATEFROMPARTS(YEAR(col), MONTH(col), 1).

EXTRACT and DATE_PART — individual components

EXTRACT pulls out a single component (year, month, day, hour, weekday) from a timestamp.

-- Orders by day of week (1=Sunday in Postgres, 1=Monday in ISO)
SELECT
    EXTRACT(DOW FROM created_at) AS day_of_week,
    COUNT(*)                      AS order_count
FROM orders
GROUP BY day_of_week
ORDER BY day_of_week;

-- Orders placed in business hours (9am–5pm)
SELECT COUNT(*) FROM orders
WHERE EXTRACT(HOUR FROM created_at AT TIME ZONE 'Europe/London') BETWEEN 9 AND 17;

-- Year-over-year comparison
SELECT
    EXTRACT(YEAR FROM created_at)  AS yr,
    EXTRACT(MONTH FROM created_at) AS mo,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

Date arithmetic with intervals

-- Orders placed in the last 7 days
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';

-- Subscriptions expiring in the next 30 days
SELECT customer_id, plan, expires_at
FROM subscriptions
WHERE expires_at BETWEEN NOW() AND NOW() + INTERVAL '30 days';

-- Calculate how many days since each customer's last order
SELECT
    customer_id,
    MAX(created_at)                        AS last_order,
    NOW() - MAX(created_at)                AS time_since,
    EXTRACT(DAY FROM NOW() - MAX(created_at)) AS days_since
FROM orders
GROUP BY customer_id
ORDER BY days_since DESC;

MySQL uses DATE_ADD / DATE_SUB:

SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

SQL Server uses DATEADD:

SELECT * FROM orders WHERE created_at >= DATEADD(DAY, -7, GETDATE());

TO_CHAR / FORMAT — formatting for display

-- Postgres: format a timestamp as a readable string
SELECT TO_CHAR(created_at, 'DD Mon YYYY HH24:MI') AS formatted
FROM orders;
-- → '20 Jun 2026 14:35'

SELECT TO_CHAR(created_at, 'YYYY-MM') AS month_label FROM orders;
-- → '2026-06'

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(created_at, '%d %b %Y %H:%i') AS formatted FROM orders;

-- SQL Server: FORMAT
SELECT FORMAT(created_at, 'dd MMM yyyy HH:mm') AS formatted FROM orders;

Timezone handling

-- Convert a UTC timestamp to a specific timezone (Postgres)
SELECT
    created_at                                        AS utc_time,
    created_at AT TIME ZONE 'Europe/London'           AS london_time,
    created_at AT TIME ZONE 'America/New_York'        AS new_york_time
FROM orders LIMIT 5;

-- Store all timestamps in UTC; convert at query time
-- Never store local time in TIMESTAMPTZ — the database converts it for you

-- Find orders placed during peak London hours
SELECT COUNT(*) FROM orders
WHERE (created_at AT TIME ZONE 'Europe/London')::TIME
      BETWEEN '09:00' AND '17:00';

AGE and DATEDIFF — time elapsed

-- Postgres: AGE returns an interval
SELECT customer_id, AGE(NOW(), created_at) AS account_age FROM customers;
-- → '1 year 3 mons 15 days'

SELECT EXTRACT(YEAR FROM AGE(birthdate)) AS age_years FROM users;

-- MySQL: TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(DAY, created_at, NOW()) AS days_old FROM orders;

-- SQL Server: DATEDIFF
SELECT DATEDIFF(DAY, created_at, GETDATE()) AS days_old FROM orders;

Recap

Store timestamps as TIMESTAMPTZ and always in UTC — let the database convert to local time at query time with AT TIME ZONE. Use DATE_TRUNC to group by time period for reports and charts. Use EXTRACT to filter by time component (hour of day, day of week). Use INTERVAL arithmetic for relative date ranges rather than hardcoding date strings. Format timestamps for display with TO_CHAR / DATE_FORMAT only in the final SELECT — keep date arithmetic in native date types, not strings.

More ways to practice