Date and time functions |V2.2.77|OceanBase Database| docs|Distributed Database

Date and time functions

Last Updated：2023-08-18 09:26:34 Updated

Date and time functions are used to display the date and time.

CURDATE

Declaration

CURDATE()

Description

This function returns the current date without the time part.

Example

obclient> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2018-05-05 |
+------------+
1 row in set (0.00 sec)

CURRENT_DATE

Declaration

CURRENT_DATE()
CURRENT_DATE

Description

It is same as the CURDATE() function.

CURRENT_TIME

Declaration

CURRENT_TIME([scale])

Description

This function returns the current time without the date part.

The value of scale ranges from 0 to 6. It specifies the microsecond precision. Default value: 0.

Example

obclient> SELECT CURRENT_TIME(6);
+-----------------+
| CURRENT_TIME(6) |
+-----------------+
| 11:11:45.215311 |
+-----------------+
1 row in set (0.01 sec)

CURRENT_TIMESTAMP

Declaration

CURRENT_TIMESTAMP([scale])

Description

This function returns the current date and time based on the specified time zone.

The value of scale ranges from 0 to 6. It specifies the microsecond precision. Default value: 0.

Example

obclient> SELECT CURRENT_TIMESTAMP(6);
+----------------------------+
| CURRENT_TIMESTAMP(6) |
+----------------------------+
| 2018-05-05 11:35:39.177764 |
+----------------------------+
1 row in set (0.01 sec)

CURTIME

Declaration

CURTIME()

Description

The same as the CURRENT_TIME() function.

DATE_ADD

Declaration

DATE_ADD(date, INTERVAL expr unit)

Description

You can use this statement for the arithmetic calculation of the date and time.

date specifies the basic date and time. The date part is required and the time part is optional.

expr specifies the time interval, which can be a negative value.

unit specifies the unit of the time interval.

The following table lists all time interval units.

Unit	Type	Description	Format
`MICROSECOND`	Independent	Microseconds	`MICROSECONDS`
`SECOND`	Independent	Seconds	`SECONDS`
`MINUTE`	Independent	Minutes	`MINUTES`
`HOUR`	Independent	Hours	`HOURS`
`DAY`	Independent	Days	`DAYS`
`WEEK`	Independent	Weeks	`WEEKS`
`MONTH`	Independent	Months	`MONTHS`
`QUARTER`	Independent	Quarters	`QUARTERS`
`YEAR`	Independent	Years	`YEARS`
`SECOND_MICROSECOND`	Combination	Seconds and microseconds	`'SECONDS.MICROSECONDS'`
`MINUTE_MICROSECOND`	Combination	Minutes, seconds, and microseconds	`'MINUTES:SECONDS.MICROSECONDS'`
`MINUTE_SECOND`	Combination	Minutes and seconds	`'MINUTES:SECONDS'`
`HOUR_MICROSECOND`	Combination	Hours, minutes, seconds, and microseconds	`'HOURS:MINUTES:SECONDS.MICROSECONDS'`
`HOUR_SECOND`	Combination	Hours, minutes, and seconds	`'HOURS:MINUTES:SECONDS'`
`HOUR_MINUTE`	Combination	Hours and minutes	`'HOURS:MINUTES'`
`DAY_SECOND`	Combination	Days, hours, minutes, and seconds	`'DAYS HOURS:MINUTES:SECONDS'`
`DAY_MINUTE`	Combination	Days, hours, and minutes	`'DDAYSD HOURS:MINUTES'`
`DAY_HOUR`	Combination	Days and hours	`'DAYS HOURS'`
`YEAR_MONTH`	Combination	Years and months	`'YEARS-MONTHS'`

Example

obclient> SELECT
     DATE_ADD(NOW(), INTERVAL 5 DAY),
     DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND),
     DATE_ADD('2014-01-10', INTERVAL 5 SECOND),
     DATE_ADD('2014-01-10', INTERVAL 5 MINUTE),
     DATE_ADD('2014-01-10', INTERVAL 5 HOUR),
     DATE_ADD('2014-01-10', INTERVAL 5 DAY),
     DATE_ADD('2014-01-10', INTERVAL 5 WEEK),
     DATE_ADD('2014-01-10', INTERVAL 5 MONTH),
     DATE_ADD('2014-01-10', INTERVAL 5 QUARTER),
     DATE_ADD('2014-01-10', INTERVAL 5 YEAR),
     DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
     DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
     DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND),
     DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
     DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND),
     DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE),
     DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
     DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
     DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE),
     DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR),
     DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH)
     \G
