Date & Time Functions Interview Questions & Answers

Each database provides standard and proprietary functions for the current timestamp.

-- ANSI standard (all databases)
SELECT CURRENT_DATE;        -- date only: '2026-06-20'
SELECT CURRENT_TIME;        -- time only: '14:30:00.000000+00'
SELECT CURRENT_TIMESTAMP;   -- date + time: '2026-06-20 14:30:00.000000+00'

-- Postgres: preferred shorthands
SELECT now();               -- same as CURRENT_TIMESTAMP (tz-aware)
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;

-- MySQL
SELECT NOW();               -- '2026-06-20 14:30:00'
SELECT CURDATE();           -- '2026-06-20'
SELECT CURTIME();           -- '14:30:00'
SELECT UTC_TIMESTAMP();     -- always UTC

-- SQL Server
SELECT GETDATE();           -- local server time
SELECT GETUTCDATE();        -- UTC
SELECT SYSDATETIMEOFFSET(); -- with timezone offset

Rule of thumb: always store and compare timestamps in UTC. In Postgres, use now() (returns TIMESTAMPTZ in UTC) rather than LOCALTIMESTAMP (returns naive timestamp in the server timezone).

Date arithmetic is done with intervals or database-specific functions.

-- Postgres: interval arithmetic
SELECT now() + INTERVAL '7 days';             -- 7 days from now
SELECT now() - INTERVAL '1 month';            -- 1 month ago
SELECT '2026-06-20'::DATE + INTERVAL '1 year 3 months';
SELECT '2026-06-20'::DATE - '2026-01-01'::DATE;  -- → 170 (days as integer)

-- MySQL: DATE_ADD / DATE_SUB
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT DATEDIFF('2026-06-20', '2026-01-01');  -- → 170

-- SQL Server: DATEADD / DATEDIFF
SELECT DATEADD(day, 7, GETDATE());
SELECT DATEADD(month, -1, GETDATE());
SELECT DATEDIFF(day, '2026-01-01', '2026-06-20');  -- → 170

Rule of thumb: in Postgres, prefer the +/- operator with INTERVAL literals — it is readable and handles month-end edge cases correctly ('2026-01-31'::DATE + INTERVAL '1 month' → '2026-02-28').

DATE_TRUNC(unit, timestamp) truncates a timestamp to the specified precision — zeroing out all smaller units. This is the standard way to group time-series data by day, week, month, etc.

-- Postgres DATE_TRUNC
SELECT DATE_TRUNC('month', now());           -- → '2026-06-01 00:00:00+00'
SELECT DATE_TRUNC('week',  now());           -- → '2026-06-16 00:00:00+00' (Monday)
SELECT DATE_TRUNC('hour',  now());           -- → '2026-06-20 14:00:00+00'

-- Group orders by month
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(total)                      AS revenue,
       COUNT(*)                        AS orders
FROM   orders
GROUP  BY 1
ORDER  BY 1;

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

-- SQL Server: DATETRUNC (SQL Server 2022+) or DATEFROMPARTS workaround
SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) AS month,
       SUM(total) AS revenue
FROM   orders
GROUP  BY DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1);

Rule of thumb: DATE_TRUNC is the idiomatic Postgres way to bucket time-series data. Pair it with an index on the timestamp column for efficient range scans per bucket.

EXTRACT (standard SQL) and database-specific functions pull individual components out of a date or timestamp.

-- ANSI EXTRACT (all databases)
SELECT EXTRACT(YEAR  FROM now());   -- → 2026
SELECT EXTRACT(MONTH FROM now());   -- → 6
SELECT EXTRACT(DAY   FROM now());   -- → 20
SELECT EXTRACT(DOW   FROM now());   -- → 6 (0=Sunday … 6=Saturday, Postgres)
SELECT EXTRACT(WEEK  FROM now());   -- → ISO week number

-- Postgres shorthand
SELECT DATE_PART('year', now());    -- equivalent to EXTRACT

-- MySQL
SELECT YEAR(now());    -- → 2026
SELECT MONTH(now());   -- → 6
SELECT DAY(now());     -- → 20
SELECT DAYOFWEEK(now()); -- 1=Sunday … 7=Saturday

-- SQL Server
SELECT YEAR(GETDATE());
SELECT DATEPART(WEEKDAY, GETDATE());

