← SQL roadmap

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 / codeWhat it doesExample → 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 / dateUTC_DATE() → '2026-11-04'
CONVERT_TZ(dt, from, to)Converts between time zonesCONVERT_TZ('2026-11-04 10:00','+00:00','+05:30') → '2026-11-04 15:30:00'
UNIX_TIMESTAMP(dt)Seconds since 1970-01-01 UTCUNIX_TIMESTAMP('2026-01-01') → 1767225600 (UTC session)
FROM_UNIXTIME(n[, fmt])Unix seconds → datetimeFROM_UNIXTIME(0) → '1970-01-01 00:00:00'
QUARTER(d)Quarter 1–4QUARTER('2026-11-04') → 4
DAYOFYEAR(d) / DAYOFMONTH(d)Day number in year / monthDAYOFYEAR('2026-02-01') → 32
WEEKDAY(d)0 = Monday … 6 = SundayWEEKDAY('2026-11-04') → 2
YEARWEEK(d, mode)Year + week numberYEARWEEK('2026-11-04', 3) → 202645
HOUR / MINUTE / SECOND(t)Time partsHOUR('21:43:10') → 21
TIME_FORMAT(t, fmt)Formats a timeTIME_FORMAT('21:43','%h:%i %p') → '09:43 PM'
TIMEDIFF(a, b)Difference as TIMETIMEDIFF('18:00','09:30') → '08:30:00'
ADDTIME / SUBTIMEAdd / subtract a timeADDTIME('10:00','01:30') → '11:30:00'
SEC_TO_TIME / TIME_TO_SECSeconds ↔ HH:MM:SSSEC_TO_TIME(3725) → '01:02:05'
PERIOD_DIFF(p1, p2)Months between YYYYMM periodsPERIOD_DIFF(202611, 202601) → 10
MAKETIME(h, m, s)Builds a TIMEMAKETIME(9,30,0) → '09:30:00'
%Y %m %d4-digit year, 2-digit month, dayDATE_FORMAT('2026-11-04','%d/%m/%Y') → '04/11/2026'
%b %M %a %WShort/long month, short/long weekdayDATE_FORMAT('2026-11-04','%a, %d %b') → 'Wed, 04 Nov'
%H %h %i %s %p24h hour, 12h hour, minutes, seconds, AM/PMDATE_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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: Date & Time Deep Dive” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.