*************************** 1. row ***************************
                                      DATE_ADD(NOW(), INTERVAL 5 DAY): 2021-04-21 16:04:53
                       DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND): 2014-01-10 00:00:00.000005
                            DATE_ADD('2014-01-10', INTERVAL 5 SECOND): 2014-01-10 00:00:05
                            DATE_ADD('2014-01-10', INTERVAL 5 MINUTE): 2014-01-10 00:05:00
                              DATE_ADD('2014-01-10', INTERVAL 5 HOUR): 2014-01-10 05:00:00
                               DATE_ADD('2014-01-10', INTERVAL 5 DAY): 2014-01-15
                              DATE_ADD('2014-01-10', INTERVAL 5 WEEK): 2014-02-14
                             DATE_ADD('2014-01-10', INTERVAL 5 MONTH): 2014-06-10
                           DATE_ADD('2014-01-10', INTERVAL 5 QUARTER): 2015-04-10
                              DATE_ADD('2014-01-10', INTERVAL 5 YEAR): 2019-01-10
       DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2014-01-10 00:00:05.000005
   DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2014-01-10 00:05:05.000005
               DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND): 2014-01-10 00:05:05
  DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2014-01-10 05:05:05.000005
              DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2014-01-10 05:05:05
                 DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE): 2014-01-10 05:05:00
DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2014-01-11 05:05:05.000005
            DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2014-01-11 05:05:05
               DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2014-01-11 05:05:00
                    DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR): 2014-01-11 05:00:00
                   DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH): 2015-02-10
1 row in set (0.01 sec)

DATE_FORMAT

Declaration

DATE_FORMAT(date, format)

Description

Export the date and time in the specified format.

date specifies the date and time.

format specifies the output format.

The following table lists all output formats.

Specifier	Description	Format
`%a`	The abbreviation of the week.	`Sun..Sat`
`%b`	The abbreviation of the month.	`Jan..Dec`
`%c`	The numeric format of the month.	`1..12`
`%D`	The abbreviation of the day.	`1st..31st`
`%d`	The numeric format of the day.	`01..31`
`%e`	The numeric format of the day.	`1.. 31`
`%f`	The value for microsecond.	`000000..999999`
`%H`	The value for hour.	`00 .. 23`
`%h`	The value for hour.	`01..12`
`%I`	The value for hour.	`01..12`
`%i`	The value for minute.	`00..59`
`%j`	The day of the year.	`001..366`
`%k`	The value for hour.	`0..23`
`%l`	The value for hour.	`0..12`
`%M`	The English name of the month.	`January..December`
`%m`	The numeric format of the month.	`01..12`
`%p`	The morning or afternoon.	`AM/PM`
`%r`	The time in 12-hour format.	`hh:mm:ss AM/PM`
`%S`	The value for second.	`00..59`
`%s`	The value for second.	`00..59`
`%T`	The time in 24-hour format.	`hh:mm:ss`
`%U`	The week of the year, with Sunday taken as the first day of a week.	`00..53`
`%u`	The week of the year, with Monday taken as the first day of a week.	`00..53`
`%V`	The week of the year, with Sunday taken as the first day of a week (used together with `%X`).	`01..53`
`%v`	The week of the year, with Monday taken as the first day of a week (used together with `%X`).	`01..53`
`%W`	The English name of the week.	`Sunday..Saturday`
`%w`	The day of the week.	`0=Sunday..6=Saturday`
`%X`	The year of the week, with Sunday taken as the first day of a week (used together with `%V`).
`%x`	The year of the week, with Monday taken as the first day of a week (used together with `%v`).
`%Y`	The year in four-digit format.
`%y`	The year in two-digit format.
`%%`	The literal `%` character.

Example

obclient> SELECT
          DATE_FORMAT('2014-01-01', '%Y-%M-%d'),
          DATE_FORMAT('2014-01-01', '%X-%V'),
          DATE_FORMAT('2014-01-01', '%U')
          \G
*************************** 1. row ***************************
DATE_FORMAT('2014-01-01', '%Y-%M-%d'): 2014-January-01
   DATE_FORMAT('2014-01-01', '%X-%V'): 2013-52
      DATE_FORMAT('2014-01-01 ', '%U'): 00
1 row in set (0.01 sec)

DATE_SUB

Declaration

DATE_SUB(date, INTERVAL expr unit)

Description

You can use this statement for the arithmetic calculation of the date and time.

