This topic describes the date and time functions in AnalyticDB for MySQL.

  • ADDDATE: adds a time interval to the specified date.
  • ADDTIME: adds a time interval to the specified time.
  • CONVERT_TZ: converts from the time zone specified by from_tz to the time zone specified by to_tz, and returns the resulting value.
  • CURDATE: returns the current date.
  • CURTIME: returns the current time.
  • DATE: returns the date part of a date or datetime expression.
  • DATE_FORMAT: returns a date as a string in the specified format.
  • SUBDATE/DATE_SUB: subtracts the specified interval from a date.
  • DATEDIFF: subtracts a date from another.
  • DAY/DAYOFMONTH: returns the day of the month for a date. Valid values: 1 to 31.
  • DAYNAME: returns the day of the week for a date, such as Monday.
  • DAYOFWEEK: returns the day of the week as a numeric value for a date.
  • DAYOFYEAR: returns the day of the year for a date.
  • EXTRACT: returns one or more separate parts of a date or time. For example, this function can return the year, month, day, hour, or minute of a date or time.
  • FROM_DAYS: returns a DATE value based on the parameter N indicating the number of days.
  • FROM_UNIXTIME: returns a UNIX timestamp in the specified format.
  • HOUR: returns the hour part of a time.
  • LAST_DAY: returns the last day of the month for a date or datetime.
  • LOCALTIME/LOCALTIMESTAMP/NOW: returns the current timestamp.
  • MAKEDATE: returns a date based on the year and dayofyear parameters.
  • MAKETIME: returns a time based on the hour, minute, and second parameters.
  • MINUTE: returns the minute part of a time.
  • MONTH: returns the month of a date.
  • MONTHNAME: returns the full name of the month for a date.
  • PERIOD_ADD: adds a number of months specified by N to the period specified by P.
  • PERIOD_DIFF: returns the number of months between two periods.
  • QUARTER: returns the quarter of the year for a date.
  • SEC_TO_TIME: converts a quantity of seconds to a time.
  • SECOND: returns the second part of the specified time.
  • STR_TO_DATE: converts a string to a date or datetime in the specified format.
  • SUBTIME: subtracts a time interval from a date.
  • SYSDATE: returns the system time.
  • TIME: returns the time part of the date or datetime expression specified by expr as a string.
  • TIME_FORMAT: returns a time as a string in the specified format.
  • TIME_TO_SEC: converts a time to a quantity of seconds.
  • TIMEDIFF: subtracts a time from another.
  • TIMESTAMP: returns the date or datetime expression specified by expr as a date or datetime value.
  • TIMESTAMPADD: adds an interval to a date or datetime expression.
  • TIMESTAMPDIFF: subtracts an interval from a date or datetime expression.
  • TO_DAYS: returns the number of days since year 0 based on the specified date.
  • TO_SECONDS: returns the number of seconds that have elapsed since year 0 based on the specified time.
  • UNIX_TIMESTAMP: returns the timestamp for the current time. The timestamp follows the UNIX time format. It is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970.
  • UTC_DATE: returns the UTC date.
  • UTC_TIME: returns the UTC time.
  • UTC_TIMESTAMP: returns the UTC timestamp.
  • WEEK: returns the week number for a date.
  • WEEKDAY: returns the weekday for a date.
  • WEEKOFYEAR: returns the calendar week for a date.
  • YEAR: returns the year part of a date.
  • YEARWEEK: returns the year and week of a date.

ADDDATE

