MySQL Date and Time Functions

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:

Specifier	Meaning	Example
`%Y`	4-digit year	`2024`
`%y`	2-digit year	`24`
`%m`	Month (01–12)	`06`
`%M`	Month name	`June`
`%d`	Day (01–31)	`15`
`%H`	Hour (00–23)	`14`
`%i`	Minute (00–59)	`30`
`%s`	Second (00–59)	`00`
`%W`	Weekday name	`Saturday`
`%w`	Weekday (0=Sunday)	`6`
`%j`	Day of year (001–366)	`167`
`%U`	Week 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

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

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

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

Getting the Current Date and Time

Formatting Dates

Parsing Strings to Dates

Date Arithmetic

Extracting Date Parts

Truncating to a Period

Unix Timestamps

Common Patterns

Frequently Asked Questions

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

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

Troubleshooting

See also

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

​Getting the Current Date and Time

​Formatting Dates

​Parsing Strings to Dates

​Date Arithmetic

​Extracting Date Parts

​Truncating to a Period

​Unix Timestamps

​Common Patterns

​Frequently Asked Questions

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

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

​Troubleshooting

​See also

Getting the Current Date and Time

Formatting Dates

Parsing Strings to Dates

Date Arithmetic

Extracting Date Parts

Truncating to a Period

Unix Timestamps

Common Patterns

Frequently Asked Questions

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

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

Troubleshooting

See also