For more information, see DATE_ADD().

DATEDIFF

Declaration

DATEDIFF(date1, date2)

Description

This function returns the number of months between date1 and date2.

Only the date part of the parameter is used in calculation. The time part is ignored.

Example

obclient> SELECT DATEDIFF('2015-06-19','1994-12-17');
+-------------------------------------+
| DATEDIFF('2015-06-19','1994-12-17') |
+-------------------------------------+
|                                7489 |
+-------------------------------------+
1 row in set (0.01 sec)

EXTRACT

Declaration

EXTRACT(unit FROM date)

Description

This function returns an integer value of the specified part of date. If multiple parts are specified, all values are spliced in order.

For more information about unit, see DATE_ADD(). When unit is set to WEEK, see the description of %U in DATE_FORMAT().

Example

obclient> SELECT EXTRACT(WEEK FROM '2013-01-01'),
     EXTRACT(WEEK FROM '2013-01-06')
     EXTRACT(YEAR_MONTH FROM '2012-03-09'),
     EXTRACT(DAY FROM NOW())\G;
*************************** 1. row ***************************
      EXTRACT(WEEK FROM '2013-01-01'): 0
      EXTRACT(WEEK FROM '2013-01-06'): 1
EXTRACT(YEAR_MONTH FROM '2012-03-09'): 201203
              EXTRACT(DAY FROM NOW()): 16
1 row in set (0.00 sec)

FROM_DAYS

Declaration

FROM_DAYS(N)

Description

This function returns the DATE value corresponding to the number of days specified for N. The number of days refers to the number of days from 0000-01-01.

Example

obclient> SELECT FROM_DAYS(736271), FROM_DAYS(700000);
+-------------------+-------------------+
| FROM_DAYS(736271) | FROM_DAYS(700000) |
+-------------------+-------------------+
| 2015-11-04        | 1916-07-15        |
+-------------------+-------------------+
1 row in set (0.00 sec)

FROM_UNIXTIME

Declaration

FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp, format)

Description

If format is not specified, a value of the DATETIME type is returned, regardless of the time zone.

If format is specified, a date and time string in the specified format is returned.

unix_timestamp refers to the UNIX timestamp, that is, the number of microseconds from 1970-01-01 00:00:00.000000.

The formats supported by format are listed in the description of DATE_FORMAT().

Example

obclient> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
+---------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
+---------------------------------------------------------+
| 2018 5th May 08:41:26 2018                              |
+---------------------------------------------------------+
1 row in set (0.01 sec)

MONTH

Declaration

MONTH(date)

Description

This function returns the month of date.

Example

obclient> SELECT MONTH('2008-02-03');
+---------------------+
| MONTH('2008-02-03') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.01 sec)

NOW

Declaration

NOW([scale])

Description

The same as the CURRENT_TIMESTAMP() function.

PERIOD_DIFF

Declaration

PERIOD_DIFF(p1, p2)

Description

This function returns the interval between two dates, in months. The date can contain only the year and month in the format of YYYYMM or YYMM.

Example

