Date and Time Functions

Date handling is where SQL dialects diverge most, so interviewers usually accept any reasonable syntax — what they are checking is whether you understand truncation, half-open ranges, and the difference between a date and a timestamp.

Getting the current moment

CURRENT_DATE          -- standard, all engines
CURRENT_TIMESTAMP     -- standard
NOW()                 -- PostgreSQL, MySQL
GETDATE()             -- SQL Server
SYSDATE               -- Oracle

Extracting parts

SELECT
    EXTRACT(YEAR  FROM order_date)  AS year,     -- standard
    EXTRACT(MONTH FROM order_date)  AS month,
    EXTRACT(DOW   FROM order_date)  AS day_of_week,
    DATE_PART('quarter', order_date) AS quarter  -- PostgreSQL
FROM orders;

EXTRACT is the portable one. MySQL also has YEAR(), MONTH(), DAYOFWEEK(); SQL Server uses DATEPART(year, col).

Truncation: the key function for reporting

EXTRACT(MONTH …) gives you 1–12 with no year, so January 2023 and January 2024 collapse into one group. Truncation keeps the date but zeroes everything below the chosen unit, which is what you actually want:

-- PostgreSQL
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
FROM orders GROUP BY 1 ORDER BY 1;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month, SUM(total) FROM orders GROUP BY 1;

-- SQL Server
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month, SUM(total)
FROM orders GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);

Grouping by YEAR(d), MONTH(d) also works and sorts correctly, but truncation gives you a real date you can join and filter on.

Date arithmetic

-- PostgreSQL / standard
order_date + INTERVAL '7 days'
order_date - INTERVAL '1 month'
AGE(CURRENT_DATE, birth_date)                 -- an interval

-- MySQL
DATE_ADD(order_date, INTERVAL 7 DAY)
DATEDIFF(end_date, start_date)                -- days, note the argument order
TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) -- whole years

-- SQL Server
DATEADD(day, 7, order_date)
DATEDIFF(day, start_date, end_date)           -- the OPPOSITE argument order

MySQL's DATEDIFF(end, start) and SQL Server's DATEDIFF(unit, start, end) take their arguments in different orders — a sign error waiting to happen when moving between them.

SQL Server's `DATEDIFF` counts boundary crossings, not elapsed time.

DATEDIFF(year, '2024-12-31', '2025-01-01') is 1, despite one day passing. For a real age in years, compare the full dates.

Age and tenure

-- Portable: subtract years, then adjust if the anniversary has not arrived
SELECT
    name,
    EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date)
        - CASE
              WHEN (EXTRACT(MONTH FROM CURRENT_DATE), EXTRACT(DAY FROM CURRENT_DATE))
                 < (EXTRACT(MONTH FROM birth_date),   EXTRACT(DAY FROM birth_date))
              THEN 1 ELSE 0
          END AS age
FROM people;

The CASE is what makes it correct. Subtracting years alone makes someone born in December a year older than they are for most of the year — a small bug that an interviewer will notice.

Half-open ranges

The most important habit in this whole topic:

-- WRONG on a timestamp column: misses everything after midnight on the 31st
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- RIGHT: half-open, [start, end)
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

BETWEEN is inclusive of both ends, and a bare date literal means midnight, so the last day is effectively excluded. Half-open ranges also compose without gaps or overlaps when you chain periods, and they let the engine use an index — unlike WHERE DATE(order_date) = …, which wraps the column in a function and forces a scan.

Common report shapes

-- Last 30 days
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'

-- Month to date
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)

-- This time last year, for a year-over-year comparison
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 year'

-- Weekday only
WHERE EXTRACT(DOW FROM order_date) BETWEEN 1 AND 5

Filling in days with no rows — so a report shows a zero rather than skipping the date — needs a generated date series left-joined to the data. That is covered in CTEs and gaps and islands.

Time zones

TIMESTAMP WITH TIME ZONE (PostgreSQL's timestamptz) stores an absolute instant; TIMESTAMP stores wall-clock text with no zone. Store instants in UTC and convert on display — order_date AT TIME ZONE 'Asia/Kolkata' — because "which day was this" depends on the viewer's zone, and daily aggregates computed in the wrong zone are wrong by up to a day at the boundaries. Mentioning this unprompted reads as production experience.

Mistakes that cost the interview

• `BETWEEN` on a timestamp range, losing the final day.
• `WHERE YEAR(col) = 2024`, which cannot use an index.
• Grouping by month without the year.
• Argument order in `DATEDIFF`, which differs by engine.
• Age by year subtraction alone.
• Ignoring time zones on daily aggregates.

Practise these on the SQL sheet.