Search This Blog

Wednesday, November 14, 2018

MySQL date time example

mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2005-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2004-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'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

The following table shows the expected form of the expr argument for each unit value.
unit ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
SECOND_MICROSECOND'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND'MINUTES:SECONDS'
HOUR_MICROSECOND'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND'HOURS:MINUTES:SECONDS'
HOUR_MINUTE'HOURS:MINUTES'
DAY_MICROSECOND'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE'DAYS HOURS:MINUTES'
DAY_HOUR'DAYS HOURS'
YEAR_MONTH'YEARS-MONTHS'
The values QUARTER and WEEK are available beginning with MySQL 5.0.0.