obclient> SELECT PERIOD_DIFF(200802, 200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
|                         11 |
+----------------------------+
1 row in set (0.01 sec)

STR_TO_DATE

Declaration

STR_TO_DATE(str, format)

Description

Use format to convert str into a DATETIME, DATE, or TIME value. The return value type depends on which parts of the date and time are included in format.

The formats supported by format are listed in the description of DATE_FORMAT().

Example

obclient> SELECT STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r');
+-----------------------------------------------------+
| STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r') |
+-----------------------------------------------------+
| 2014-01-01 05:05:05                                 |
+-----------------------------------------------------+
1 row in set (0.01 sec)

TIME

Declaration

TIME(datetime)

Description

This function returns the datetime value of the TIME data type.

Example

obclient> SELECT TIME('2003-12-31 01:02:03');
+-----------------------------+
| TIME('2003-12-31 01:02:03') |
+-----------------------------+
| 01:02:03.000000             |
+-----------------------------+
1 row in set (0.01 sec)

TIME_TO_USEC

Declaration

TIME_TO_USEC(date)

Description

This function converts the value of date into microseconds from 1970-01-01 00:00:00.000000, taking into account the time zone.

date can represent the date or the date and time.

Example

obclient> SELECT TIME_TO_USEC('2014-03-25'), TIME_TO_USEC(NOW());
+----------------------------+---------------------+
| TIME_TO_USEC('2014-03-25') | TIME_TO_USEC(NOW()) |
+----------------------------+---------------------+
|           1395676800000000 |    1525528100000000 |
+----------------------------+---------------------+
1 row in set (0.01 sec)

TIMEDIFF

Declaration

TIMEDIFF(date1, date2)

Description

This function returns the interval between two date and time values of TIME data type.

Example

obclient> SELECT
     TIMEDIFF('2015-06-06 12:12:12', '2014-06-05 11:11:11'),
     TIMEDIFF('2015-06-06 12:12:12', '2015-06-05 11:11:11')
     \G
*************************** 1. row ***************************
TIMEDIFF('2015-06-06 12:12:12', '2014-06-05 11:11:11'): 838:59:59
TIMEDIFF('2015-06-06 12:12:12', '2015-06-05 11:11:11'): 25:01:01
1 row in set (0.00 sec)

TIMESTAMPDIFF

Declaration

TIMESTAMPDIFF(unit, date1, date2)

Description

This function returns the interval between two date and time values in the unit specified for unit. unit can only be an independent unit in DATE_ADD().

Example

obclient> SELECT
     TIMESTAMPDIFF(SECOND, NOW(), '2011-01-01 11:11:11'),
     TIMESTAMPDIFF(DAY, '2011-01-01 11:11:11', NOW())
     \G
*************************** 1. row ***************************
TIMESTAMPDIFF(SECOND, NOW(), '2011-01-01 11:11:11'): -231677498
   TIMESTAMPDIFF(DAY, '2011-01-01 11:11:11', NOW()): 2681
1 row in set (0.00 sec)

TIMESTAMPADD

Declaration

TIMESTAMPADD(unit, interval_expr, date)

Description

You can use this statement for the arithmetic calculation of the date and time.

The functionality of this function is the same as that of DATE_ADD(), but unit must be an independent unit.

Example

obclient> SELECT
     TIMESTAMPADD(DAY, -5, '2010-01-01 00:00:00'),
     DATE_ADD('2010-01-01 00:00:00', INTERVAL -5 DAY)
     \G
*************************** 1. row ***************************
    TIMESTAMPADD(DAY, -5, '2010-01-01 00:00:00'): 2009-12-27 00:00:00
DATE_ADD('2010-01-01 00:00:00', INTERVAL -5 DAY): 2009-12-27 00:00:00
1 row in set (0.01 sec)

TO_DAYS

Declaration

TO_DAYS(date)

Description

This function returns the number of days corresponding to the value of date. The number of days refers to the number of days from 0000-01-01.

Example

obclient> SELECT TO_DAYS('2015-11-04'), TO_DAYS('20151104');
+-----------------------+---------------------+
| TO_DAYS('2015-11-04') | TO_DAYS('20151104') |
+-----------------------+---------------------+
|                736271 |              736271 |
+-----------------------+---------------------+
1 row in set (0.01 sec)

USEC_TO_TIME

Declaration

USEC_TO_TIME(usec)

Description

This function converts the value of usec into the TIMESTAMP type.

usec specifies the number of microseconds from 1970-01-01 00:00:00.000000, taking into account the time zone.

Example

obclient> SELECT USEC_TO_TIME(1);
+----------------------------+
| USEC_TO_TIME(1)            |
+----------------------------+
| 1970-01-01 08:00:00.000001 |
+----------------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP

Declaration

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

Description

If date is not specified, the number of seconds from '1970-01-01 00:00:00' to the current time is returned, taking into account the time zone.

If date is specified, the number of seconds from '1970-01-01 00:00:00' to the specified time is returned, taking into account the time zone.

Example

obclient> SELECT UNIX_TIMESTAMP(), TIME_TO_USEC(NOW());
+------------------+---------------------+
| UNIX_TIMESTAMP() | TIME_TO_USEC(NOW()) |
+------------------+---------------------+
|       1525570561 |    1525570561000000 |
+------------------+---------------------+
1 row in set (0.01 sec)

obclient> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00') |
+---------------------------------------+
|                             875974980 |
+---------------------------------------+
1 row in set (0.01 sec)

UTC_TIMESTAMP

Declaration

UTC_TIMESTAMP()

Description

This function returns the current UTC time.

Example

obclient> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2018-05-06 01:38:32 |
+---------------------+
1 row in set (0.01 sec)

YEAR

Declaration

YEAR(date)

Description

This function returns the year of date.

Example

obclient> SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
|               1987 |
+--------------------+
1 row in set (0.00 sec)