Rule of thumb: use EXTRACT for portable code. In Postgres, DATE_PART is equivalent but returns DOUBLE PRECISION — use EXTRACT when you need an integer result for arithmetic.

-- Postgres: AGE() returns a human-readable interval
SELECT AGE('2026-06-20', '1993-03-15');
-- → '33 years 3 mons 5 days'

SELECT AGE(now(), birth_date) FROM users;
-- → age as interval; use EXTRACT to get the years component
SELECT EXTRACT(YEAR FROM AGE(now(), birth_date)) AS age_years FROM users;

-- Postgres: simple subtraction → interval (days)
SELECT '2026-06-20'::DATE - '2026-01-01'::DATE;  -- → 170 (integer days)

-- MySQL
SELECT DATEDIFF('2026-06-20', '2026-01-01');      -- → 170 (days)
SELECT TIMESTAMPDIFF(YEAR, birth_date, NOW())  AS age FROM users;
SELECT TIMESTAMPDIFF(MONTH, '2026-01-01', '2026-06-20');  -- → 5 months

-- SQL Server
SELECT DATEDIFF(day,  '2026-01-01', '2026-06-20');  -- → 170
SELECT DATEDIFF(year, birth_date, GETDATE()) AS age FROM users;

Rule of thumb: use TIMESTAMPDIFF (MySQL) or DATEDIFF (SQL Server) for simple numeric differences. In Postgres, subtract dates directly for integer days; use AGE() when you need a human-readable breakdown.

The fundamental rule: store in UTC, display in the user's timezone. Use timezone-aware column types so conversions are unambiguous.

-- Postgres: TIMESTAMPTZ stores in UTC; AT TIME ZONE converts for display
CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  occurred   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Convert to a specific timezone for display
SELECT occurred AT TIME ZONE 'America/New_York' AS local_time FROM events;
SELECT occurred AT TIME ZONE 'Asia/Kolkata'     AS ist_time   FROM events;

-- Set the session timezone (affects display, not storage)
SET TIME ZONE 'America/New_York';
SELECT now();  -- displayed in ET, still stored as UTC internally

-- MySQL: CONVERT_TZ
SELECT CONVERT_TZ(occurred, 'UTC', 'America/New_York') FROM events;

-- SQL Server: AT TIME ZONE (SQL Server 2016+)
SELECT occurred AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' FROM events;

Rule of thumb: always use TIMESTAMPTZ (Postgres) or an equivalent UTC-storing type. Never store local times without an explicit timezone offset — daylight-saving transitions will corrupt historical data.

-- Postgres: TO_CHAR
SELECT TO_CHAR(now(), 'YYYY-MM-DD');            -- → '2026-06-20'
SELECT TO_CHAR(now(), 'DD/MM/YYYY');            -- → '20/06/2026'
SELECT TO_CHAR(now(), 'Month DD, YYYY');        -- → 'June     20, 2026'
SELECT TO_CHAR(now(), 'FMMonth DD, YYYY');      -- → 'June 20, 2026' (FM removes padding)
SELECT TO_CHAR(now(), 'YYYY-MM-DD HH24:MI:SS'); -- → '2026-06-20 14:30:00'

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');          -- → '2026-06-20'
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i:%s'); -- → '20/06/2026 14:30:00'

-- SQL Server: FORMAT / CONVERT
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');         -- → '2026-06-20'
SELECT CONVERT(VARCHAR, GETDATE(), 23);         -- → '2026-06-20' (style 23)

Rule of thumb: format dates as strings in the application layer when possible, since formatting functions are not portable and locale handling varies. Use TO_CHAR in SQL only for exports or reports generated entirely in the database.

GENERATE_SERIES (Postgres) produces a set of dates or timestamps, making it easy to build a complete date spine and LEFT JOIN to fill in zero values for missing days.

-- Postgres: daily date spine for June 2026
SELECT generate_series(
  '2026-06-01'::DATE,
  '2026-06-30'::DATE,
  INTERVAL '1 day'
) AS day;

-- Fill gaps in daily revenue (days with no orders show 0)
SELECT day::DATE,
       COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series('2026-06-01'::DATE, '2026-06-30'::DATE, '1 day') AS day
LEFT JOIN orders o ON o.created_at::DATE = day::DATE
GROUP BY 1
ORDER BY 1;