ADDDATE(date,INTERVAL expr unit)
ADDDATE(expr,days      
  • Parameter types:
    adddate(date, INTERVAL expr unit)
    adddate(timestamp, INTERVAL expr unit)
    adddate(datetime, INTERVAL expr unit)
    adddate(varchar, INTERVAL expr unit)
    adddate(date, varchar)
    adddate(date, bigint)
    adddate(datetime, bigint)
    adddate(datetime, varchar)
    adddate(timestamp, varchar)
    adddate(timestamp, bigint)
    adddate(varchar, bigint)
    adddate(varchar, varchar)               
  • Return value type: DATE.
  • Description: This function adds a time interval to the specified date.
    • Valid values of unit: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, and year_month. Default value of unit: day.
    • days and expr: This function returns the value of expr plus days.
  • Example:
    select adddate(date '2001-1-22',interval '3' day);
    +----------------------------------------------+
    | adddate(DATE '2001-1-22', INTERVAL  '3' DAY) |
    +----------------------------------------------+
    | 2001-01-25                                   |              
    select adddate(timestamp '2001-1-22',interval '3' day);
    +---------------------------------------------------+
    | adddate(TIMESTAMP '2001-1-22', INTERVAL  '3' DAY) |
    +---------------------------------------------------+
    | 2001-01-25 00:00:00                               |                
    select adddate(datetime '2001-1-22',interval '3' day);
    +--------------------------------------------------+
    | adddate(DATETIME '2001-1-22', INTERVAL  '3' DAY) |
    +--------------------------------------------------+
    | 2001-01-25 00:00:00                              |               
    select adddate('2001-1-22',interval '3' day);
    +-----------------------------------------+
    | adddate('2001-1-22', INTERVAL  '3' DAY) |
    +-----------------------------------------+
    | 2001-01-25                                           
    select adddate(datetime '2001-1-22',interval '3' second);
    +-----------------------------------------------------+
    | adddate(DATETIME '2001-1-22', INTERVAL  '3' SECOND) |
    +-----------------------------------------------------+
    |                                 2001-01-22 00:00:03 |               
    
    select adddate(datetime '2001-1-22',interval '3' minute);
    +-----------------------------------------------------+
    | adddate(DATETIME '2001-1-22', INTERVAL  '3' MINUTE) |
    +-----------------------------------------------------+
    |                                 2001-01-22 00:03:00 |
    					
    select adddate(datetime '2001-1-22',interval '3' hour);
    +---------------------------------------------------+
    | adddate(DATETIME '2001-1-22', INTERVAL  '3' HOUR) |
    +---------------------------------------------------+
    |                               2001-01-22 03:00:00 |                
    select adddate(datetime '2001-1-22',interval '3' month);
    +----------------------------------------------------+
    | adddate(DATETIME '2001-1-22', INTERVAL  '3' MONTH) |
    +----------------------------------------------------+
    |                                2001-04-22 00:00:00 |              
    select adddate(datetime '2001-1-22',interval '3' year);
    +---------------------------------------------------+
    | adddate(DATETIME '2001-1-22', INTERVAL  '3' YEAR) |
    +---------------------------------------------------+
    |                               2004-01-22 00:00:00 |               
    					
    select adddate(datetime '2001-1-22',interval '3' hour_second) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-22 03:00:00 |                
    select adddate(datetime '2001-1-22',interval '3' hour_minute) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-22 03:00:00 |              
    select adddate(datetime '2001-1-22',interval '3' day_second) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-25 00:00:00 |               
    select adddate(datetime '2001-1-22',interval '3' minute_second) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-22 00:03:00 | 
    adddate(datetime '2001-1-22',interval '3' day_minute) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-25 00:00:00 |                
    select adddate(datetime '2001-1-22',interval '3' day_hour) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-25 00:00:00 |               
    select adddate(datetime '2001-1-22 12:32:1',interval '4' year_month) as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2005-01-22 12:32:01 |               
    select adddate('2001-1-22','3');
    +---------------------------+
    | adddate('2001-1-22', '3') |
    +---------------------------+
    | 2001-01-25                |               
    select adddate('2001-1-22',3);
    +-------------------------+
    | adddate('2001-1-22', 3) |
    +-------------------------+
    | 2001-01-25              |                
    select adddate(datetime '2001-1-22 12:12:32',3);
    +-------------------------------------------+
    | adddate(DATETIME '2001-1-22 12:12:32', 3) |
    +-------------------------------------------+
    |                       2001-01-25 12:12:32 |                
    select adddate(datetime '2001-1-22 12:12:32','3');
    +---------------------------------------------+
    | adddate(DATETIME '2001-1-22 12:12:32', '3') |
    +---------------------------------------------+
    |                         2001-01-25 12:12:32 |                
    select adddate(timestamp '2001-1-22 12:12:32','3');
    +----------------------------------------------+
    | adddate(TIMESTAMP '2001-1-22 12:12:32', '3') |
    +----------------------------------------------+
    |                          2001-01-25 12:12:32 |               
    select adddate(timestamp '2001-1-22 12:12:32',3);
    +--------------------------------------------+
    | adddate(TIMESTAMP '2001-1-22 12:12:32', 3) |
    +--------------------------------------------+
    |                        2001-01-25 12:12:32 |               
    select adddate('2001-1-22 12:12:32',3);
    +----------------------------------+
    | adddate('2001-1-22 12:12:32', 3) |
    +----------------------------------+
    | 2001-01-25 12:12:32              |                
    select adddate('2001-1-22 12:12:32','3');
    +------------------------------------+
    | adddate('2001-1-22 12:12:32', '3') |
    +------------------------------------+
    | 2001-01-25 12:12:32                |               

ADDTIME

ADDTIME(expr1,expr2)            
  • Description: This function adds the time specified by expr2 to the time specified by expr1 and returns the result.
  • Parameter types:
    addtime(date,varchar)
    addtime(time,varchar)
    addtime(datetime,varchar)
    addtime(timestamp,varchar)
    addtime(varchar,varchar)                    
  • Return value type: VARCHAR.
  • Example:
    select addtime(date '1998-01-01','01:01:01');
    +----------------------------------------+
    | addtime(DATE '1998-01-01', '01:01:01') |
    +----------------------------------------+
    | 1998-01-01 01:01:01                    |                    
    select addtime(time '00:00:00','01:01:01');
    +--------------------------------------+
    | addtime(TIME '00:00:00', '01:01:01') |
    +--------------------------------------+
    | 01:01:01                             |                    
    select addtime(datetime '2001-1-22 00:00:00','01:01:01');
    +----------------------------------------------------+
    | addtime(DATETIME '2001-1-22 00:00:00', '01:01:01') |
    +----------------------------------------------------+
    | 2001-01-22 01:01:01                                |                    
    select addtime(timestamp '2001-1-22 00:00:00','01:01:01');
    +-----------------------------------------------------+
    | addtime(TIMESTAMP '2001-1-22 00:00:00', '01:01:01') |
    +-----------------------------------------------------+
    | 2001-01-22 01:01:01                                 |                    
    select addtime('2001-1-22 00:00:00','01:01:01');
    +-------------------------------------------+
    | addtime('2001-1-22 00:00:00', '01:01:01') |
    +-------------------------------------------+
    | 2001-01-22 01:01:01                       |                    

CONVERT_TZ

CONVERT_TZ(dt,from_tz,to_tz)           
  • Description: This function converts a datetime value dt from the time zone specified by from_tz to the time zone specified by to_tz and returns the result.
  • Parameter types:
    convert_tz(varchar, varchar, varchar)                   
  • Return value type: DATETIME.
  • Example:
    select convert_tz('2004-01-01 12:00:00','+00:00','+10:00');
    +-------------------------------------------------------+
    | convert_tz('2004-01-01 12:00:00', '+00:00', '+10:00') |
    +-------------------------------------------------------+
    |                                   2004-01-01 22:00:00 |                   
    select convert_tz('2004-01-01 12:00:00','GMT','MET');
    +-------------------------------------------------+
    | convert_tz('2004-01-01 12:00:00', 'GMT', 'MET') |
    +-------------------------------------------------+
    |                             2004-01-01 13:00:00 |                    

CURDATE

CURDATE()            
  • Description: This function returns the current date.
  • Return value type: DATE.
  • Example:
    select curdate;
    +------------+
    | curdate()  |
    +------------+
    | 2019-05-25 |                    

CURTIME

CURTIME()            
  • Description: This function returns the current time.
  • Return value type: TIME.
  • Example:
    select curtime();
    +--------------+
    | curtime()    |
    +--------------+
    | 14:39:22.109 |                   

DATE

DATE(expr)            
  • Description: This function returns the date part of a date or datetime expression.
  • Parameter types:
    date(timestamp)
    date(datetime)
    date(varchar)                   
  • Return value type: DATE.
  • Example:
    select date(timestamp '2003-12-31 01:02:03');
    +---------------------------------------+
    | date(TIMESTAMP '2003-12-31 01:02:03') |
    +---------------------------------------+
    | 2003-12-31                            |                   
    select date(datetime '2003-12-31 01:02:03');
    +--------------------------------------+
    | date(DATETIME '2003-12-31 01:02:03') |
    +--------------------------------------+
    | 2003-12-31                           |                    
    select date('2003-12-31 01:02:03');
    +-----------------------------+
    | date('2003-12-31 01:02:03') |
    +-----------------------------+
    | 2003-12-31                  |                    

DATE_FORMAT

DATE_FORMAT(date,format)           
  • Description: This function returns a date as a string in the specified format. The following table describes the format specifiers.
    %a The abbreviated day of a week. Valid values: Sun to Sat.
    %b The abbreviated month name. Valid values: Jan to Dec.
    %c The month in the numeric format. Valid values: 0 to 12.
    %d The day of the month in the numeric format. Valid values: 00 to 31.
    %e The day of the month in the numeric format. Valid values: 0 to 31.
    %f The microseconds. Valid values: 000000 to 999999.
    %H The hour. Valid values: 00 to 23.
    %h The hour. Valid values: 01 to 12.
    %I The hour. Valid values: 01 to 12.
    %i The minutes in the numeric format. Valid values: 00 to 59.
    %j The day of the year. Valid values: 001 to 366.
    %k The hour. Valid values: 0 to 23.
    %l The hour. Valid values: 1 to 12.
    %M The name of the month. Valid values: January to December.
    %m The month in the numeric format. Valid values: 00 to 12.
    %p The abbreviated time period in the 12-hour format. Valid values: AM and PM.
    %r The time in the 12-hour format (hh:mm:ss AM or hh:mm:ss PM).
    %S The seconds. Valid values: 00 to 59.
    %s The seconds. Valid values: 00 to 59.
    %T The time in the 24-hour format (hh:mm:ss).
    %v The number of the week in the year. Monday is the first day of a week. This specifier applies to WEEK() mode 3 and is used with %x.
    %W The name of the weekday. Valid values: Sunday to Saturday.
    %x The year of the week in the numeric format. Monday is the first day of a week. This specifier is used with %v, and the value contains four digits.
    %Y The year in the four-digit format.
    %y The year in the two-digit format.
    %% The percent sign (%).
    %x x, for any "x" not listed above.
  • Parameter types:
    date_format(timestamp, varchar)
    date_format(varchar, varchar) 
    date_format(datetime, varchar)
    date_format(date, varchar)                   
  • Return value type: VARCHAR.
  • Example:
    select date_format(timestamp '2019-05-27 13:23:00', '%W %M %Y')as result;
    +-----------------+
    | result          |
    +-----------------+
    | Monday May 2019 |                    
    select date_format(timestamp '2019-05-27 13:23:00', '%W %M %Y')as result;
    +-----------------+
    | result          |
    +-----------------+
    | Monday May 2019 |                    
    select date_format(timestamp '2019-05-27 13:23:00', '%W %M %Y')as result;
    +-----------------+
    | result          |
    +-----------------+
    | Monday May 2019 |                    
    select date_format(date '2019-05-27', '%W %M %Y')as result;
    +-----------------+
    | result          |
    +-----------------+
    | Monday May 2019 |                    

SUBDATE/DATE_SUB

DATE_SUB(date,INTERVAL expr unit)            
  • Description: This function subtracts the interval specified by INTERVAL from the date specified by date.

    Valid values of unit: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, and year_month. Default value of unit: day.

  • Parameter types:
    subdate(date, INTERVAL expr unit)
    subdate(timestamp, INTERVAL expr unit)
    subdate(datetime, INTERVAL expr unit)
    subdate(varchar, INTERVAL expr unit)
    subdate(date, bigint)
    subdate(date, varchar)
    subdate(datetime, bigint)
    subdate(datetime, varchar)
    subdate(timestamp, bigint)
    subdate(timestamp, varchar)
    subdate(varchar, bigint)
    subdate(varchar, varchar)                    
  • Return value type: DATE.
  • Example:
    select date_sub(date '2001-1-22',interval '3' day);
    +-----------------------------------------------+
    | date_sub(DATE '2001-1-22', INTERVAL  '3' DAY) |
    +-----------------------------------------------+
    | 2001-01-19                                    |                    
    select date_sub(timestamp '2001-1-22 00:00:00',interval '3' day)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-19 00:00:00 |                    
    select date_sub(datetime '2001-1-22 00:00:00',interval '3' day)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-19 00:00:00 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' day);
    +---------------------------------------------------+
    | date_sub('2001-1-22 00:00:00', INTERVAL  '3' DAY) |
    +---------------------------------------------------+
    | 2001-01-19 00:00:00                               |                    
    select date_sub('2001-1-22 00:00:00',interval '3' second);
    +------------------------------------------------------+
    | date_sub('2001-1-22 00:00:00', INTERVAL  '3' SECOND) |
    +------------------------------------------------------+
    | 2001-01-21 23:59:57                                  |                    
    select date_sub('2001-1-22 00:00:00',interval '3' minute);
    +------------------------------------------------------+
    | date_sub('2001-1-22 00:00:00', INTERVAL  '3' MINUTE) |
    +------------------------------------------------------+
    | 2001-01-21 23:57:00                                  |                    
    
    select date_sub('2001-1-22 00:00:00',interval '3' hour);
    +----------------------------------------------------+
    | date_sub('2001-1-22 00:00:00', INTERVAL  '3' HOUR) |
    +----------------------------------------------------+
    | 2001-01-21 21:00:00                                |
    					
    select date_sub('2001-1-22 00:00:00',interval '3' month);
    +-----------------------------------------------------+
    | date_sub('2001-1-22 00:00:00', INTERVAL  '3' MONTH) |
    +-----------------------------------------------------+
    | 2000-10-22 00:00:00                                 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' year);
    +----------------------------------------------------+
    | date_sub('2001-1-22 00:00:00', INTERVAL  '3' YEAR) |
    +----------------------------------------------------+
    | 1998-01-22 00:00:00                                |                    
    select date_sub('2001-1-22 00:00:00',interval '3' minute_second)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-21 23:57:00 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' hour_second)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-21 21:00:00 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' hour_minute)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-21 21:00:00 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' day_second)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-19 00:00:00 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' day_minute)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-19 00:00:00 |                    
    select date_sub('2001-1-22 00:00:00',interval '3' day_hour)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2001-01-19 00:00:00 |                   
    select date_sub('2001-1-22 00:00:00',interval '3' year_month)as result;
    +---------------------+
    | result              |
    +---------------------+
    | 1998-01-22 00:00:00 |                    
    select date_sub(date '2001-1-22 00:00:00',3);
    +----------------------------------------+
    | date_sub(DATE '2001-1-22 00:00:00', 3) |
    +----------------------------------------+
    | 2001-01-19                             |                    
    
    select date_sub(date '2001-1-22 00:00:00','3');
    +------------------------------------------+
    | date_sub(DATE '2001-1-22 00:00:00', '3') |
    +------------------------------------------+
    | 2001-01-19                               |
    					
    select date_sub(datetime '2001-1-22 00:00:00',3);
    +--------------------------------------------+
    | date_sub(DATETIME '2001-1-22 00:00:00', 3) |
    +--------------------------------------------+
    |                        2001-01-19 00:00:00 |                  
    select date_sub(datetime '2001-1-22 00:00:00','3');
    +----------------------------------------------+
    | date_sub(DATETIME '2001-1-22 00:00:00', '3') |
    +----------------------------------------------+
    |                          2001-01-19 00:00:00 |                   
    select date_sub(timestamp '2001-1-22 00:00:00',3);
    +---------------------------------------------+
    | date_sub(TIMESTAMP '2001-1-22 00:00:00', 3) |
    +---------------------------------------------+
    |                         2001-01-19 00:00:00 |                    
    select date_sub(timestamp '2001-1-22 00:00:00','3');
    +-----------------------------------------------+
    | date_sub(TIMESTAMP '2001-1-22 00:00:00', '3') |
    +-----------------------------------------------+
    |                           2001-01-19 00:00:00 |
    					
    select date_sub('2001-1-22 00:00:00',3);
    +-----------------------------------+
    | date_sub('2001-1-22 00:00:00', 3) |
    +-----------------------------------+
    | 2001-01-19 00:00:00               |                   
    select date_sub('2001-1-22 00:00:00','3');
    +-------------------------------------+
    | date_sub('2001-1-22 00:00:00', '3') |
    +-------------------------------------+
    | 2001-01-19 00:00:00                 |                   

