Handling Current Date and Time

Retrieving the current system time is a common requirement in database operations. MySQL offers several functions for this, each behaving slightly differently regarding when the time is captured.

CURDATE() returns the current date in 'YYYY-MM-DD' format. If used in a numeric context, it returns the date as an integer.

mysql> SELECT CURDATE();
-> '2024-03-10'
mysql> SELECT CURDATE() + 0;
-> 20240310

NOW() provides the current date and time as 'YYYY-MM-DD hh:mm:ss'. Similar to CURDATE(), it can return a numeric value if added to zero.

mysql> SELECT NOW();
-> '2024-03-10 14:30:00'
mysql> SELECT NOW() + 0;
-> 20240310143000.000000

It is important to distinguish NOW() from SYSDATE(). NOW() returns the time at the start of the statement execution, whereas SYSDATE() returns the time at the exact moment the function is executed. This difference is visible when using sleep intervals.

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2024-03-10 14:30:00 |        0 | 2024-03-10 14:30:00 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2024-03-10 14:30:05 |        0 | 2024-03-10 14:30:07 |
+---------------------+----------+---------------------+

Date Arithmetic and Intervals

MySQL allows for adding or subtracting specific time intervals from date values using DATE_ADD, DATE_SUB, ADDDATE, and ADDTIME.

DATE_ADD and DATE_SUB accept an interval expression. The unit of the interval determines how the calculation is performed.

mysql> SELECT DATE_ADD('2023-06-15', INTERVAL 45 DAY);
-> '2023-07-30'
mysql> SELECT DATE_SUB('2023-06-15', INTERVAL 1 YEAR);
-> '2022-06-15'
mysql> SELECT DATE_ADD('2023-12-31 23:59:59', INTERVAL 1 SECOND);
-> '2024-01-01 00:00:00'

Complex intervals can also be specified, such as combining days and hours.

mysql> SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
-> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'

ADDDATE functions similarly to DATE_ADD. If no unit is specified, it defaults to days.

mysql> SELECT ADDDATE('2023-06-15', INTERVAL 45 DAY);
-> '2023-07-30'
mysql> SELECT ADDDATE('2023-06-15', 45);
-> '2023-07-30'

For time-specific addition, ADDTIME adds a time expression to a date or time expression.

mysql> SELECT ADDTIME('2023-12-31 23:59:59.999999', '1 1:1:1.000002');
-> '2024-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
-> '03:00:01.999997'

Extracting Date Components

Specific parts of a date can be extracted using dedicated functions. DATE() isolates the date part from a datetime expression.

mysql> SELECT DATE('2023-12-25 01:02:03');
-> '2023-12-25'

To determine the day within a month, year, or week, use the following:

• DAYOFMONTH(date): Returns the day of the month (1-31).
• DAYOFYEAR(date): Returns the day of the year (1-366).
• DAYOFWEEK(date): Returns the weekday index (1=Sunday, 7=Saturday).
• DAYNAME(date): Returns the name of the weekday.

mysql> SELECT DAYOFMONTH('2023-06-15');
-> 15
mysql> SELECT DAYOFYEAR('2023-06-15');
-> 166
mysql> SELECT DAYOFWEEK('2023-06-15');
-> 5
mysql> SELECT DAYNAME('2023-06-15');
-> 'Thursday'

LAST_DAY(date) is useful for finding the final date of the given month, accounting for leap years.

mysql> SELECT LAST_DAY('2023-02-05');
-> '2023-02-28'
mysql> SELECT LAST_DAY('2024-02-05');
-> '2024-02-29'

Formatting and Timestamp Conversion

DATE_FORMAT converts a date into a string based on a specified format mask. This allows for custom output styles.

mysql> SELECT DATE_FORMAT('2023-12-25 14:30:00', '%W %M %Y');
-> 'Monday December 2023'
mysql> SELECT DATE_FORMAT('2023-12-25 14:30:00', '%H:%i:%s');
-> '14:30:00'
mysql> SELECT DATE_FORMAT('2023-12-25 14:30:00', '%Y%m%d%H%i%s');
-> '20231225143000'

Conversion between Unix timestamps and MySQL datetime formats is handled by UNIX_TIMESTAMP and FROM_UNIXTIME.

UNIX_TIMESTAMP() returns the seconds elapsed since '1970-01-01 00:00:00' UTC. If a date is provided, it converts that date to a timestamp.

mysql> SELECT UNIX_TIMESTAMP();
-> 1710000000
mysql> SELECT UNIX_TIMESTAMP('2023-06-01 12:00:00');
-> 1685620800
mysql> SELECT UNIX_TIMESTAMP('2023-06-01 12:00:00.012');
-> 1685620800.012

FROM_UNIXTIME performs the reverse operation, optionally formatting the result.

mysql> SELECT FROM_UNIXTIME(1685620800);
-> '2023-06-01 12:00:00'
mysql> SELECT FROM_UNIXTIME(1685620800,'%Y %D %M %h:%i:%s %x');
-> '2023 1st June 12:00:00 2023'

For converting days counted from year 0 to a date, FROM_DAYS is available.

mysql> SELECT FROM_DAYS(730669);
-> '2000-07-03'

Time Differences and Timezones

To calculate the difference in days between two dates, use DATEDIFF. It computes expr1 - expr2.

mysql> SELECT DATEDIFF('2023-12-31 23:59:59','2023-12-30');
-> 1
mysql> SELECT DATEDIFF('2023-11-30 23:59:59','2023-12-31');
-> -31

Timezone conversion is supported via CONVERT_TZ, which shifts a datetime from one timezone to another.

mysql> SELECT CONVERT_TZ('2024-01-01 12:00:00','GMT','MET');
-> '2024-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2024-01-01 12:00:00','+00:00','+10:00');
-> '2024-01-01 22:00:00'

Period addition alows adding months to a value in 'YYMM' or 'YYYYMM' format using PERIOD_ADD.

mysql> SELECT PERIOD_ADD(202401,2);
-> 202403

To retrieve the start of the current day (00:00:00) or the end (23:59:59), combinations of these functions are often used. For example, to get the end of the current day:

mysql> SELECT DATE_SUB(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 SECOND);