MySQL and SQL Server do not have GENERATE_SERIES natively; the common workaround is a numbers/calendar table or a recursive CTE.

Rule of thumb: always use a date spine (GENERATE_SERIES or a calendar table) when charting time-series data — direct GROUP BY on timestamps silently omits days with no records, creating misleading gaps in charts.

An interval represents a duration (not a point in time). Postgres has a rich INTERVAL type; MySQL and SQL Server use numeric + unit keywords instead.

-- Postgres INTERVAL literals
SELECT INTERVAL '1 year 2 months 3 days';
SELECT INTERVAL '90 minutes';
SELECT INTERVAL '2 hours 30 minutes';

-- Arithmetic with intervals
SELECT now() - INTERVAL '1 week';
SELECT now() + INTERVAL '90 days';

-- Storing intervals (Postgres)
CREATE TABLE subscriptions (
  id         SERIAL PRIMARY KEY,
  started_at TIMESTAMPTZ NOT NULL,
  duration   INTERVAL NOT NULL DEFAULT '1 month'
);
SELECT started_at + duration AS expires_at FROM subscriptions;

-- Extract numeric parts from an interval
SELECT EXTRACT(DAYS FROM INTERVAL '1 year 3 days');    -- → 3 (only the days part)
SELECT EXTRACT(EPOCH FROM INTERVAL '2 hours');         -- → 7200 (total seconds)

Rule of thumb: use EXTRACT(EPOCH FROM interval_col) to convert an interval to a total number of seconds for arithmetic or comparison — it is the most reliable way to compare durations of mixed units.

In Postgres, now() (and CURRENT_TIMESTAMP) returns the timestamp at the start of the current transaction — it does not change within the same transaction. clock_timestamp() returns the actual wall-clock time at the moment it is called.

BEGIN;
  SELECT now();             -- → '2026-06-20 14:30:00.000'
  PERFORM pg_sleep(2);
  SELECT now();             -- → '2026-06-20 14:30:00.000'  (same! start of txn)
  SELECT clock_timestamp(); -- → '2026-06-20 14:30:02.005'  (actual time now)
COMMIT;

This matters for:

• Audit columns: DEFAULT now() is fine — all rows in the same transaction get the same "created_at", which is expected.
• Benchmarking / profiling: use clock_timestamp() to measure elapsed time within a transaction.
• Rate limiting: if checking "last request time" within a long transaction, use clock_timestamp() to get the real current time.

Rule of thumb: use now() for audit timestamps (consistent within a transaction is a feature, not a bug). Use clock_timestamp() when you need the actual wall time, such as for elapsed-time calculations or timeouts checked inside a transaction.

Range queries on timestamp columns are common (WHERE created_at >= X AND created_at < Y). The key to making them fast is a B-tree index on the raw timestamp, combined with using range predicates rather than wrapping the column in a function.

-- GOOD: range predicate — index on created_at is used
CREATE INDEX idx_orders_created ON orders (created_at);

SELECT * FROM orders
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-07-01';

-- BAD: function applied to the column — index NOT used
SELECT * FROM orders WHERE DATE_TRUNC('month', created_at) = '2026-06-01';

-- Fix: use a range instead of DATE_TRUNC in the WHERE clause
SELECT * FROM orders
WHERE created_at >= DATE_TRUNC('month', now())
  AND created_at <  DATE_TRUNC('month', now()) + INTERVAL '1 month';
-- OR: functional index on DATE_TRUNC
CREATE INDEX idx_orders_month ON orders (DATE_TRUNC('month', created_at));

Rule of thumb: never wrap a timestamp column in a function in a WHERE clause if an index on the raw column exists. Rewrite the predicate as a range (col >= start AND col < end) so the index is used.

Use the LAG window function to access the previous row's timestamp within a partition, then subtract to get the gap.

-- Time between consecutive logins per user
SELECT user_id,
       logged_in_at,
       LAG(logged_in_at) OVER (
         PARTITION BY user_id
         ORDER BY logged_in_at
       ) AS prev_login,
       logged_in_at - LAG(logged_in_at) OVER (
         PARTITION BY user_id
         ORDER BY logged_in_at
       ) AS gap
FROM   user_logins
ORDER  BY user_id, logged_in_at;

-- Average gap between sessions per user
SELECT user_id,
       AVG(gap) AS avg_session_gap
