Topic 36 of 52 · Intermediate
Functions Reference: Date & Time Deep Dive
Formatting codes, conversions, time zones, week/quarter maths and common date patterns.
Concept
MySQL stores DATE (YYYY-MM-DD), TIME, DATETIME and TIMESTAMP (stored in UTC, shown in the session time zone).
DATE_FORMAT turns a date into text using % codes; STR_TO_DATE does the opposite. Use them at the edges (display/import), and keep real date types in tables.
For ranges, compare with half-open intervals (>= start AND < next_start) so indexes stay usable and you never miss the last second of a day.
Cheat sheet
| Function / code | What it does | Example → result |
|---|---|---|
| SYSDATE() | Time at the moment of execution (NOW is fixed per statement) | SYSDATE() → '2026-11-04 21:43:10' |
| UTC_TIMESTAMP() / UTC_DATE() | Current UTC time / date | UTC_DATE() → '2026-11-04' |
| CONVERT_TZ(dt, from, to) | Converts between time zones | CONVERT_TZ('2026-11-04 10:00','+00:00','+05:30') → '2026-11-04 15:30:00' |
| UNIX_TIMESTAMP(dt) | Seconds since 1970-01-01 UTC | UNIX_TIMESTAMP('2026-01-01') → 1767225600 (UTC session) |
| FROM_UNIXTIME(n[, fmt]) | Unix seconds → datetime | FROM_UNIXTIME(0) → '1970-01-01 00:00:00' |
| QUARTER(d) | Quarter 1–4 | QUARTER('2026-11-04') → 4 |
| DAYOFYEAR(d) / DAYOFMONTH(d) | Day number in year / month | DAYOFYEAR('2026-02-01') → 32 |
| WEEKDAY(d) | 0 = Monday … 6 = Sunday | WEEKDAY('2026-11-04') → 2 |
| YEARWEEK(d, mode) | Year + week number | YEARWEEK('2026-11-04', 3) → 202645 |
| HOUR / MINUTE / SECOND(t) | Time parts | HOUR('21:43:10') → 21 |
| TIME_FORMAT(t, fmt) | Formats a time | TIME_FORMAT('21:43','%h:%i %p') → '09:43 PM' |
| TIMEDIFF(a, b) | Difference as TIME | TIMEDIFF('18:00','09:30') → '08:30:00' |
| ADDTIME / SUBTIME | Add / subtract a time | ADDTIME('10:00','01:30') → '11:30:00' |
| SEC_TO_TIME / TIME_TO_SEC | Seconds ↔ HH:MM:SS | SEC_TO_TIME(3725) → '01:02:05' |
| PERIOD_DIFF(p1, p2) | Months between YYYYMM periods | PERIOD_DIFF(202611, 202601) → 10 |
| MAKETIME(h, m, s) | Builds a TIME | MAKETIME(9,30,0) → '09:30:00' |
| %Y %m %d | 4-digit year, 2-digit month, day | DATE_FORMAT('2026-11-04','%d/%m/%Y') → '04/11/2026' |
| %b %M %a %W | Short/long month, short/long weekday | DATE_FORMAT('2026-11-04','%a, %d %b') → 'Wed, 04 Nov' |
| %H %h %i %s %p | 24h hour, 12h hour, minutes, seconds, AM/PM | DATE_FORMAT(NOW(),'%h:%i %p') → '09:43 PM' |
Code (SQL · MySQL 8)
-- Orders this month (index friendly)
SELECT * FROM orders
WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01') + INTERVAL 1 MONTH;
-- Age in full years and display formats
SELECT name,
TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age,
DATE_FORMAT(dob, '%d %b %Y') AS dob_pretty,
CONVERT_TZ(last_login, '+00:00', '+05:30') AS last_login_ist
FROM users;
-- Revenue per quarter
SELECT YEAR(created_at) AS yr, QUARTER(created_at) AS qtr, SUM(amount) AS revenue
FROM orders
GROUP BY yr, qtr
ORDER BY yr, qtr;Remember
- NOW() is fixed for a statement; SYSDATE() changes per call.
- TIMESTAMP is stored in UTC; DATETIME stores exactly what you insert.
- Use INTERVAL arithmetic (d + INTERVAL 7 DAY) instead of adding raw numbers.
- Named time zones in CONVERT_TZ need the time-zone tables loaded; offsets like '+05:30' always work.
Interview questions
DATETIME vs TIMESTAMP?
TIMESTAMP converts to UTC for storage and back to the session zone, range 1970–2038; DATETIME stores the literal value, range 1000–9999, no conversion.
How do you get the first and last day of the current month?
First: DATE_FORMAT(CURDATE(), '%Y-%m-01'); last: LAST_DAY(CURDATE()).
Find users who signed up in the last 7 days.
WHERE created_at >= NOW() - INTERVAL 7 DAY.
Lesson test
Take an 8-question test on “Functions Reference: Date & Time Deep Dive” — concepts, output prediction, bug spotting and interview questions.