This topic describes the date and time functions supported by PolarDB-X.

Supported functions

PolarDB-X supports the following date and time functions.

FunctionDescriptionExample
Function Description Example
ADDDATE(d,n) Returns the date value of the day that is n days after the date d. SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);

The following result is returned:

->2017-06-25
ADDTIME(t,n) Adds the time expression n to the t argument.
  • Execute the following statement to add five seconds to the specified time.

    SELECT ADDTIME('2011-11-11 11:11:11', 5);

    The following result is returned:

    ->2011-11-11 11:11:16

  • Execute the following statement to add 2 hours, 10 minutes, and 5 seconds to the specified time.

    SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5");

    The following result is returned:

    -> 2020-06-15 11:44:26
CURDATE() Returns the current date. SELECT CURDATE();

The following result is returned:

-> 2018-09-19
CURRENT_DATE() Returns the current date.

SELECT CURRENT_DATE();

The following result is returned:

-> 2018-09-19
CURRENT_TIME() Returns the current time.

SELECT CURRENT_TIME();

The following result is returned:

-> 19:59:02
CURRENT_TIMESTAMP() Returns the current date and time.

SELECT CURRENT_TIMESTAMP();

The following result is returned:

-> 2018-09-19 20:57:43
CURTIME() Returns the current time.

SELECT CURTIME();

The following result is returned:

-> 19:59:02
DATE() Extracts a date value from a date or a datetime expression.

SELECT DATE("2017-06-15");

The following result is returned:

-> 2017-06-15
DATEDIFF(d1,d2) Calculates the number of days between the date d1 and the date d2.

SELECT DATEDIFF('2001-01-01','2001-02-02');

The following result is returned:

-> -32
DATE_ADD(d,INTERVAL expr type) Adds an interval to the date d. Valid values of the type argument are:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

  • SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);

    The following result is returned:

    -> 2017-06-25

  • SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);

    -> 2017-06-15 09:49:21

  • SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);

    The following result is returned:

    ->2017-06-15 06:34:21
DATE_FORMAT(d,f) Returns the date d in the format that is specified by the f argument.

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');

The following result is returned:

-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type) Subtracts an interval from the specified date. Execute the following statement to subtract two days from each value in the OrderDate field of the Orders table.

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d) Returns the day component of the date d. SELECT DAY("2017-06-15");

The following result is returned:

-> 15
DAYNAME(d) Returns what day the date d is in a week, such as Monday and Tuesday.

SELECT DAYNAME('2011-11-11 11:11:11');

The following result is returned:

->Friday
DAYOFMONTH(d)) Returns what day the date d is in a month.

SELECT DAYOFMONTH('2011-11-11 11:11:11');

The following result is returned:

->11
DAYOFWEEK(d) Returns the weekday index for the date d. For example, the value 1 is returned for Sunday, and the value 2 is returned for Monday.

SELECT DAYOFWEEK('2011-11-11 11:11:11');

The following result is returned:

->6
DAYOFYEAR(d) Returns what day the date d is in a year.

SELECT DAYOFYEAR('2011-11-11 11:11:11');

The following result is returned:

->315
EXTRACT(type FROM d) Extracts the component specified by the type argument from the date d.

Valid values of the type argument are:

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11')

The following result is returned:

->11
FROM_DAYS(n) Returns the date value of the day that is n days after 0000-01-01.

SELECT FROM_DAYS(1111)

The following result is returned:

-> 0003-01-16
HOUR(t) Returns the hour component of the t argument.

SELECT HOUR('1:2:3')

The following result is returned:

-> 1
LAST_DAY(d) Returns the date value of the last day in the month of the specified date.

SELECT LAST_DAY("2017-06-20");

The following result is returned:

-> 2017-06-30
LOCALTIME() Returns the current date and time.

SELECT LOCALTIME()

The following result is returned:

-> 2018-09-19 20:57:43
LOCALTIMESTAMP() Returns the current date and time.

SELECT LOCALTIMESTAMP()

The following result is returned:

-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year) Returns a date based on the year argument and the day-of-year argument. The day-of-year argument specifies the number of days that have elapsed from the start of the year that is specified by the year argument.

SELECT MAKEDATE(2017, 3);

The following result is returned:

-> 2017-01-03
MAKETIME(hour, minute, second) Returns a time value based on the hour, minute, and second arguments.

SELECT MAKETIME(11, 35, 4);

The following result is returned:

-> 11:35:04
MICROSECOND(date) Returns the microsecond component of the date argument.

SELECT MICROSECOND("2017-06-20 09:34:00.000023");

The following result is returned:

