Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
MySQL provides a full suite of date and time functions. The main types you’ll work with are DATE (date only), TIME (time only), DATETIME (date + time, no timezone), and TIMESTAMP (date + time, stored in UTC, displayed in session timezone). See Timestamps and Time Zones in MySQL for timezone handling details.

​
Getting the Current Date and Time

SELECT NOW();            -- '2024-06-15 14:30:00'  (DATETIME, session timezone)
SELECT SYSDATE();        -- same, but evaluated at call time, not statement start
SELECT CURRENT_TIMESTAMP; -- synonym for NOW()

SELECT CURDATE();        -- '2024-06-15'  (DATE only)
SELECT CURTIME();        -- '14:30:00'    (TIME only)
SELECT UTC_TIMESTAMP();  -- '2024-06-15 21:30:00'  (UTC)
NOW() is evaluated once at the start of a statement — all rows in the same INSERT get the same timestamp. SYSDATE() is evaluated each time it’s called.

​
Formatting Dates

DATE_FORMAT converts a date or datetime to a string using a format string: 
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');          -- '2024-06-15'
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');          -- '15/06/2024'
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');         -- 'June 15, 2024'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- '2024-06-15 14:30:00'
Common format specifiers:
SpecifierMeaningExample
%Y4-digit year2024
%y2-digit year24
%mMonth (01–12)06
%MMonth nameJune
%dDay (01–31)15
%HHour (00–23)14
%iMinute (00–59)30
%sSecond (00–59)00
%WWeekday nameSaturday
%wWeekday (0=Sunday)6
%jDay of year (001–366)167
%UWeek number (0–53, week starts Sunday)24

​
Parsing Strings to Dates

STR_TO_DATE converts a string to a date using the same format specifiers: 
SELECT STR_TO_DATE('15/06/2024', '%d/%m/%Y');         -- '2024-06-15'
SELECT STR_TO_DATE('June 15, 2024', '%M %d, %Y');     -- '2024-06-15'
SELECT STR_TO_DATE('2024-06-15 14:30:00', '%Y-%m-%d %H:%i:%s');  -- DATETIME
Returns NULL if the string doesn’t match the format.

​
Date Arithmetic

DATE_ADD / DATE_SUB — add or subtract an interval: 
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);        -- 7 days from now
SELECT DATE_ADD(NOW(), INTERVAL 3 MONTH);      -- 3 months from now
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);       -- 1 year ago
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- '2024-02-29' (end of Feb)
Interval units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. Shorthand with + and - operators: 
SELECT NOW() + INTERVAL 1 DAY;
SELECT '2024-06-15' - INTERVAL 1 MONTH;
DATEDIFF — days between two dates: 
SELECT DATEDIFF('2024-12-31', '2024-06-15');  -- 199
SELECT DATEDIFF(NOW(), created_at) AS days_old FROM orders;
DATEDIFF only counts days, not time. For time-aware differences, use TIMESTAMPDIFF: 
SELECT TIMESTAMPDIFF(HOUR, '2024-06-15 08:00', '2024-06-15 14:30');  -- 6
SELECT TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS minutes_ago FROM events;
TIMESTAMPDIFF(unit, from, to) — note that from comes before to.

​
Extracting Date Parts

EXTRACT / YEAR / MONTH / DAY: 
SELECT YEAR(NOW());     -- 2024
SELECT MONTH(NOW());    -- 6
SELECT DAY(NOW());      -- 15 (same as DAYOFMONTH)
SELECT HOUR(NOW());     -- 14
SELECT MINUTE(NOW());   -- 30

-- EXTRACT uses interval unit names
SELECT EXTRACT(YEAR FROM NOW());        -- 2024
SELECT EXTRACT(HOUR FROM NOW());        -- 14
SELECT EXTRACT(YEAR_MONTH FROM NOW());  -- 202406
DAYOFWEEK / DAYOFYEAR / WEEK: 
SELECT DAYOFWEEK(NOW());   -- 7  (1=Sunday, 7=Saturday)
SELECT WEEKDAY(NOW());     -- 5  (0=Monday, 6=Sunday)
SELECT DAYOFYEAR(NOW());   -- 167
SELECT WEEK(NOW());        -- 24  (week 0–53, Sunday-based)
SELECT YEARWEEK(NOW());    -- 202424

​
Truncating to a Period

To group data by month, week, or day, truncate the datetime to the period boundary: 
-- Group orders by month
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

-- Group by week
SELECT YEARWEEK(created_at) AS week, COUNT(*) AS orders
FROM orders
GROUP BY week;

-- Truncate to start of month
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');  -- '2024-06-01'

​
Unix Timestamps

SELECT UNIX_TIMESTAMP();                          -- seconds since epoch (session timezone)
SELECT UNIX_TIMESTAMP('2024-06-15 14:30:00');     -- datetime to epoch (session timezone)
SELECT FROM_UNIXTIME(1718451000);                 -- epoch to DATETIME
SELECT FROM_UNIXTIME(1718451000, '%Y-%m-%d');     -- epoch to formatted string

​
Common Patterns

Records from the last 30 days: 
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL 30 DAY;
Records from the current month: 
SELECT * FROM orders
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND created_at <  DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01');
Age from a birthdate: 
SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age FROM users;

​
Frequently Asked Questions

​
What’s the difference between NOW() and SYSDATE()?

NOW() returns the timestamp when the statement began executing — all rows in a single INSERT or UPDATE get the same value. SYSDATE() returns the actual clock time at the moment it’s called, so rows inserted in a loop get different values. For audit timestamps where consistency within a transaction matters, NOW() is almost always the right choice.

​
Why does DATE_ADD with INTERVAL 1 MONTH behave oddly at month-end?

MySQL clamps to the last valid day. DATE_ADD('2024-01-31', INTERVAL 1 MONTH) returns 2024-02-29 (leap year) or 2024-02-28. This is expected behavior — there’s no “31st of February.” If you need predictable month arithmetic, truncate to the start of the month first.

​
Troubleshooting

ProblemSolution
STR_TO_DATE returns NULLFormat string doesn’t match the input — verify each format specifier matches its corresponding input character
DATEDIFF returns 0 for timestamps on the same dateDATEDIFF ignores the time component — two rows on the same calendar date return 0
Date comparison misses rows at end of dayWHERE created_at <= '2024-06-15' stops at midnight — use < '2024-06-16' to include the full day
TIMESTAMPDIFF returns unexpected negative valueArguments are in wrong order — TIMESTAMPDIFF(unit, from, to), not (unit, to, from)
WEEK() returns different numbers than expectedWeek numbering mode matters (Sunday vs Monday start) — use YEARWEEK(date, 3) for ISO 8601 week numbers