FROM (
  SELECT user_id,
         logged_in_at - LAG(logged_in_at) OVER (
           PARTITION BY user_id ORDER BY logged_in_at
         ) AS gap
  FROM user_logins
) sub
WHERE gap IS NOT NULL  -- first row per user has no previous
GROUP BY user_id;

Rule of thumb: LAG with a date subtraction is the standard SQL pattern for inter-event time calculations. The first event per partition returns NULL for LAG — always filter or handle that case explicitly.

-- Postgres: MAKE_DATE / MAKE_TIMESTAMP
SELECT MAKE_DATE(2026, 6, 20);                        -- → '2026-06-20'
SELECT MAKE_TIMESTAMP(2026, 6, 20, 14, 30, 0);        -- → '2026-06-20 14:30:00'
SELECT MAKE_TIMESTAMPTZ(2026, 6, 20, 14, 30, 0, 'UTC'); -- tz-aware

-- MySQL: MAKEDATE / MAKETIME / STR_TO_DATE
SELECT MAKEDATE(2026, 171);            -- → '2026-06-20' (day 171 of 2026)
SELECT STR_TO_DATE('20/06/2026', '%d/%m/%Y');  -- → '2026-06-20'

-- SQL Server: DATEFROMPARTS / DATETIMEFROMPARTS
SELECT DATEFROMPARTS(2026, 6, 20);    -- → '2026-06-20'
SELECT DATETIMEFROMPARTS(2026, 6, 20, 14, 30, 0, 0);

-- Practical: reconstruct a date from separate year/month columns
SELECT MAKE_DATE(order_year, order_month, 1) AS period_start
FROM   monthly_summaries;

Rule of thumb: use MAKE_DATE (Postgres) or DATEFROMPARTS (SQL Server) rather than string concatenation + casting — they are cleaner and correctly reject invalid dates (e.g., February 30) instead of silently coercing them.

-- Postgres: AGE() returns an interval
SELECT AGE(birthdate)                   AS age       FROM persons;  -- age from today
SELECT AGE(NOW(), birthdate)            AS age       FROM persons;  -- explicit end date
SELECT EXTRACT(YEAR FROM AGE(birthdate)) AS age_years FROM persons;

-- MySQL: TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age_years FROM persons;
SELECT TIMESTAMPDIFF(MONTH, start_date, end_date) AS months_elapsed FROM projects;

-- SQL Server: DATEDIFF
SELECT DATEDIFF(YEAR,  birthdate, GETDATE()) AS age_years   FROM persons;
SELECT DATEDIFF(DAY,   start_dt,  end_dt)    AS days_open   FROM tickets;
SELECT DATEDIFF(MONTH, start_dt,  end_dt)    AS months_open FROM projects;

Note: DATEDIFF(YEAR, …) in SQL Server counts year boundaries crossed, not full years lived — a birthday on December 31 would add 1 year on January 1 of the next year. Use TIMESTAMPDIFF (MySQL) or AGE (Postgres) for more accurate age calculation.

Rule of thumb: for precise age-in-years, use TIMESTAMPDIFF(YEAR, …) (MySQL) or EXTRACT(YEAR FROM AGE(…)) (Postgres). Never subtract year numbers directly — they don't account for the month/day portion.

Two date ranges [A_start, A_end] and [B_start, B_end] overlap when A_start < B_end AND A_end > B_start. This is the standard overlap predicate — all seven overlap cases satisfy it.

-- Find all bookings that overlap with a requested range
SELECT *
FROM   bookings
WHERE  start_date < '2026-07-01'   -- booking starts before requested end
  AND  end_date   > '2026-06-15';  -- booking ends after requested start

-- Find overlapping pairs within the same table (self-join)
SELECT a.id AS booking_a, b.id AS booking_b
FROM   bookings a
JOIN   bookings b ON a.id < b.id          -- avoid duplicate pairs
       AND a.start_date < b.end_date
       AND a.end_date   > b.start_date;

-- Postgres: use the built-in OVERLAPS operator (inclusive bounds)
SELECT *
FROM   bookings
WHERE  (start_date, end_date) OVERLAPS ('2026-06-15'::DATE, '2026-07-01'::DATE);

Rule of thumb: the overlap condition is A.start < B.end AND A.end > B.start. Make sure to use < / > (exclusive) vs <= / >= (inclusive) consistently with your data model — half-open intervals [start, end) are generally easiest to reason about.