-> 23
MINUTE(t) Returns the minute component of the t argument.

SELECT MINUTE('1:2:3');

The following result is returned:

-> 2
MONTHNAME(d) Returns the name of the month of the specified date, such as November.

SELECT MONTHNAME('2011-11-11 11:11:11');

The following result is returned:

-> November
MONTH(d) Returns the number for the month in the specified date. The value ranges from 1 to 12.

SELECT MONTH('2011-11-11 11:11:11');

The following result is returned:

->11
NOW() Returns the current date and time.

SELECT NOW()

The following result is returned:

-> 2018-09-19 20:57:43
PERIOD_ADD(period, number) Adds the number argument to the period argument. The number argument specifies the number of months you want to add. The period argument consists of a year component and a month component.

SELECT PERIOD_ADD(201703, 5);

The following result is returned:

-> 201708
PERIOD_DIFF(period1, period2) Returns the number of months between the period1 argument and the period2 argument.

SELECT PERIOD_DIFF(201710, 201703);

The following result is returned:

-> 7
QUARTER(d) Returns the quarter of the date specified by the d argument. Possible return values: 1,2,3, and 4.

SELECT QUARTER('2011-11-11 11:11:11');

The following result is returned:

-> 4
SECOND(t) Returns the second component of the t argument.

SELECT SECOND('1:2:3')

The following result is returned:

-> 3
SEC_TO_TIME(s) Converts the s argument to a TIME value in the hh:mm:ss format. The s argument indicates the number of seconds that have elapsed since 00:00:00.

SELECT SEC_TO_TIME(4320);

The following result is returned:

-> 01:12:00
STR_TO_DATE(string, format_mask) Converts a string to a date.

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");

The following result is returned:

-> 2017-08-10
SUBDATE(d,n) Subtracts n days from the date d.

SELECT SUBDATE('2011-11-11 11:11:11', 1);

The following result is returned:

->2011-11-10 11:11:11
SUBTIME(t,n) Subtracts n seconds from the t argument.

SELECT SUBTIME('2011-11-11 11:11:11', 5);

The following result is returned:

->2011-11-11 11:11:06
SYSDATE() Returns the current date and time.

SELECT SYSDATE();

The following result is returned:

-> 2018-09-19 20:57:43
TIME(expression) Extracts the time from the expression argument.

SELECT TIME("19:30:10");

The following result is returned:

-> 19:30:10
TIME_FORMAT(t,f) Returns the time specified by the t argument in the format specified by the f argument.

SELECT TIME_FORMAT('11:11:11','%r');

The following result is returned:

-> 11:11:11 AM
TIME_TO_SEC(t) Converts the t argument to the number of seconds that have elapsed since 00:00:00 until the time t.

SELECT TIME_TO_SEC('1:12:00');

The following result is returned:

-> 4320
TIMEDIFF(time1, time2) Returns the difference between the time1 argument and the time2 argument.

SELECT TIMEDIFF("13:10:11", "13:10:10");

The following result is returned:

-> 00:00:01
TIMESTAMP(expression, interval) Returns a datetime value. If you specify one argument, the function returns the datetime value of the specified argument. If you specify two arguments, the function adds the second argument to the first argument and returns the result as a datetime value.

SELECT TIMESTAMP("2017-07-23", "13:10:11");

The following result is returned:

-> 2017-07-23 13:10:11
TO_DAYS(d) Returns the number of days that have elapsed since January 1, 0000 until the date d.

SELECT TO_DAYS('0001-01-01 01:01:01');

The following result is returned:

-> 366
WEEK(d) Returns the calendar week of the date d. Return values are integers from 0 to 53.

SELECT WEEK('2011-11-11 11:11:11');

The following result is returned:

-> 45
WEEKDAY(d) Returns the weekday index for the date d. For example, the value 0 is returned for Monday, and the value 1 is returned for Tuesday.

SELECT WEEKDAY("2017-06-15");

The following result is returned:

-> 3
WEEKOFYEAR(d) Returns the calendar week of the date d. Return values are integers from 0 to 53.

SELECT WEEKOFYEAR('2011-11-11 11:11:11');

The following result is returned:

-> 45
YEAR(d) Returns the year component of the date d.

SELECT YEAR("2017-06-15");

The following result is returned:

-> 2017
YEARWEEK(date, mode) Returns the year component of the specified date and the calendar week of the specified date. The calendar week is an integer from 0 to 53. For the mode argument, the value specifies the day of the week. For example, the value 0 represents Sunday and the value 1 represents Monday, and so on.

SELECT YEARWEEK("2017-06-15");

The following result is returned:

-> 201724