DATEDIFF

DATEDIFF(expr1,expr2)            
  • Description: This function subtracts the date specified by expr2 from the date specified by expr1.
  • Parameter types:
    datediff(varchar, varchar) 
    datediff(datetime, varchar)
    datediff(varchar, datetime)
    datediff(datetime, datetime)
    datediff(varchar, timestamp)
    datediff(timestamp, timestamp)
    datediff(timestamp, varchar)
    datediff(date, date)
    datediff(date, varchar)
    datediff(varchar, date)                    
  • Return value type: BIGINT.
  • Example:
    select datediff('2007-12-31 23:59:59','2007-12-30');
    +-----------------------------------------------+
    | datediff('2007-12-31 23:59:59', '2007-12-30') |
    +-----------------------------------------------+
    |                                             1 |                   
    select datediff(datetime '2007-12-31 23:59:59','2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |
    select datediff('2007-12-31 23:59:59',datetime '2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                    
    select datediff(datetime '2007-12-31 23:59:59',datetime '2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                   
    select datediff('2007-12-31 23:59:59',timestamp '2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                    
    select datediff(timestamp '2007-12-31 23:59:59',timestamp '2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                    
    select datediff(timestamp '2007-12-31 23:59:59','2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                    
    select datediff(date '2007-12-31 23:59:59',date '2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                    
    select datediff(date '2007-12-31 23:59:59','2007-12-30')as result;
    +--------+
    | result |
    +--------+
    |      1 |                   
    select datediff('2008-12-31',date '2007-12-30');
    +-------------------------------------------+
    | datediff('2008-12-31', DATE '2007-12-30') |
    +-------------------------------------------+
                                           367                   

DAY/DAYOFMONTH

DAY(date)
DAYOFMONTH(date)         
  • Description: This function returns the day of the month for the date specified by date. Valid values: [1,31].
  • Parameter types:
    dayofmonth(timestamp)
    dayofmonth(datetime)
    dayofmonth(date)
    dayofmonth(time)
    dayofmonth(varchar)                    
  • Return value type: BIGINT.
  • Example:
    select dayofmonth(timestamp '2007-02-03 12:23:09');
    +---------------------------------------------+
    | dayofmonth(TIMESTAMP '2007-02-03 12:23:09') |
    +---------------------------------------------+
    |                                           3 |                    
    select dayofmonth(date '2007-02-03');
    +-------------------------------+
    | dayofmonth(DATE '2007-02-03') |
    +-------------------------------+
    |                             3 |                   
    select dayofmonth(time '17:01:10');
    +-----------------------------+
    | dayofmonth(TIME '17:01:10') |
    +-----------------------------+
    |                          30 |                    
    select day('2007-02-03');
    +-------------------+
    | day('2007-02-03') |
    +-------------------+
    |                 3 |                   
    select dayofmonth(datetime '2007-02-03 00:00:00');
    +--------------------------------------------+
    | dayofmonth(DATETIME '2007-02-03 00:00:00') |
    +--------------------------------------------+
    |                                          3 |                   

DAYNAME

DAYNAME(date)            
  • Description: This function returns the day of the week for a date, such as Monday.
  • Parameter types:
    dayname(timestamp)
    dayname(datetime)
    dayname(date)
    dayname(varchar)                    
  • Return value type: VARCHAR.
  • Example:
    select dayname(timestamp '2007-02-03 00:00:00');
    +------------------------------------------+
    | dayname(TIMESTAMP '2007-02-03 00:00:00') |
    +------------------------------------------+
    | Saturday                                 |                    
    select dayname(datetime '2007-02-03 00:00:00');
    +-----------------------------------------+
    | dayname(DATETIME '2007-02-03 00:00:00') |
    +-----------------------------------------+
    | Saturday                                |                   
    select dayname(date '2007-02-04');
    +----------------------------+
    | dayname(DATE '2007-02-04') |
    +----------------------------+
    | Sunday                     |                    
    select dayname('2007-02-03');
    +-----------------------+
    | dayname('2007-02-03') |
    +-----------------------+
    | Saturday              |                    

DAYOFWEEK

DAYOFWEEK(date)            
  • Description: This function returns the day of the week as a numeric value for a date, where 1 is for Sunday, 2 for Monday, and 7 for Saturday.
  • Parameter types:
    dayofweek(timestamp)
    dayofweek(datetime)
    dayofweek(date)
    dayofweek(varchar)                    
  • Return value type: BIGINT.
  • Example:
    select dayofweek(timestamp '2007-02-03 00:00:00');
    +--------------------------------------------+
    | dayofweek(TIMESTAMP '2007-02-03 00:00:00') |
    +--------------------------------------------+
    |                                          7                     
    select dayofweek(datetime '2007-02-03 00:00:00');
    +-------------------------------------------+
    | dayofweek(DATETIME '2007-02-03 00:00:00') |
    +-------------------------------------------+
    |                                         7 |                   
    select dayofweek(date '2007-02-03');
    +------------------------------+
    | dayofweek(DATE '2007-02-03') |
    +------------------------------+
    |                            7 |                    
    select dayofweek('2007-02-03');
    +-------------------------+
    | dayofweek('2007-02-03') |
    +-------------------------+
    |          7              |                   

DAYOFYEAR

DAYOFYEAR(date)            
  • Description: This function returns the day of the year for a date. Valid values: [1,366].
  • Parameter types:
    dayofyear(timestamp)
    dayofyear(datetime) 
    dayofyear(date) 
    dayofyear(varchar)                    
  • Return value type: BIGINT.
  • Example:
    select dayofyear(timestamp '2007-02-03 00:12:12');
    +--------------------------------------------+
    | dayofyear(TIMESTAMP '2007-02-03 00:12:12') |
    +--------------------------------------------+
    |                                         34 |                   
    select dayofyear(datetime '2007-02-03 00:12:12');
    +-------------------------------------------+
    | dayofyear(DATETIME '2007-02-03 00:12:12') |
    +-------------------------------------------+
    |                                        34 |                    
    select dayofyear(date '2007-02-03');
    +------------------------------+
    | dayofyear(DATE '2007-02-03') |
    +------------------------------+
    |                           34 |                   
    select dayofyear('2007-02-03');
    +-------------------------+
    | dayofyear('2007-02-03') |
    +-------------------------+
    |            34           |                    

EXTRACT

EXTRACT(unit FROM date)           
  • Description: This function returns one or more separate parts of a date or time in the specified unit. For example, this function can return the year, month, day, hour, or minute of a date or time.

    Valid values of unit: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, and year_month.

  • Supported input parameter types: VARCHAR, TIMESTAMP, DATETIME, and TIME.
  • Return value type: BIGINT.
  • Example:
    select extract(second from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    |    34 |                    
    select extract(minute from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    |    12 |                   
    select extract(hour from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    |     0 |                    
    select extract(month from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    |     7 |                    
    select extract(minute_second from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    |  1234 |                    
    select extract(hour_second from '2019-07-02 12:12:34');
    +--------+
    | _col0  |
    +--------+
    | 121234 |                    
    select extract(hour_minute from '2019-07-02 12:12:34');  
    +-------+
    | _col0 |
    +-------+
    |  1212 |                    
    select extract(day_second from '2019-07-02 12:12:34');
    +---------+
    | _col0   |
    +---------+
    | 2121234 |                    
    select extract(day_hour from '2019-07-02 12:12:34');
    +-------+
    | _col0 |
    +-------+
    |   212 |                    
    select extract(day from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    |     2 |                   
    select extract(year_month from '2019-07-02 00:12:34');
    +--------+
    | _col0  |
    +--------+
    | 201907 |                    
    select extract(day_minute from '2019-07-02 00:12:34');
    +-------+
    | _col0 |
    +-------+
    | 20012 |                    
    select extract(year from timestamp  '2019-05-30');
    +-------+
    | _col0 |
    +-------+
    |  2019 |                   
    select extract(year from datetime  '2019-05-30');
    +-------+
    | _col0 |
    +-------+
    |  2019 |                    
    select extract(year from time '15:23:22');
    +-------+
    | _col0 |
    +-------+
    |  2019 |                    

FROM_DAYS

FROM_DAYS(N)            
  • Description: This function returns a DATE value based on the parameter N indicating the number of days.
  • Parameter types:
    from_days(varchar) 
    from_days(bigint)
  • Return value type: DATE.
  • Example:
    select from_days(730669);
    +-------------------+
    | from_days(730669) |
    +-------------------+
    | 2000-07-03        |                    
    select from_days('730669');
    +---------------------+
    | from_days('730669') |
    +---------------------+
    | 2000-07-03          |                    

FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp[,format])            
  • Description: This function returns the UNIX timestamp specified by unixtime in the specified format.

    The format parameter conforms to the format in the DATE_FORMAT function.

  • Parameter types:
    from_unixtime(varchar, varchar)
    from_unixtime(varchar)
    from_unixtime(double, varchar)
    from_unixtime(double)                   
  • Return value type: DATETIME.
  • Example:
    select from_unixtime('1447430881','%Y %M %h:%i:%s %x');
    +--------------------------------------------------+
    | from_unixtime('1447430881', '%Y %M %h:%i:%s %x') |
    +--------------------------------------------------+
    | 2015 November 12:08:01 2015                      |                   
    select from_unixtime('1447430881');
    +-----------------------------+
    | from_unixtime('1447430881') |
    +-----------------------------+
    |         2015-11-14 00:08:01 |                    
    select from_unixtime(1447430881);
    +---------------------------+
    | from_unixtime(1447430881) |
    +---------------------------+
    |       2015-11-14 00:08:01 |                    
    select from_unixtime(1447430881,'%Y %M %h:%i:%s %x');
    +------------------------------------------------+
    | from_unixtime(1447430881, '%Y %M %h:%i:%s %x') |
    +------------------------------------------------+
    | 2015 November 12:08:01 2015                    |                    

HOUR

HOUR(time)            
  • Description: This function returns the hour part of a time.
  • Parameter types:
    hour(timestamp)
    hour(datetime)
    hour(date)
    hour(time)
    hour(varchar)                    
  • Return value type: BIGINT.
  • Example:
    select hour(timestamp '2019-12-07 10:05:03');
    +---------------------------------------+
    | hour(TIMESTAMP '2019-12-07 10:05:03') |
    +---------------------------------------+
    |                                    10 |                    
    select hour(datetime '2019-12-07 10:05:03');
    +--------------------------------------+
    | hour(DATETIME '2019-12-07 10:05:03') |
    +--------------------------------------+
    |                                   10 |                    
    select hour(date '2019-12-07');
    +-------------------------+
    | hour(DATE '2019-12-07') |
    +-------------------------+
    |                       0 |                    
    select hour(time '10:05:03');
    +-----------------------+
    | hour(TIME '10:05:03') |
    +-----------------------+
    |                    10 |                   
    select hour('10:05:03');
    +------------------+
    | hour('10:05:03') |
    +------------------+
    |       10         |                   

LAST_DAY

LAST_DAY(date)            
  • Description: This function returns the last day of the month for a date or datetime.
  • Parameter types:
    last_day(varchar)
    last_day(timestamp)
    last_day(datetime)
    last_day(date)                    
  • Return value type: DATE.
  • Example:
    select last_day('2003-02-05');
    +------------------------+
    | last_day('2003-02-05') |
    +------------------------+
    | 2003-02-28             |                    
    select last_day(timestamp '2003-02-05 12:12:12');
    +-------------------------------------------+
    | last_day(TIMESTAMP '2003-02-05 12:12:12') |
    +-------------------------------------------+
    | 2003-02-28                                |                    
    select last_day(datetime '2003-02-05 12:12:12');
    +------------------------------------------+
    | last_day(DATETIME '2003-02-05 12:12:12') |
    +------------------------------------------+
    | 2003-02-28                               |                    
    select last_day(date '2003-02-05');
    +-----------------------------+
    | last_day(DATE '2003-02-05') |
    +-----------------------------+
    | 2003-02-28                  |                    

LOCALTIME/LOCALTIMESTAMP/NOW

localtime
localtime()
localtimestamp
localtimestamp()
now()            
  • Description: This function returns the current timestamp.
  • Return value type: DATETIME.
  • Example:
    select now();
    +---------------------+
    | now()              |
    +---------------------+
    | 2019-05-25 00:28:37                   
    select localtime;
    +---------------------+
    | localtime()         |
    +---------------------+
    | 2019-05-28 20:44:25 |                   
    select localtime();
    +---------------------+
    | localtime()         |
    +---------------------+
    | 2019-05-31 17:37:36 |                    
    select localtimestamp;
    +---------------------+
    | localtimestamp()    |
    +---------------------+
    | 2019-05-28 20:44:44 |                   
    select localtimestamp();
    +---------------------+
    | localtimestamp()    |
    +---------------------+
    | 2019-05-31 17:38:13 |                   

MAKEDATE

MAKEDATE(year,dayofyear)
  • Description: This function returns a date based on the year and dayofyear parameters.
  • Parameter types:
    makedate(bigint, bigint)
    makedate(varchar, varchar)                    
  • Return value type: DATE.
  • Example:
    select makedate(2011,31), makedate(2011,32);
    +--------------------+--------------------+
    | makedate(2011, 31) | makedate(2011, 32) |
    +--------------------+--------------------+
    | 2011-01-31         | 2011-02-01         |
    select makedate('2011','31'), makedate('2011','32');
    +------------------------+------------------------+
    | makedate('2011', '31') | makedate('2011', '32') |
    +------------------------+------------------------+
    | 2011-01-31             | 2011-02-01             |                   

MAKETIME

MAKETIME(hour,minute,second)            
  • Description: This function returns a time based on the hour, minute, and second parameters.
  • Parameter types:
    maketime(bigint, bigint, bigint)
    maketime(varchar, varchar, varchar)                    
  • Return value type: TIME.
  • Example:
    select maketime(12,15,30);
    +----------------------+
    | maketime(12, 15, 30) |
    +----------------------+
    | 12:15:30             |                    
    select maketime('12','15','30');
    +----------------------------+
    | maketime('12', '15', '30') |
    +----------------------------+
    | 12:15:30                   |                   

MINUTE

MINUTE(time)           
  • Description: This function returns the minute part of a time.
  • Parameter types:
    minute(timestamp)
    minute(datetime)
    minute(date)
    minute(time)
    minute(varchar)                    
  • Return value type: BIGINT.
  • Example:
    select minute(timestamp '2008-02-03 10:05:03');
    +-----------------------------------------+
    | minute(TIMESTAMP '2008-02-03 10:05:03') |
    +-----------------------------------------+
    |                                       5 |                    
    
    select minute(datetime '2008-02-03 10:05:03');
    +----------------------------------------+
    | minute(DATETIME '2008-02-03 10:05:03') |
    +----------------------------------------+
    |                                      5 |
    select minute(date '2008-02-03');
    +---------------------------+
    | minute(DATE '2008-02-03') |
    +---------------------------+
    |                         0 |
    select minute(time '12:12:12');
    +-------------------------+
    | minute(TIME '12:12:12') |
    +-------------------------+
    |                      12 |
    select minute('2008-02-03 10:05:03');
    +-------------------------------+
    | minute('2008-02-03 10:05:03') |
    +-------------------------------+
    |            5                  |

MONTH

MONTH(date)
  • Description: This function returns the month part of a date.
  • Parameter types:
    month(timestamp)
    month(datetime)
    month(date)
    month(time)
    month(varchar)                   
  • Return value type: BIGINT.
  • Example:
    select month(timestamp '2008-02-03 00:00:00');
    +----------------------------------------+
    | month(TIMESTAMP '2008-02-03 00:00:00') |
    +----------------------------------------+
    |                                      2 |
    select month(datetime '2008-02-03 00:00:00');
    +---------------------------------------+
    | month(DATETIME '2008-02-03 00:00:00') |
    +---------------------------------------+
    |                                     2 |
    select month(date '2008-02-03');
    +--------------------------+
    | month(DATE '2008-02-03') |
    +--------------------------+
    |                        2 |

    The MONTH function can also return the month when an SQL statement is executed. In the following example, 5 is returned for an SQL statement that is executed in May, 2019.

    select month(time '12:12:12');
    +------------------------+
    | month(TIME '12:12:12') |
    +------------------------+
    |                      5 |
    select month('2008-02-03');
    +---------------------+
    | month('2008-02-03') |
    +---------------------+
    |                   2 |
    					

MONTHNAME

MONTHNAME(date)
  • Description: This function returns the full name of the month for a date.
  • Parameter types:
    monthname(timestamp)
    monthname(datetime)
    monthname(date)
    monthname(varchar)                    
  • Return value type: VARCHAR.
  • Example:
    select monthname(timestamp '2008-02-03 00:00:00');
    +--------------------------------------------+
    | monthname(TIMESTAMP '2008-02-03 00:00:00') |
    +--------------------------------------------+
    | February                                   |                   
    select monthname(datetime '2008-02-03 00:00:00');
    +-------------------------------------------+
    | monthname(DATETIME '2008-02-03 00:00:00') |
    +-------------------------------------------+
    | February                                  |                    
    select monthname(date '2008-02-03');
    +------------------------------+
    | monthname(DATE '2008-02-03') |
    +------------------------------+
    | February                     |                    
    select monthname('2008-02-03');
    +-------------------------+
    | monthname('2008-02-03') |
    +-------------------------+
    | February                |                    

PERIOD_ADD

PERIOD_ADD(P,N)            
  • Description: This function adds a number of months specified by N to the period specified by P.
  • Parameter types:
    period_add(bigint, bigint)
    period_add(varchar, varchar) 
    period_add(varchar, bigint)                    
  • Return value type: BIGINT.
  • Example:
    select period_add(200801,2);
    +-----------------------+
    | period_add(200801, 2) |
    +-----------------------+
    |                200803 |                    
    select period_add('200801','2');
    +---------------------------+
    | period_add('200801', '2') |
    +---------------------------+
    |                    200803 |                    
    select period_add('200801',2);
    +-------------------------+
    | period_add('200801', 2) |
    +-------------------------+
    |                  200803 |                    

PERIOD_DIFF

PERIOD_DIFF(P1,P2)            
  • Description: This function returns the number of months between the two periods specified by P1 and P2.
  • Parameter types:
    period_diff(bigint, bigint)
    period_diff(varchar, varchar)                    
  • Return value type: BIGINT.
  • Example:
    select period_diff(200802,200703);
    +-----------------------------+
    | period_diff(200802, 200703) |
    +-----------------------------+
    |                          11 |                    
    select period_diff('200802','200703');
    +---------------------------------+
    | period_diff('200802', '200703') |
    +---------------------------------+
    |                              11 |                   

QUARTER

QUARTER(date)
  • Description: This function returns the quarter of the year for a date. Valid values: [1,4].
  • Parameter types:
    quarter(datetime)
    quarter(varchar)
    quarter(timestamp)
    quarter(date)                    
  • Return value type: BIGINT.
  • Example:
    select quarter(datetime '2008-04-01 12:12:12');
    +-----------------------------------------+
    | quarter(DATETIME '2008-04-01 12:12:12') |
    +-----------------------------------------+
    |                                       2 |                    
    select quarter('2008-04-01');
    +-----------------------+
    | quarter('2008-04-01') |
    +-----------------------+
    |                     2 |                    
    select quarter(timestamp '2008-04-01 12:12:12');
    +------------------------------------------+
    | quarter(TIMESTAMP '2008-04-01 12:12:12') |
    +------------------------------------------+
    |                                        2 |                    
    select quarter(date '2008-04-01');
    +----------------------------+
    | quarter(DATE '2008-04-01') |
    +----------------------------+
    |                          2 |                    

SEC_TO_TIME

SEC_TO_TIME(seconds)           
  • Description: This function converts a quantity of seconds specified by seconds to a time.
  • Parameter types:
    sec_to_time(bigint)
    sec_to_time(varchar)                   
  • Return value type: TIME.
  • Example:
    select sec_to_time(2378);
    +-------------------+
    | sec_to_time(2378) |
    +-------------------+
    | 00:39:38          |                    
    select sec_to_time('2378');
    +---------------------+
    | sec_to_time('2378') |
    +---------------------+
    | 00:39:38                                

SECOND

SECOND(time)
  • Description: This function returns the second part of the specified time. Valid values: [0,59].
  • Parameter types:
    second(timestamp)
    second(datetime)
    second(date)
    second(time)
    second(varchar)                   
  • Return value type: BIGINT.
  • Example:
    select second(timestamp '2019-03-12 12:13:14');
    +-----------------------------------------+
    | second(TIMESTAMP '2019-03-12 12:13:14') |
    +-----------------------------------------+
    |                                      14 |                    
    select second(datetime '2019-03-12 12:13:14');
    +----------------------------------------+
    | second(DATETIME '2019-03-12 12:13:14') |
    +----------------------------------------+
    |                                     14 |                    
    select second(date '2019-03-12');
    +---------------------------+
    | second(DATE '2019-03-12') |
    +---------------------------+
    |                         0 |
    select second(time '12:13:14'); 
    +-------------------------+
    | second(TIME '12:13:14') |
    +-------------------------+
    |                      14 |
    select second('12:12:23');
    +--------------------+
    | second('12:12:23') |
    +--------------------+
    |                 23 |

STR_TO_DATE

STR_TO_DATE(str,format)
  • Description: This function converts a string to a date or datetime in the specified format.

    The format parameter conforms to the format in the DATE_FORMAT function.

  • Parameter types:
    str_to_date(varchar, varchar)
  • Return value type: DATETIME.
  • Example:
    select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2017-01-06 10:20:30 |                    

SUBTIME

SUBTIME(expr1,expr2)
  • Description: This function subtracts the interval specified by expr2 from the time specified by expr1.
  • Parameter types:
    subtime(date, varchar)
    subtime(datetime, varchar)
    subtime(timestamp, varchar)
    subtime(time, varchar)
    subtime(varchar, varchar)                   
  • Return value type: DATETIME.
  • Example:
    select subtime(date '2018-10-31','0:1:1');
    +-------------------------------------+
    | subtime(DATE '2018-10-31', '0:1:1') |
    +-------------------------------------+
    |                 2018-10-30 23:58:59 |                    
    
    select subtime(datetime '2018-10-31 12:12:12','0:1:1');
    +--------------------------------------------------+
    | subtime(DATETIME '2018-10-31 12:12:12', '0:1:1') |
    +--------------------------------------------------+
    |                              2018-10-31 12:11:11 |
    					
    select subtime(timestamp '2018-10-31 12:12:12','0:1:1');
    +---------------------------------------------------+
    | subtime(TIMESTAMP '2018-10-31 12:12:12', '0:1:1') |
    +---------------------------------------------------+
    |                               2018-10-31 12:11:11 |                   
    select subtime(time '12:12:12','0:1:1');
    +-----------------------------------+
    | subtime(TIME '12:12:12', '0:1:1') |
    +-----------------------------------+
    | 12:11:11                          |
    +-----------------------------------+                   
    select subtime('2018-10-31 23:59:59','0:1:1');
    +-----------------------------------------+
    | subtime('2018-10-31 23:59:59', '0:1:1') |
    +-----------------------------------------+
    | 2018-10-31 23:58:58                     |                   

SYSDATE

SYSDATE()
  • Description: This function returns the system time.
  • Return value type: DATETIME.
  • Example:
    select sysdate();
    +---------------------+
    | sysdate()           |
    +---------------------+
    | 2019-05-26 00:47:21 |                    

TIME

TIME(expr)
  • Description: This function returns the time part of the date or datetime expression specified by expr as a string.
  • Parameter types:
    time(varchar)
    time(datetime)
    time(timestamp)                    
  • Return value type: VARCHAR.
  • Example:
    select time('2003-12-31 01:02:03');
    +-----------------------------+
    | time('2003-12-31 01:02:03') |
    +-----------------------------+
    | 01:02:03                    |                   
    select time(datetime '2003-12-31 01:02:03');
    +--------------------------------------+
    | time(DATETIME '2003-12-31 01:02:03') |
    +--------------------------------------+
    | 01:02:03                             |                   
    select time(timestamp '2003-12-31 01:02:03');
    +---------------------------------------+
    | time(TIMESTAMP '2003-12-31 01:02:03') |
    +---------------------------------------+
    | 01:02:03                              |                    

TIME_FORMAT

TIME_FORMAT(time,format)
  • Description: This function returns the time specified by time as a string in the specified format.

    The format parameter conforms to the format in the DATE_FORMAT function.

  • Parameter types:
    time_format(varchar, varchar) 
    time_format(timestamp, varchar)
    time_format(datetime, varchar)
    time_format(time, varchar) 
    time_format(date, varchar)                    
  • Return value type: VARCHAR.
  • Example:
    select time_format('12:00:00', '%H %k %h %I %l');
    +-------------------------------------------+
    | time_format('12:00:00', '%H %k %h %I %l') |
    +-------------------------------------------+
    | 12 12 12 12 12                            |                   
    select time_format(timestamp '1998-01-01 23:00:00','%H %k %h %I %l')as result;
    +----------------+
    | result         |
    +----------------+
    | 23 23 11 11 11 |                   
    select time_format(datetime '1998-01-01 23:00:00','%H %k %h %I %l')as result;
    +----------------+
    | result         |
    +----------------+
    | 23 23 11 11 11 |                    
    select time_format(time '23:00:00','%H %k %h %I %l');
    +------------------------------------------------+
    | time_format(TIME '23:00:00', '%H %k %h %I %l') |
    +------------------------------------------------+
    | 23 23 11 11 11                                 |                    
    select time_format(date '1998-01-01','%H %k %h %I %l');
    +--------------------------------------------------+
    | time_format(DATE '1998-01-01', '%H %k %h %I %l') |
    +--------------------------------------------------+
    | 00 0 12 12 12                                    |                    

TIME_TO_SEC

TIME_TO_SEC(time)
  • Description: This function converts the time specified by time to a quantity of seconds.
  • Parameter types:
    time_to_sec(varchar)
    time_to_sec(datetime)
    time_to_sec(timestamp)
    time_to_sec(date)
    time_to_sec(time)
  • Return value type: BIGINT.
  • Example:
    select time_to_sec(datetime '2009-12-12 22:23:00');
    +---------------------------------------------+
    | time_to_sec(DATETIME '2009-12-12 22:23:00') |
    +---------------------------------------------+
    |                                       80580 |                   
    select time_to_sec(timestamp '2009-12-12 22:23:00');
    +----------------------------------------------+
    | time_to_sec(TIMESTAMP '2009-12-12 22:23:00') |
    +----------------------------------------------+
    |                                        80580 |                    
    select time_to_sec(date '2009-12-12');
    +--------------------------------+
    | time_to_sec(DATE '2009-12-12') |
    +--------------------------------+
    |                              0 |                    
    select time_to_sec(time '12:12:12');
    +------------------------------+
    | time_to_sec(TIME '12:12:12') |
    +------------------------------+
    |                        43932 |                   
    select time_to_sec('22:23:00');
    +-------------------------+
    | time_to_sec('22:23:00') |
    +-------------------------+
    |                   80580 |                   

TIMEDIFF

TIMEDIFF(expr1,expr2)
  • Description: This function subtracts the time specified by expr2 from the time specified by expr1. This function is equivalent to the SUBTIME function.
  • Parameter types:
    timediff(time, varchar)
    timediff(time, time)
    timediff(varchar, varchar)                    
  • Return value type: DATETIME.
  • Example:
    select timediff(time '12:00:00','10:00:00');
    +---------------------------------------+
    | timediff(TIME '12:00:00', '10:00:00') |
    +---------------------------------------+
    | 02:00:00                              |                    
    select timediff('12:00:00','10:00:00');
    +----------------------------------+
    | timediff('12:00:00', '10:00:00') |
    +----------------------------------+
    | 02:00:00                         |                   
    select timediff(time '12:00:00',time '10:00:00');
    +--------------------------------------------+
    | timediff(TIME '12:00:00', TIME '10:00:00') |
    +--------------------------------------------+
    | 02:00:00                                   |                    

TIMESTAMP

TIMESTAMP(expr)
  • Description: This function returns the date or datetime expression specified by expr as a date or datetime value.
  • Parameter types:
    timestamp(date)
    timestamp(varchar)                    
  • Return value type: DATETIME.
  • Example:
    select timestamp(date '2019-05-27');
    +------------------------------+
    | timestamp(DATE '2019-05-27') |
    +------------------------------+
    | 2019-05-27 00:00:00          |                    
    select timestamp('2019-05-27');
    +-------------------------+
    | timestamp('2019-05-27') |
    +-------------------------+
    |     2019-05-27 00:00:00 |                    

TIMESTAMPADD

TIMESTAMPADD(unit,interval,datetime_expr)
  • Description: This function adds the interval specified by interval to the date or datetime expression specified by datetime_expr. unit specifies the unit of the interval.

    Valid values of unit: second, minute, hour, day, week, month, quarter, and year.

  • Parameter types:
    timestampadd(varchar, varchar, timestamp) 
    timestampadd(varchar, bigint, timestamp)
    timestampadd(varchar, varchar, date)
    timestampadd(varchar, bigint, date)
    timestampadd(varchar, varchar, datetime)
    timestampadd(varchar, bigint, datetime) 
    timestampadd(varchar, varchar, varchar)
    timestampadd(varchar, bigint, varchar)
  • Return value type: DATETIME.
  • Example:
    select timestampadd(second,'1',timestamp '2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 12:12:13 |
    select timestampadd(second,1,timestamp '2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 12:12:13 |                   
    select timestampadd(second,'1',date '2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 00:00:01 |                  
    select timestampadd(second,1,date '2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 00:00:01 |                    
    select timestampadd(second,'1',datetime '2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 12:12:13 |                   
    select timestampadd(second,1,datetime '2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 12:12:13 |
    select timestampadd(second,'1','2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 12:12:13 |                   
    select timestampadd(second,1,'2003-01-02 12:12:12')as result;
    +---------------------+
    | result              |
    +---------------------+
    | 2003-01-02 12:12:13 |
    select timestampadd(second,1,'2003-01-02 12:12:12');
    +--------------------------------------------------+
    | timestampadd('second', 1, '2003-01-02 12:12:12') |
    +--------------------------------------------------+
    | 2003-01-02 12:12:13                              |                    
    select timestampadd(minute,8820,'2019-08-24 09:00:00');
    +-----------------------------------------------------+
    | timestampadd('MINUTE', 8820, '2019-08-24 09:00:00') |
    +-----------------------------------------------------+
    | 2019-08-30 12:00:00                                                     
    select timestampadd(hour,1,'2003-01-02 12:12:12');
    +------------------------------------------------+
    | timestampadd('hour', 1, '2003-01-02 12:12:12') |
    +------------------------------------------------+
    | 2003-01-02 13:12:12                            |                    
    select timestampadd(day,1,'2003-01-02 12:12:12');
    +-----------------------------------------------+
    | timestampadd('day', 1, '2003-01-02 12:12:12') |
    +-----------------------------------------------+
    | 2003-01-03 12:12:12                           |                    
    select timestampadd(week,1,'2003-01-02 12:12:12');
    +------------------------------------------------+
    | timestampadd('week', 1, '2003-01-02 12:12:12') |
    +------------------------------------------------+
    | 2003-01-09 12:12:12                            |                    
    select timestampadd(month,1,'2003-01-02 12:12:12');
    +-------------------------------------------------+
    | timestampadd('month', 1, '2003-01-02 12:12:12') |
    +-------------------------------------------------+
    | 2003-02-02 12:12:12                             |                    
    select timestampadd(year,1,'2003-01-02 12:12:12');
    +------------------------------------------------+
    | timestampadd('year', 1, '2003-01-02 12:12:12') |
    +------------------------------------------------+
    | 2004-01-02 12:12:12                            |                    
    select timestampadd(quarter,1,'2003-01-02 12:12:12');
    +---------------------------------------------------+
    | timestampadd('quarter', 1, '2003-01-02 12:12:12') |
    +---------------------------------------------------+
    | 2003-04-02 12:12:12                               |

TIMESTAMPDIFF

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)            
  • Description: This function subtracts the interval specified by datetime_expr2 from the date or datetime expression specified by datetime_expr1. unit specifies the unit of the result.

    Valid values of unit: second, minute, hour, day, week, month, quarter, and year.

    Use this function in the same way as the TIMESTAMPADD function.

  • Parameter types:
    timestampdiff(varchar, timestamp, timestamp)
    timestampdiff(varchar, date, date)
    timestampdiff(varchar, datetime, datetime)
    timestampdiff(varchar, varchar, varchar)                    
  • Return value type: BIGINT.
  • Example:
    select timestampdiff(second,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    +---------+
    | result  |
    +---------+
    | 7689600 |                    
    select timestampdiff(minute,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    +--------+
    | result |
    +--------+
    | 128160 |                   
    select timestampdiff(hour,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    +--------+
    | result |
    +--------+
    |   2136 |                    
    select timestampdiff(day,timestamp '2003-02-01',timestamp '2003-05-01')as result;
    +--------+
    | result |
    +--------+
    |     89 |                   
    select timestampdiff(day,date '2003-02-01',date '2003-05-01');
    +------------------------------------------------------------+
    | timestampdiff('day', DATE '2003-02-01', DATE '2003-05-01') |
    +------------------------------------------------------------+
    |                                                         89 |                    
    select timestampdiff(day,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    +--------+
    | result |
    +--------+
    |     89 |                    
    select timestampdiff(day,'2003-02-01','2003-05-01');
    +--------------------------------------------------+
    | timestampdiff('day', '2003-02-01', '2003-05-01') |
    +--------------------------------------------------+
    |                                               89 |                    
    select timestampdiff(week,'2003-02-01','2003-05-01');
    +---------------------------------------------------+
    | timestampdiff('week', '2003-02-01', '2003-05-01') |
    +---------------------------------------------------+
    |                                                12 |
    select timestampdiff(quarter,'2003-02-01','2003-05-01');
    +------------------------------------------------------+
    | timestampdiff('quarter', '2003-02-01', '2003-05-01') |
    +------------------------------------------------------+
    |                                                    1 |
    select timestampdiff(month,'2003-02-01','2003-05-01');
    +----------------------------------------------------+
    | timestampdiff('month', '2003-02-01', '2003-05-01') |
    +----------------------------------------------------+
    |                                                  3 |                    
    select timestampdiff(year,datetime '2003-02-01 10:12:13',datetime '2001-05-01 10:12:13')as result;
    +--------+
    | result |
    +--------+
    |     -1 |                    

TO_DAYS

TO_DAYS(date)
  • Description: This function returns the number of days since year 0 based on the date specified by date.
  • Parameter types:
    to_days(date)
    to_days(time)
    to_days(varchar)
    to_days(timestamp)
    to_days(datetime)
  • Return value type: BIGINT.
  • Example:
    select to_days(date '2018-12-12');
    +----------------------------+
    | to_days(DATE '2018-12-12') |
    +----------------------------+
    |                     737405 |
    select to_days(time '12:12:12');
    +--------------------------+
    | to_days(TIME '12:12:12') |
    +--------------------------+
    |                   737572 |
    select to_days(now());
    +----------------+
    | to_days(now()) |
    +----------------+
    |         737572 |

    The preceding query is equivalent to to_days(curdate()).

    select to_days(curdate());
    +--------------------+
    | to_days(curdate()) |
    +--------------------+
    |             737573 |
    select to_days(datetime '2019-09-08 12:12:12');
    +-----------------------------------------+
    | to_days(DATETIME '2019-09-08 12:12:12') |
    +-----------------------------------------+
    |                                  737675 |
    select to_days('2019-09-08 12:12:12');
    +--------------------------------+
    | to_days('2019-09-08 12:12:12') |
    +--------------------------------+
    |                         737675 |
    select to_days(timestamp '2019-09-08 12:12:12');
    +------------------------------------------+
    | to_days(TIMESTAMP '2019-09-08 12:12:12') |
    +------------------------------------------+
    |                                   737675 |

TO_SECONDS

TO_SECONDS(expr)
  • Description: This function returns the number of seconds that have elapsed since year 0 based on the time specified by expr.
  • Parameter types:
    to_seconds(date)
    to_seconds(datetime)
    to_seconds(timestamp)
    to_seconds(varchar)
    to_seconds(time)                    
  • Return value type: BIGINT.
  • Example:
    select to_seconds(date '2019-09-08');
    +-------------------------------+
    | to_seconds(DATE '2019-09-08') |
    +-------------------------------+
    |                   63735120000 |
    select to_seconds(datetime '2019-09-08 09:09:00');
    +--------------------------------------------+
    | to_seconds(DATETIME '2019-09-08 09:09:00') |
    +--------------------------------------------+
    |                                63735152940 |
    select to_seconds(timestamp '2019-09-08 09:09:00');
    +---------------------------------------------+
    | to_seconds(TIMESTAMP '2019-09-08 09:09:00') |
    +---------------------------------------------+
    |                                 63735152940 |

    If you execute the following SQL statement, the system adds curdate() to '09:09:00'.

    select to_seconds(time '09:09:00');
    +-----------------------------+
    | to_seconds(TIME '09:09:00') |
    +-----------------------------+
    |                 63726253740 |                   
    select to_seconds('2019-09-08');
    +--------------------------+
    | to_seconds('2019-09-08') |
    +--------------------------+
    |              63735120000 |

UNIX_TIMESTAMP

UNIX_TIMESTAMP([date])
  • Description: UNIX_TIMESTAMP() returns the timestamp for the current time. The timestamp follows the UNIX time format. It is the number of seconds that have elapsed since '1970-01-01 00:00:00' UTC. UNIX_TIMESTAMP(date) returns the timestamp for a date. It is the number of seconds that have elapsed since '1970-01-01 00:00:00' UTC.
  • Parameter types:
    unix_timestamp()
    unix_timestamp(varchar)
    unix_timestamp(timestamp)
    unix_timestamp(date)
    unix_timestamp(datetime)
  • Return value type: BIGINT.
  • Example:
    select unix_timestamp();
    +------------------+
    | unix_timestamp() |
    +------------------+
    |       1558935850 |
    select unix_timestamp(timestamp '2019-09-08 12:12:12');
    +-------------------------------------------------+
    | unix_timestamp(TIMESTAMP '2019-09-08 12:12:12') |
    +-------------------------------------------------+
    |                                      1567915932 |
    select unix_timestamp(date '2019-09-08');
    +-----------------------------------+
    | unix_timestamp(DATE '2019-09-08') |
    +-----------------------------------+
    |                        1567872000 |
    select unix_timestamp(datetime '2019-09-08 12:12:12');
    +------------------------------------------------+
    | unix_timestamp(DATETIME '2019-09-08 12:12:12') |
    +------------------------------------------------+
    |                                     1567915932 |
    select unix_timestamp('2019-09-08 12:12:12');
    +---------------------------------------+
    | unix_timestamp('2019-09-08 12:12:12') |
    +---------------------------------------+
    |                            1567915932 |

UTC_DATE

UTC_DATE()
  • Description: This function returns the UTC date.
  • Return value type: VARCHAR.
  • Example:
    select utc_date();
    +------------+
    | utc_date() |
    +------------+
    | 2019-05-27 |

UTC_TIME

UTC_TIME()
  • Description: This function returns the UTC time.
  • Return value type: VARCHAR.
  • Example:
    select utc_time();
    +------------+
    | utc_time() |
    +------------+
    | 05:53:19   |

UTC_TIMESTAMP

utc_timestamp()
  • Description: This function returns the UTC timestamp.
  • Return value type: VARCHAR.
  • Example:
    select utc_timestamp();
    +---------------------+
    | utc_timestamp()     |
    +---------------------+
    | 2019-05-27 05:55:15 |                    

WEEK

WEEK(date[,mode])
  • Description: This function returns the week number for the date specified by date, which is the week to which date belongs in the year.
    • date specifies the date for which you want to obtain the week number.
    • mode is an optional parameter that specifies the logic for calculating the week number. It allows you to specify whether the week starts from Monday or Sunday. The return value ranges from 0 to 52 or from 0 to 53. The following table describes the formats that mode supports.
    0 Sunday 0 to 53
    1 Monday 0 to 53
    2 Sunday 1 to 53
    3 Monday 1 to 53
    4 Sunday 0 to 53
    5 Monday 0 to 53
    6 Sunday 1 to 53
    7 Monday 1 to 53
  • Parameter types:
    week(varchar)
    week(varchar, bigint)
    week(date)
    week(date, bigint)
    week(datetime)
    week(datetime, bigint)
    week(timestamp)
    week(timestamp, bigint)                   
  • Return value type: BIGINT.
  • Example:
    select week('2019-05-27');
    +--------------------+
    | week('2019-05-27') |
    +--------------------+
    |                 21 |
    select week('2008-02-20',1);
    +-----------------------+
    | week('2008-02-20', 1) |
    +-----------------------+
    |                     8 |
    select week(date '2008-02-20');
    +-------------------------+
    | week(DATE '2008-02-20') |
    +-------------------------+
    |                       7 |
    select week(date '2008-02-20',1);
    +----------------------------+
    | week(DATE '2008-02-20', 1) |
    +----------------------------+
    |                          8 |
    select week(datetime '2008-02-20 00:00:00',1);
    +-----------------------------------------+
    | week(DATETIME '2008-02-20 00:00:00', 1) |
    +-----------------------------------------+
    |                                       8 |
    select week(datetime '2008-02-20 00:00:00');
    +--------------------------------------+
    | week(DATETIME '2008-02-20 00:00:00') |
    +--------------------------------------+
    |                                    7 |
    select week(timestamp '2008-02-20 00:00:00');
    +---------------------------------------+
    | week(TIMESTAMP '2008-02-20 00:00:00') |
    +---------------------------------------+
    |                                     7 |
    select week(timestamp '2008-02-20 00:00:00',1);
    +------------------------------------------+
    | week(TIMESTAMP '2008-02-20 00:00:00', 1) |
    +------------------------------------------+
    |                                        8 |

WEEKDAY

WEEKDAY(date)
  • Description: This function returns the weekday for the date specified by date. The result is an integer indicating the weekday. The mapping is as follows: 0 = Monday, 1 = Tuesday, ... 6 = Sunday.
  • Parameter types:
    weekday(timestamp)
    weekday(datetime)
    weekday(date)
    weekday(varchar)
  • Return value type: BIGINT.
  • Example:
    select weekday(timestamp '2019-05-27 00:09:00');
    +------------------------------------------+
    | weekday(TIMESTAMP '2019-05-27 00:09:00') |
    +------------------------------------------+
    |                                        0 |
    select weekday(datetime '2019-05-27 00:09:00');
    +-----------------------------------------+
    | weekday(DATETIME '2019-05-27 00:09:00') |
    +-----------------------------------------+
    |                                       0 |
    select weekday(date '2019-05-27 00:09:00');
    +-------------------------------------+
    | weekday(DATE '2019-05-27 00:09:00') |
    +-------------------------------------+
    |                                   0 |
    select weekday('2019-05-27');
    +-----------------------+
    | weekday('2019-05-27') |
    +-----------------------+
    |                     0 |

WEEKOFYEAR

WEEKOFYEAR(date)
  • Description: This function returns the calendar week for the date specified by date. Valid values: [1, 53].
  • Parameter types:
    weekofyear(timestamp)
    weekofyear(datetime)
    weekofyear(date)
    weekofyear(varchar)
  • Return value type: BIGINT.
  • Example:
    select weekofyear(timestamp '2019-05-27 09:00:00');
    +---------------------------------------------+
    | weekofyear(TIMESTAMP '2019-05-27 09:00:00') |
    +---------------------------------------------+
    |                                          22 |
    select weekofyear(datetime '2019-05-27 09:00:00');
    +--------------------------------------------+
    | weekofyear(DATETIME '2019-05-27 09:00:00') |
    +--------------------------------------------+
    |                                         22 |
    select weekofyear(date '2019-05-27');
    +-------------------------------+
    | weekofyear(DATE '2019-05-27') |
    +-------------------------------+
    |                            22 |
    select weekofyear('2019-05-27');
    +--------------------------+
    | weekofyear('2019-05-27') |
    +--------------------------+
    |                       22 |

YEAR

YEAR(date)
  • Description: This function returns the year part of the date specified by date.
  • Parameter types:
    year(timestamp)
    year(datetime)
    year(date)
    year(time)
    year(varchar)
  • Return value type: BIGINT.
  • Example:
    select year(timestamp '2019-05-27 00:00:00');
    +---------------------------------------+
    | year(TIMESTAMP '2019-05-27 00:00:00') |
    +---------------------------------------+
    |                                  2019 |
    select year(datetime '2019-05-27 00:00:00');
    +--------------------------------------+
    | year(DATETIME '2019-05-27 00:00:00') |
    +--------------------------------------+
    |                                 2019 |
    select year(date '2019-05-27');
    +-------------------------+
    | year(DATE '2019-05-27') |
    +-------------------------+
    |                    2019 |

    If you execute the following SQL statement, the system adds curdate to '00:00:00' and returns the result as a string.

    select year(time '00:00:00');
    +-----------------------+
    | year(TIME '00:00:00') |
    +-----------------------+
    |                  2019 |
    select year('2019-05-27');
    +--------------------+
    | year('2019-05-27') |
    +--------------------+
    |               2019 |

YEARWEEK

YEARWEEK(date)
YEARWEEK(date,mode)
  • Description: This function the year and week of a date.

    Description: The year in the result may be different from the year in the date parameter for the first and the last week of the year.

    mode works in the same way as mode in the WEEK function. For the single-parameter syntax, the value of mode is 0.

  • Parameter types:
    yearweek(timestamp)
    yearweek(timestamp, bigint)
    yearweek(datetime)
    yearweek(datetime, bigint)
    yearweek(date, bigint) 
    yearweek(date) 
    yearweek(varchar)
    yearweek(varchar, bigint)
  • Return value type: BIGINT.
  • Example:
    select yearweek(timestamp '2019-05-27 00:00:00');
    +-------------------------------------------+
    | yearweek(TIMESTAMP '2019-05-27 00:00:00') |
    +-------------------------------------------+
    |                                    201921 |
    select yearweek(timestamp '2019-05-27 00:00:00',1);
    +----------------------------------------------+
    | yearweek(TIMESTAMP '2019-05-27 00:00:00', 1) |
    +----------------------------------------------+
    |                                       201922 |
    select yearweek(datetime '2019-05-27 00:00:00');
    +------------------------------------------+
    | yearweek(DATETIME '2019-05-27 00:00:00') |
    +------------------------------------------+
    |                                   201921 |
    select yearweek(datetime '2019-05-27 00:00:00',1);
    +---------------------------------------------+
    | yearweek(DATETIME '2019-05-27 00:00:00', 1) |
    +---------------------------------------------+
    |                                      201922 |
    select yearweek(date '2019-05-27',1);
    +--------------------------------+
    | yearweek(DATE '2019-05-27', 1) |
    +--------------------------------+
    |                         201922 |
    select yearweek(date '2019-05-27');
    +-----------------------------+
    | yearweek(DATE '2019-05-27') |
    +-----------------------------+
    |                      201921 |
    select yearweek('2019-05-27');
    +------------------------+
    | yearweek('2019-05-27') |
    +------------------------+
    |                 201921 |
    select yearweek('2019-05-27',1);
    +---------------------------+
    | yearweek('2019-05-27', 1) |
    +---------------------------+
    |                    201922 |