Dates, times and time zones

MySQL has DATE, TIME, DATETIME, TIMESTAMP and YEAR. The two that cause the interview questions are DATETIME and TIMESTAMP.

DATETIME versus TIMESTAMP

• DATETIME stores the calendar date and clock time you give it, unchanged. It knows nothing about time zones. Range: years 1000 to 9999.
• TIMESTAMP converts the value from the session time_zone to UTC on the way in, and from UTC to the session time_zone on the way out. Its range ends at 2038-01-19 03:14:07 UTC.

1CREATE TABLE events (id INT PRIMARY KEY, dt DATETIME NULL, ts TIMESTAMP NULL);2-- A helper that runs a statement and records the error instead of stopping.3CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);4CREATE PROCEDURE try_sql(IN stmt TEXT)5BEGIN6  DECLARE EXIT HANDLER FOR SQLEXCEPTION7  BEGIN8    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;9    INSERT INTO attempts (statement, outcome)10    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));11  END;12  SET @sql = stmt;13  PREPARE s FROM @sql;14  EXECUTE s;15  DEALLOCATE PREPARE s;16  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');17END;

1SET time_zone = '+00:00';2INSERT INTO events VALUES (1, '2026-03-01 12:00:00', '2026-03-01 12:00:00');3-- One second past the TIMESTAMP range, into each column type.4CALL try_sql('INSERT INTO events (id, dt) VALUES (2, ''2038-01-19 03:14:08'')');5CALL try_sql('INSERT INTO events (id, ts) VALUES (3, ''2038-01-19 03:14:08'')');6 7SET time_zone = '+04:00';8SELECT id, dt, ts,9       (SELECT GROUP_CONCAT(CONCAT(IF(n = 1, 'DATETIME: ', 'TIMESTAMP: '), outcome)10                            ORDER BY n SEPARATOR ' / ')11        FROM attempts) AS one_second_past_203812FROM events13WHERE id = 1;

The same text went into both columns at UTC. Read back under +04:00, the DATETIME is unchanged and the TIMESTAMP has moved four hours. One second past the 2038 limit, the DATETIME insert succeeds and the TIMESTAMP insert fails with error 1292. Neither is wrong; they answer different questions. TIMESTAMP means "this instant", DATETIME means "this wall-clock reading". A common convention is to keep every server and session on UTC and store instants in DATETIME, which avoids the 2038 limit.

Both types take an optional precision for fractional seconds, DATETIME(3) for milliseconds up to DATETIME(6). Without it, fractions are rounded, not cut off.

Arithmetic

1SELECT '2026-01-31' + INTERVAL 1 MONTH                      AS jan31_plus_1m,2       '2024-01-31' + INTERVAL 1 MONTH                      AS leap_year,3       TIMESTAMPDIFF(MONTH, '2026-01-31', '2026-02-28')     AS whole_months,4       TIMESTAMPDIFF(YEAR,  '2000-02-29', '2026-02-28')     AS age,5       STR_TO_DATE('03/04/2026', '%d/%m/%Y')                AS parsed,6       CAST('2026-03-31 23:59:59.5' AS DATETIME)            AS rounded;

• Adding a month to 31 January gives the last day of February: MySQL clamps to the end of the shorter month (28th in 2026, 29th in 2024).
• TIMESTAMPDIFF(MONTH, ...) counts complete months, so 31 January to 28 February is 0 months.
• TIMESTAMPDIFF(YEAR, birth, today) is the correct way to compute an age; someone born on 29 February 2000 is still 25 on 28 February 2026.
• STR_TO_DATE parses with an explicit format, so '03/04/2026' with %d/%m/%Y is 3 April, not 4 March.

The half-open range

To select a month, compare the column with two boundaries: inclusive start, exclusive end of the following period.

WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'

This has no edge cases: it does not care whether the column has fractional seconds, and it leaves the column bare, so an index on placed_at can be searched as a range. Wrapping the column, as in WHERE MONTH(placed_at) = 3, forces MySQL to compute the function for every row. The alternatives with BETWEEN look equivalent and are not:

1CREATE TABLE orders (id INT PRIMARY KEY, placed_at DATETIME(3));2INSERT INTO orders VALUES3  (1, '2026-03-31 09:00:00.000'),4  (2, '2026-03-31 23:59:59.500'),5  (3, '2026-04-01 00:00:00.000'),6  (4, '2026-03-01 00:00:00.000');

1SELECT2  (SELECT GROUP_CONCAT(id ORDER BY id) FROM orders3    WHERE placed_at BETWEEN '2026-03-01' AND '2026-03-31')          AS between_dates,4  (SELECT GROUP_CONCAT(id ORDER BY id) FROM orders5    WHERE placed_at BETWEEN '2026-03-01' AND '2026-03-31 23:59:59') AS between_235959,6  (SELECT GROUP_CONCAT(id ORDER BY id) FROM orders7    WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01')   AS half_open;

BETWEEN '2026-03-01' AND '2026-03-31' means "up to 31 March at midnight" and drops the whole last day. Ending at '23:59:59' still drops an order at 23:59:59.500. Only the half-open range finds all three March orders.

Common mistakes

• BETWEEN with a date as the upper bound on a DATETIME. It stops at midnight at the start of that day, as order 1 shows.
• Ending a range at 23:59:59. Fractional seconds slip past, as order 2 shows.
• Casting to a lower precision and expecting truncation. CAST('2026-03-31 23:59:59.5' AS DATETIME) rounds up to the next day.
• Using TIMESTAMP for dates far in the future (contract end dates, expiry dates past 2038). The insert fails with error 1292, as the first example shows.

What interviewers ask

• DATETIME or TIMESTAMP? TIMESTAMP converts through UTC using the session time zone and ends in 2038; DATETIME stores the value as given.
• How do you select all rows from March efficiently? A half-open range on the bare column, which is correct for any precision and can use an index.