Current date and time
SELECT NOW(); -- 2026-09-17 14:30:00
SELECT CURDATE(); -- 2026-09-17
SELECT CURTIME(); -- 14:30:00
SELECT UTC_TIMESTAMP();
SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(1750000000);
NOW() is fixed for the whole statement; SYSDATE() re-evaluates each time it is called.
Extracting parts
SELECT YEAR(d), MONTH(d), DAY(d), HOUR(d), MINUTE(d), SECOND(d) FROM t;
SELECT MONTHNAME('2026-09-17'); -- 'September'
SELECT DAYNAME('2026-09-17'); -- 'Thursday'
SELECT DAYOFWEEK('2026-09-17'); -- 1 = Sunday
SELECT WEEK(d), QUARTER(d), DAYOFYEAR(d), LAST_DAY(d);
SELECT EXTRACT(YEAR_MONTH FROM '2026-09-17'); -- 202609
Date arithmetic
SELECT DATE_ADD('2026-09-17', INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH);
SELECT NOW() + INTERVAL 1 YEAR;
SELECT DATEDIFF('2026-12-31', '2026-09-17'); -- days between
SELECT TIMESTAMPDIFF(YEAR, '2000-05-10', CURDATE()); -- age in years
SELECT TIMESTAMPDIFF(MINUTE, start_at, end_at) FROM sessions;
Interval units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
Formatting and parsing
SELECT DATE_FORMAT(NOW(), '%d %M %Y'); -- 17 September 2026
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT STR_TO_DATE('17/09/2026', '%d/%m/%Y');
| Code | Meaning | Code | Meaning |
|---|---|---|---|
%Y | 4-digit year | %H | Hour 00-23 |
%m | Month 01-12 | %i | Minutes |
%M | Month name | %s | Seconds |
%d | Day 01-31 | %p | AM/PM |
%W | Weekday name | %r | 12-hour time |
Timezones
SELECT @@global.time_zone, @@session.time_zone;
SET time_zone = '+06:00'; -- Bangladesh
SELECT CONVERT_TZ(NOW(), '+00:00', '+06:00');
TIMESTAMP columns are stored in UTC and converted to the session timezone on read; DATETIME columns are stored exactly as given. For multi-region apps, store UTC and convert in the application.
Practical examples
-- students admitted in the last 30 days
SELECT * FROM students WHERE admitted_on >= CURDATE() - INTERVAL 30 DAY;
-- monthly signups report
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS total
FROM students
GROUP BY month
ORDER BY month;
-- age from date of birth
SELECT name_en, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age FROM students;