MaxCompute SQL provides common date functions. You can select an appropriate date function based on your business requirements to complete date calculations and conversions. This topic describes the syntax and parameters of the date functions that are supported by MaxCompute SQL. This topic also provides examples on how to use date functions to develop data.

Function Description
DATEADD Changes a date value based on the values of datepart and delta.
DATE_ADD Adds or subtracts a number of days to or from a date. The logic of the DATE_ADD function is opposite to that of the DATE_SUB function.
DATE_SUB Adds or subtracts a number of days to or from a date. The logic of the DATE_SUB function is opposite to that of the DATE_ADD function.
DATEDIFF Calculates the difference between two date values based on the time unit specified by datepart.
DATEPART Returns a specified component of a date value.
DATETRUNC Truncates a date value based on the time unit specified by datepart.
FROM_UNIXTIME Converts a UNIX timestamp of the BIGINT type into a date value of the DATETIME type.
GETDATE Returns the current system time as a date value.
ISDATE Determines whether a date string can be converted into a date value in a specified format.
LASTDAY Returns the last day of the month in which the specified date value falls.
TO_DATE Converts a string into a date value in a specified format.
TO_CHAR Converts a date value into a string in a specified format.
UNIX_TIMESTAMP Converts a date value into a UNIX timestamp that is an integer.
WEEKDAY Returns a number that represents the day of the week in which the provided date falls.
WEEKOFYEAR Returns a number that represents the week of the year in which the provided date falls.
ADD_MONTHS Returns a date value that is obtained after a number of months are added to a specified date.
CURRENT_TIMESTAMP Returns the current timestamp.
DAY Returns the day in which the specified date value falls.
DAYOFMONTH Returns the day component of a date value.
EXTRACT Returns a specified component of a timestamp.
FROM_UTC_TIMESTAMP Converts a UTC timestamp into a timestamp for a specified time zone.
HOUR Returns the hour component of a date value.
LAST_DAY Returns the last day of the month in which the specified date value falls.
MINUTE Returns the minute component of a date value.
MONTH Returns the month in which the specified date value falls.
MONTHS_BETWEEN Returns the number of months between specified date values.
NEXT_DAY Returns the date of the first weekday that is later than a specified date value.
QUARTER Returns the quarter in which the specified date value falls.
SECOND Returns the second component of a date value.
TO_MILLIS Converts a specified date into a UNIX timestamp in milliseconds.
YEAR Returns the year in which the specified date value falls.

DATEADD

  • Syntax
    datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>)
  • Description

    Changes a date value based on datepart and delta that you specified. To change the current time to a date value based on a specified delta, you can use this function with the GETDATE function.

  • Parameters
    • date: required. A date value of the DATE, DATETIME, or TIMESTAMP type.

      A value of the STRING type is implicitly converted into a value of the DATETIME type before calculation if the value format conforms to the DATETIME format yyyy-mm-dd hh:mi:ss, such as 2021-08-28 00:00:00, and the MaxCompute V1.0 data type edition is used.

    • delta: required. The interval that you want to add to or subtract from the specified component of a date value. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, the date value is incremented. Otherwise, the date value is decremented.

      If the input value is of the STRING or DOUBLE type, the value is implicitly converted into a value of the BIGINT type before calculation.

      Note
      • If you add or subtract the interval specified by delta at a date component, a carry or return at more significant date components may occur. The year, month, hour, minute, and second components are computed by using different numeral systems. The year component uses the base-10 numeral system. The month component uses the base-12 numeral system. The hour component uses the base-24 numeral system. The minute and second components use the base-60 numeral system.
      • If the DATEADD function adds the interval specified by delta to the month component of a date value and this operation does not cause an overflow on the day component, retain the value of the day component. Otherwise, set the value of the day component to the last day of the specified month.
    • datepart: required. The date part that you want to modify in the date value. The value is a constant of the STRING type. If the format of the input value is invalid or the input value is not a constant of the STRING type, an error is returned.

      The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates that the DATEADD function adds an interval to the year component of the date value. The value mm indicates that the DATEADD function adds an interval to the month component of the date value. The value dd indicates that the DATEADD function adds an interval to the day component of the date value. For more information about the rules for data type conversion, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, and -hour.

  • Return value
    A value of the DATETIME type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
    • If the value of date is null, an error is returned.
    • If the value of delta or datepart is null, null is returned.
  • Examples
    • Example 1: common use of DATEADD
      -- The return value is 2005-03-01 00:00:00. After one day is added, the result is beyond the last day of February. The actual date value is the first day of March. 
      select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd');
      -- The return value is 2005-02-27 00:00:00. One day is subtracted. 
      select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
      -- The return value is 2006-10-28 00:00:00. After 20 months are added, the month overflows, and the year value increases by 1. 
      select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
      -- The return value is 2005-03-28 00:00:00. 
      select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
      -- The return value is 2005-02-28 00:00:00. February in 2005 has only 28 days. Therefore, the last day of February is returned. 
      select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
      -- The return value is 2005-02-28 00:00:00. 
      select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
      -- The return value is 2005-03-18. 
      select dateadd(date '2005-02-18', 1, 'mm');
      -- The return value is 2005-03-18 00:00:00.0. 
      set odps.sql.type.system.odps2=true;
      select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm');
      -- If the current time is 2020-11-17 16:31:44, the return value is 2020-11-16 16:31:44. 
      select dateadd(getdate(),-1,'dd');
      -- The return value is null. 
      select dateadd(date '2005-02-18', 1, null);
    • Example 2: use of DATEADD in which a value of the DATETIME type is expressed as a constant
      In MaxCompute SQL statements, a value of the DATETIME type cannot be directly expressed as a constant. The following statement uses an invalid expression of a value of the DATETIME type:
      select dateadd(2005-03-30 00:00:00, -1, 'mm');
      To describe a constant of the DATETIME type, use a valid expression of a value of the DATETIME type in the following statement:
      -- Explicitly convert a constant of the STRING type into the DATETIME type. The return value is 2005-02-28 00:00:00. 
      select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');
    • Example 3: The input value is of the STRING type.
      -- The input value is of the STRING type but does not conform to the DATETIME format. As a result, an error is returned. 
      select dateadd('2021-08-27',1,'dd');
      -- The input value is of the STRING type and conforms to the DATETIME format, and the MaxCompute V1.0 data type edition is used. 2005-03-01 00:00:00 is returned. 
      set odps.sql.type.system.odps2=false;
      set odps.sql.hive.compatible=false; 
      select dateadd('2005-02-28 00:00:00', 1, 'dd');

DATE_ADD

  • Syntax
    date date_add(date|timestamp|string <startdate>, bigint <delta>)
  • Description

    Adds or subtracts an interval that is specified by delta to or from a date that is specified by startdate. To add or subtract an interval to or from the current time, you can use this function with the GETDATE function.

    The logic of this function is opposite to that of the DATE_SUB function.

  • Parameters
    • startdate: required. The start date. The value of the DATE, DATETIME, or STRING type is supported.

      If the input value is of the STRING type, the value is implicitly converted into a value of the DATE type before calculation. The value of the STRING type must be in the 'yyyy-mm-dd' format, such as '2019-12-27'.

    • delta: required. The interval that you want to add or subtract. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, an interval is added to the start date. If the value of delta is less than 0, an interval is subtracted from the start date. If the value of delta is 0, the date value remains unchanged.
  • Return value
    A value of the DATE type is returned. The return value varies based on the following rules:
    • If the value of startdate is not of the DATE, DATETIME, or STRING type, an error is returned.
    • If the value of startdate is null, an error is returned.
    • If the value of delta is null, null is returned.
  • Examples
    -- Enable the MaxCompute V2.0 data type edition. Commit this statement along with SQL statements. 
    set odps.sql.type.system.odps2=true;
    -- The return value is 2005-03-01. After one day is added, the result is beyond the last day of February. The actual value is the first day of March. 
    select date_add(datetime '2005-02-28 00:00:00', 1);
    -- The return value is 2005-02-27. One day is subtracted. 
    select date_add(date '2005-02-28', -1);
    -- The return value is 2005-03-20. 
    select date_add('2005-02-28 00:00:00', 20);
    -- If the current time is 2020-11-17 16:31:44, the return value is 2020-11-16. 
    select date_add(getdate(),-1);
    -- The return value is null. 
    select date_add('2005-02-28 00:00:00', null);

DATE_SUB

  • Syntax
    date date_sub(date|timestamp|string <startdate>, bigint <delta>)
  • Description

    Adds or subtracts an interval that is specified by delta to or from a date that is specified by startdate. To add or subtract an interval to or from the current time, you can use this function with the GETDATE function.

    The logic of this function is opposite to that of the DATE_ADD function.

  • Parameters
    • startdate: required. The start date. The value of the DATE, DATETIME, or STRING type is supported.

      If the input value is of the STRING type, the value is implicitly converted into a value of the DATE type before calculation. The value of the STRING type must be in the 'yyyy-mm-dd' format, such as '2019-12-27'.

    • delta: required. The interval that you want to add or subtract. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, an interval is subtracted from the start date. If the value of delta is less than 0, an interval is added to the start date. If the value of delta is 0, the date value remains unchanged.
  • Return value
    A value of the DATE type is returned. The return value varies based on the following rules:
    • If the value of startdate is not of the DATE, DATETIME, or STRING type, an error is returned.
    • If the value of startdate is null, an error is returned.
    • If the value of delta is null, null is returned.
  • Examples
    -- Enable the MaxCompute V2.0 data type edition. Commit this statement along with SQL statements. 
    set odps.sql.type.system.odps2=true;
    -- The return value is 2005-02-28. One day is subtracted. The actual value is the last day of February. 
    select date_sub(datetime '2005-03-01 00:00:00', 1);
    -- The return value is 2005-03-01. One day is added. 
    select date_sub(date '2005-02-28', -1);
    -- The return value is 2005-02-27. Two days are subtracted. 
    select date_sub('2005-03-01 00:00:00', 2);
    -- If the current time is 2021-09-10 16:31:44, the return value is 2020-09-09. 
    select date_sub(getdate(),1);
    -- The return value is null. 
    select date_sub('2005-03-01 00:00:00', null);

DATEDIFF

  • Syntax
    bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>)
  • Description

    Calculates the difference between date1 and date2. The difference is measured in the time unit specified by datepart.

  • Parameters
    • date1, date2: required. The minuend and subtrahend, which are of the DATE, DATETIME, or TIMESTAMP type. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation.
    • datepart: optional. The time unit, which is a constant of the STRING type.

      If you enable the MaxCompute V2.0 data type edition, you can leave datepart empty. Default value: day. For more information about the MaxCompute V2.0 data type edition, see Data type editions. The EDTF is also supported, such as -year, -month, -mon, -day, and -hour.

      Note This function omits the lower unit based on the time unit specified by datepart and then calculates the result.
  • Return value
    A value of the BIGINT type is returned. The return value varies based on the following rules:
    • If the value of date1 or date2 is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
    • If date1 is earlier than date2, a negative value is returned.
    • If the value of date1 or date2 is null, null is returned.
    • If the value of datepart is null, null is returned.
  • Examples
    -- The start time is 2005-12-31 23:59:59 and the end time is 2006-01-01 00:00:00.
        -- The return value is 1. 
        select datediff(end, start, 'dd'); 
        -- The return value is 1. 
        select datediff(end, start, 'mm');
        -- The return value is 1. 
        select datediff(end, start, 'yyyy');
        -- The return value is 1. 
        select datediff(end, start, 'hh');
        -- The return value is 1. 
        select datediff(end, start, 'mi');
        -- The return value is 1. 
        select datediff(end, start, 'ss');
    -- The return value is 1800. 
    select datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'ss');
    -- The return value is 30. 
    select datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi');
    -- The return value is 11. 
    select datediff(date '2013-05-21', date '2013-05-10', 'dd');
    -- The return value is 1800. 
    set odps.sql.type.system.odps2=true;
    select datediff(timestamp '2013-05-31 13:00:00', timestamp '2013-05-31 12:30:00', 'ss');
    -- The start time is 2018-06-04 19:33:23.234 and the end time is 2018-06-04 19:33:23.250. Date values with milliseconds do not adopt the standard DATETIME type and cannot be implicitly converted into the DATETIME type. In this case, an explicit conversion is required. The return value is 16. 
    select datediff(to_date('2018-06-04 19:33:23.250', 'yyyy-mm-dd hh:mi:ss.ff3'), to_date('2018-06-04 19:33:23.234', 'yyyy-mm-dd hh:mi:ss.ff3') , 'ff3');
    -- The return value is null. 
    select datediff(date '2013-05-21', date '2013-05-10', null);
    -- The return value is null. 
    select datediff(date '2013-05-21', null, 'dd');

DATEPART

  • Syntax
    bigint datepart(date|datetime|timestamp <date>, string <datepart>)
  • Description

    Returns a specified component of a date value.

  • Parameters
    • date: required. A value of the DATE, DATETIME, or TIMESTAMP type. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation.
    • datepart: required. A constant of the STRING type. This parameter supports EDTF.

      The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates that the DATEADD function adds an interval to the year component of the date value. The value mm indicates that the DATEADD function adds an interval to the month component of the date value. The value dd indicates that the DATEADD function adds an interval to the day component of the date value. For more information about the rules for data type conversion, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, and -hour.

  • Return value
    A value of the BIGINT type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
    • If the value of date is null, an error is returned.
    • If the value of datepart is null, null is returned.
  • Examples
    -- The return value is 2013. 
    select datepart(datetime'2013-06-08 01:10:00', 'yyyy'); 
    -- The return value is 6. 
    select datepart(datetime'2013-06-08 01:10:00', 'mm');
    -- The return value is 2013. 
    select datepart(date '2013-06-08', 'yyyy');
    -- The return value is 2013. 
    set odps.sql.type.system.odps2=true;
    select datepart(timestamp '2013-06-08 01:10:00', 'yyyy');
    -- The return value is null. 
    select datepart(date '2013-06-08', null);

DATETRUNC

  • Syntax
    datetime datetrunc (date|datetime|timestamp <date>, string <datepart>)
  • Description

    Truncates a date value based on the time unit specified by datepart.

  • Parameters
    • date: required. A value of the DATE, DATETIME, or TIMESTAMP type. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation.
    • datepart: required. A constant of the STRING type. This parameter supports EDTF.

      The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates that the DATEADD function adds an interval to the year component of the date value. The value mm indicates that the DATEADD function adds an interval to the month component of the date value. The value dd indicates that the DATEADD function adds an interval to the day component of the date value. For more information about the rules for data type conversion, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, and -hour.

  • Return value
    A value of the DATETIME type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
    • If the value of date is null, an error is returned.
    • If the value of datepart is null, null is returned.
  • Examples
    -- The return value is 2011-01-01 00:00:00. 
    select datetrunc(datetime'2011-12-07 16:28:46', 'yyyy');
    -- The return value is 2011-12-01 00:00:00. 
    select datetrunc(datetime'2011-12-07 16:28:46', 'month');
    -- The return value is 2011-12-07 00:00:00. 
    select datetrunc(datetime'2011-12-07 16:28:46', 'DD');
    -- The return value is 2011-01-01. 
    select datetrunc(date '2011-12-07', 'yyyy');
    -- The return value is 2011-01-01 00:00:00.0. 
    set odps.sql.type.system.odps2=true;
    select datetrunc(timestamp '2011-12-07 16:28:46', 'yyyy');
    -- The return value is null. 
    select datetrunc(date '2011-12-07', null);

FROM_UNIXTIME

  • Syntax
    datetime from_unixtime(bigint <unixtime>)
  • Description

    Converts unixtime of the BIGINT type into a date value of the DATETIME type.

  • Parameters

    unixtime: required. A date value of the BIGINT type in the UNIX format. The value of this parameter is accurate to seconds. If the input value is of the STRING, DOUBLE, or DECIMAL type, the value is implicitly converted into a value of the BIGINT type before calculation.

  • Return value
    A value of the DATETIME type is returned. If the value of unixtime is null, null is returned.
    Note In Hive-compatible mode where set odps.sql.hive.compatible=true; is run, if the input value is of the STRING type, a date value of the STRING type is returned.
  • Examples
    -- The return value is 1973-11-30 05:33:09. 
    select from_unixtime(123456789);
    -- The return value is null. 
    select from_unixtime(null);

GETDATE

  • Syntax
    datetime getdate()
  • Description

    Returns the current system time as a date value. MaxCompute uses UTC+8 as the standard time zone.

  • Return value

    The current date and time are returned, which are of the DATETIME type.

    Note In MaxCompute SQL, getdate always returns a fixed value. The return value is an arbitrary time during the execution of the MaxCompute SQL task. The time is accurate to seconds. If you enable the MaxCompute V2.0 data type edition, the time is accurate to milliseconds.

ISDATE

  • Syntax
    boolean isdate(string <date>, string <format>)
  • Description

    Determines whether a date string can be converted into a date value in a specified format. If the date string can be converted into a date value in the specified format, True is returned. Otherwise, False is returned.

  • Parameters
    • date: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into a value of the STRING type before calculation.
    • format: required. A constant of the STRING type. This parameter does not support EDTF. If redundant format strings exist in format, this function converts the date string that corresponds to the first format string into a date value. The rest strings are considered delimiters. For example, isdate("1234-yyyy", "yyyy-yyyy") returns True.
  • Return value

    A value of the BOOLEAN type is returned. If the value of date or format is null, null is returned.

LASTDAY

  • Syntax
    datetime lastday(datetime <date>)
  • Description

    Returns the last day of the month in which the specified date value falls. The value is accurate to days. The hour, minute, and second components are expressed as 00:00:00.

  • Parameters

    date: a value of the DATETIME type. The date value is in the yyyy-mm-dd hh:mi:ss format. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation.

  • Return value
    A value of the DATETIME type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME or STRING type or the format does not meet the requirements, an error is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 2013-06-30 00:00:00. 
    select lastday (datetime '2013-06-08 01:10:00');
    -- The return value is null. 
    select lastday (null);

TO_DATE

  • Syntax
    datetime to_date(string <date>, string <format>)
  • Description

    Converts a string into a date value in a specified format.

  • Parameters
    • date: required. A date value of the STRING type. This parameter specifies the date string that you want to convert. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into a value of the STRING type before calculation.
    • format: required. A constant of the STRING type. This parameter indicates a date format. format does not support EDTF. Other characters are omitted as invalid characters during parsing.

      The value of format must contain yyyy. Otherwise, null is returned. If redundant format strings exist in format, this function converts the date string that corresponds to the first format string into a date value. The rest strings are considered delimiters. For example, to_date("1234-2234", "yyyy-yyyy") returns 1234-01-01 00:00:00.

      The format consists of different date components. yyyy indicates a 4-digit year. mm indicates a 2-digit month. dd indicates a 2-digit day. hh indicates an hour based on the base-24 numeral system. mi indicates a 2-digit minute. ss indicates a 2-digit second. ff3 indicates a 3-digit millisecond.

  • Return value

    A value of the DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss format. If the value of date or format is null, null is returned.

  • Examples
    -- The return value is 2010-12-03 00:00:00. 
    select to_date('Alibaba 2010-12*03', 'Alibaba yyyy-mm*dd')
    -- The return value is 2008-07-18 00:00:00. 
    select to_date('20080718', 'yyyymmdd');
    -- The return value is 2008-07-18 00:00:00. 
    select to_date('200807182030','yyyymmddhhmi');
    -- '2008718' cannot be converted into a standard date value, and an error is returned. The value must be written as '20080718'. 
    select to_date('2008718', 'yyyymmdd');
    'Alibaba 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be written as 'Alibaba 2010-12*03'. 
    select to_date('Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd')
    '2010-24-01' cannot be converted into a standard date value, and an error is returned. The value must be written as '2010-01-24'. 
    select to_date('2010-24-01', 'yyyy');
    -- The return value is 2018-10-30 15:13:12.
    select to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');
    -- The return value is null. 
    select to_date(null,'yyyymmdd hh-mi-ss.ff3');
    -- The return value is null. 
    select to_date('20181030 15-13-12.345',null);

TO_CHAR

  • Syntax
    string to_char(datetime <date>, string <format>)
  • Description

    Converts a date value of the DATETIME type into a string in a specified format.

  • Parameters
    • date: required. A date value of the DATETIME type. The date value is in the yyyy-mm-dd hh:mi:ss format. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation.
    • format: required. A constant of the STRING type. In the format parameter, the date format part is replaced by the related data and the other characters remain unchanged in the output.
  • Return value
    A value of the STRING type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME or STRING type, null is returned.
    • If the value of date is null, an error is returned.
    • If the value of format is null, null is returned.
  • Examples
    -- The return value is 'Alibaba Cloud Financial Services 2010-12*03'. If the MaxCompute client runs in Windows, Chinese characters may not be properly displayed in the returned result. 
    select to_char(datetime'2010-12-03 00:00:00', 'Alibaba Cloud Financial Services yyyy-mm*dd');
    -- The return value is 20080718. 
    select to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd');
    -- 'Alibaba 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be written as 'Alibaba 2010-12*03'. 
    select to_char(datetime'Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd');
    -- '2010-24-01' cannot be converted into a standard date value, and an error is returned. The value must be written as '2010-01-24'. 
    select to_char(datetime'2010-24-01', 'yyyy');
    -- '2008718' cannot be converted into a standard date value, and an error is returned. The value must be written as '20080718'. 
    select to_char(datetime'2008718', 'yyyymmdd');
    -- The return value is null. 
    select to_char(datetime'2010-12-03 00:00:00', null);

UNIX_TIMESTAMP

  • Syntax
    bigint unix_timestamp(datetime|date|timestamp|string <date>)
  • Description

    Converts a date value into a UNIX timestamp that is an integer.

  • Parameters

    date: required. A value of the DATETIME, DATE, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation. If you enable the MaxCompute V2.0 data type edition, the implicit conversion fails. In this case, you must use the CAST function to convert the data type, such as unix_timestamp(cast(... as datetime)).

  • Return value
    A UNIX timestamp of the BIGINT type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME, DATE, TIMESTAMP, or STRING type or the format does not meet the requirements, an error is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 1237518660. 
    select unix_timestamp(datetime'2009-03-20 11:11:00'); 
    -- The return value is null. 
    select unix_timestamp(null);

WEEKDAY

  • Syntax
    bigint weekday (datetime <date>)
  • Description

    Returns a number that represents the day of the week in which the provided date falls.

  • Parameters

    date: required. A value of the DATETIME type. The format is yyyy-mm-dd hh:mi:ss.

  • Return value
    A value of the BIGINT type is returned. The return value varies based on the following rules:
    • Monday is considered the first day of the week and its return value is 0. Days of the week are numbered in ascending order starting from 0. The return value of Sunday is 6.
    • If the value of date is not of the DATETIME type or the format does not meet the requirements, an error is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 4. 
    select weekday (datetime '2009-03-20 11:11:00');
    -- The return value is null. 
    select weekday (null);

WEEKOFYEAR

  • Syntax
    bigint weekofyear (datetime <date>)
  • Description
    Returns a number that represents the week of the year in which the provided date falls. Monday is considered the first day of the week.
    Note To determine whether a week belongs to the current year or to the next year, find the year in which more than four days of the week fall. If the week belongs to the current year, it is considered the last week of the year. If the week belongs to the next year, it is considered the first week of the next year.
  • Parameters

    date: required. A value of the DATETIME type. The format is yyyy-mm-dd hh:mi:ss.

  • Return value
    A value of the BIGINT type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME type or the format does not meet the requirements, null is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 1. -- 20141229 is in year 2014, but most days of the week fall in year 2015. Therefore, the return value 1 indicates the first week of year 2015. 
    select weekofyear(to_date("20141229", "yyyymmdd"));  
    -- The return value is 1.     
    select weekofyear(to_date("20141231", "yyyymmdd")); 
    -- The return value is 53.  
    select weekofyear(to_date("20151229", "yyyymmdd"));
    -- The return value is null. 
    select weekofyear('20141231');
    -- The return value is null. 
    select weekofyear(null);

Additional functions of MaxCompute V2.0

MaxCompute V2.0 provides additional functions. If the functions that you use involve new data types, execute the following SET statement to enable the MaxCompute V2.0 data type edition. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.
  • Session level: To use the MaxCompute V2.0 data type edition, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and execute them together.
  • Project level: The project owner can configure the MaxCompute V2.0 data type edition for the project based on the project requirements. The configuration takes effects after 10 to 15 minutes. Run the following command:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.

ADD_MONTHS

  • Syntax
    string add_months(date|timestamp|string <startdate>, int <num_months>)
  • Description

    Returns a date value that is obtained after the number of months specified by num_months is added to startdate. This function is an additional function of MaxCompute V2.0.

  • Parameters
    • startdate: required. A date value of the DATE, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.
    • num_months: required. A value of the INT type.
  • Return value
    A value of the STRING type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
    • If the value of startdate is not of the DATE, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
    • If the value of startdate is null, an error is returned.
    • If the value of num_months is null, null is returned.
  • Examples
    -- The return value is 2017-05-14. 
    select add_months('2017-02-14',3);
    -- The return value is 0017-05-14. 
    select add_months('17-2-14',3);
    -- The return value is 2017-05-14. 
    select add_months('2017-02-14 21:30:00',3);
    -- The return value is null. 
    select add_months('20170214',3);
    -- The return value is null. 
    select add_months('2017-02-14 21:30:00',null);

CURRENT_TIMESTAMP

  • Syntax
    timestamp current_timestamp()
  • Description

    Returns the current timestamp. The return value is not fixed. This function is an extension function of MaxCompute V2.0.

  • Return value

    A value of the TIMESTAMP type is returned.

  • Examples
    -- The return value is '2017-08-03 11:50:30.661'. 
    set odps.sql.type.system.odps2=true;
    select current_timestamp(); 

DAY

  • Syntax
    int day(datetime|timestamp|date|string <date>)
  • Description

    Returns the day in which the specified date value falls. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 1. 
    select day('2014-09-01');
    -- The return value is null. 
    select day('20140901');
    -- The return value is null. 
    select day(null);

DAYOFMONTH

  • Syntax
    int dayofmonth(datetime|timestamp|date|string <date>)
  • Description

    Returns the day component of a date value. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type is returned. The return value is null in the following scenarios:
    • The value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements.
    • The value of date is null.
  • Examples
    -- The return value is 1. 
    select dayofmonth('2014-09-01');
    -- The return value is null. 
    select dayofmonth('20140901');
    -- The return value is null. 
    select dayofmonth(null);

EXTRACT

  • Syntax
    int extract(<datepart> from <timestamp>)
  • Description

    Extracts the date component specified by datepart from the date specified by timestamp. This function is an extension function of MaxCompute V2.0.

  • Parameters
    • datepart: required. A value that can be set to YEAR, MONTH, DAY, HOUR, or MINUTE.
    • timestamp: required. A value of the TIMESTAMP type.
  • Return value
    A value of the INT type. The return value varies based on the following rules:
    • If the value of datepart is not YEAR, MONTH, DAY, HOUR, or MINUTE, an error is returned.
    • If the value of datepart is null, an error is returned.
    • If the value of timestamp is null, null is returned.
  • Examples
    set odps.sql.type.system.odps2=true;
    select  extract(year from '2019-05-01 11:21:00') year
             ,extract(month from '2019-05-01 11:21:00') month
             ,extract(day from '2019-05-01 11:21:00') day
             ,extract(hour from '2019-05-01 11:21:00') hour
             ,extract(minute from '2019-05-01 11:21:00') minute;
    -- Return result: 
    +------+-------+------+------+--------+
    | year | month | day  | hour | minute |
    +------+-------+------+------+--------+
    | 2019 | 5     | 1    | 11   | 21     |
    +------+-------+------+------+--------+
    -- The return value is null. 
    select  extract(year from null);

FROM_UTC_TIMESTAMP

  • Syntax
    timestamp from_utc_timestamp({any primitive type}*, string <timezone>)
  • Description

    Converts a UTC timestamp into a timestamp in a specified time zone. This function is an extension function of MaxCompute V2.0.

  • Parameters
    • {any primitive type}*: required. A timestamp of the TIMESTAMP, DATETIME, TINYINT, SMALLINT, INT, or BIGINT type. If the value is of the TINYINT, SMALLINT, INT, or BIGINT type, the unit is milliseconds.
    • timezone: required. The new time zone.
      Note You can search for the time zone list by using a search engine.
  • Return value
    A value of the TIMESTAMP type is returned. The return value varies based on the following rules:
    • If the value of {any primitive type}* is not of the TIMESTAMP, DATETIME, TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
    • If the value of {any primitive type}* is null, an error is returned.
    • If the value of timezone is null, null is returned.
  • Examples
    -- The unit of the input value is milliseconds and the return value is 2017-08-01 04:24:00.0. 
    select from_utc_timestamp(1501557840000, 'PST'); 
    -- The return value is 1970-01-30 08:00:00.0. 
    select from_utc_timestamp('1970-01-30 16:00:00','PST'); 
    -- The return value is 1970-01-29 16:00:00.0. 
    select from_utc_timestamp('1970-01-30','PST'); 
    -- The return value is 2011-12-25 17:00:00:00.123. 
    set odps.sql.type.system.odps2=true;
    select from_utc_timestamp(timestamp '2011-12-25 09:00:00.123456', 'Asia/Shanghai');
    -- The return value is 2011-12-25 01:55:00.0. 
    set odps.sql.type.system.odps2=true;
    select from_utc_timestamp(timestamp '2011-12-25 06:55:00', 'America/Toronto');
    -- The return value is null. 
    select from_utc_timestamp('1970-01-30',null);

HOUR

  • Syntax
    int hour(datetime|timestamp|string <date>)
  • Description

    Returns the hour component of a date value.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings. This function is an extension function of MaxCompute V2.0.

  • Return value
    A value of the INT type is returned. The return value is null in the following scenarios:
    • The value of date is not of the DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements.
    • The value of date is null.
  • Examples
    -- The return value is 12. 
    select hour('2014-09-01 12:00:00');
    -- The return value is 12. 
    select hour('12:00:00');
    -- The return value is null. 
    select hour('20140901120000');
    -- The return value is null. 
    select hour(null);

LAST_DAY

  • Syntax
    string last_day(date|timestamp|string <date>)
  • Description

    Returns the last day of the month in which the specified date value falls. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATE, TIMESTAMP, or STRING type. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the STRING type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
    • If the value of date is not of the DATE, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
    • If the value of date is null, an error is returned.
  • Examples
    -- The return value is 2017-03-31. 
    select last_day('2017-03-04');
    -- The return value is 2017-07-31. 
    select last_day('2017-07-04 11:40:00');
    -- The return value is null. 
    select last_day('20170304');

MINUTE

  • Syntax
    int minute(datetime|timestamp|string <date>)
  • Description

    Returns the minute component of a date value. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 30. 
    select minute('2014-09-01 12:30:00'); 
    -- The return value is 30. 
    select minute('12:30:00');
    -- The return value is null. 
    select minute('20140901120000');
    -- The return value is null. 
    select minute(null);

MONTH

  • Syntax
    int month(datetime|timestamp|date|string <date>)
  • Description

    Returns the month in which the specified date value falls. This function is an additional function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type is returned. The return value is null in the following scenarios:
    • The value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements.
    • The value of date is null.
  • Examples
    -- The return value is 9. 
    select month('2014-09-01');
    -- The return value is null. 
    select month('20140901');
    -- The return value is null. 
    select month(null);

MONTHS_BETWEEN

  • Syntax
    double months_between(datetime|timestamp|date|string <date1>, datetime|timestamp|date|string <date2>)
  • Description

    Returns the number of months between date1 and date2. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date1, date2: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd hh:mi:ss or yyyy-mm-dd format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the DOUBLE type is returned. The return value varies based on the following rules:
    • If date1 is later than date2, a positive value is returned. If date2 is later than date1, a negative value is returned.
    • If date1 and date2 correspond to the last days of two months, the return value is an integer that represents the number of months. Otherwise, the return value is computed by using the following formula: (date1 - date2)/31.
    • If the value of date1 or date2 is null, null is returned.
  • Examples
    -- The return value is 3.9495967741935485. 
    select months_between('1997-02-28 10:30:00', '1996-10-30');
    -- The return value is -3.9495967741935485. 
    select months_between('1996-10-30','1997-02-28 10:30:00' );
    -- The return value is -3.0. 
    select months_between('1996-09-30','1996-12-31');
    -- The return value is null. 
    select months_between('1996-09-30',null);

NEXT_DAY

  • Syntax
    string next_day(timestamp|date|string <startdate>, string <week>)
  • Description

    Returns the date of the first day that is later than startdate and matches the week value. The date of the specified day in the next week is returned. This function is an extension function of MaxCompute V2.0.

  • Parameters
    • startdate: required. A date value of the TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.
    • week: required. A value of the STRING type. The value of this parameter can be the first two or three letters of a weekday or the full name of a weekday, such as MO, TUE, or FRIDAY.
  • Return value
    A value of the STRING type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
    • If the value of date is not of the TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
    • If the value of date is null, an error is returned.
    • If the value of week is null, null is returned.
  • Examples
    -- The return value is 2017-08-08. 
    select next_day('2017-08-01','TU');
    -- The return value is 2017-08-08. 
    select next_day('2017-08-01 23:34:00','TU');
    -- The return value is null. 
    select next_day('20170801','TU');
    -- The return value is null. 
    select next_day('2017-08-01 23:34:00',null);

QUARTER

  • Syntax
    int quarter (datetime|timestamp|date|string <date>)
  • Description

    Returns the quarter in which the specified date value falls. Valid values: 1 to 4. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type is returned. The return value is null in the following scenarios:
    • The value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements.
    • The value of date is null.
  • Examples
    -- The return value is 4. 
    select quarter('1970-11-12 10:00:00');
    -- The return value is 4. 
    select quarter('1970-11-12');
    -- The return value is null. 
    select quarter(null);

SECOND

  • Syntax
    int second(datetime|timestamp|string <date>)
  • Description

    Returns the second component of a date value. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type is returned. The return value varies based on the following rules:
    • If the value of date is not of the DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
    • If the value of date is null, null is returned.
  • Examples
    -- The return value is 45. 
    select second('2014-09-01 12:30:45');
    -- The return value is 45. 
    select second('12:30:45');
    -- The return value is null. 
    select second('20140901123045');
    -- The return value is null. 
    select second(null);

TO_MILLIS

  • Syntax
    bigint to_millis(datetime|timestamp <date>);
  • Description

    Converts a date value into a UNIX timestamp in milliseconds. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME or TIMESTAMP type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format.

  • Return value
    A value of the BIGINT type is returned. An error is returned in the following scenarios:
    • The value of date is not of the DATETIME or TIMESTAMP type.
    • The value of date is null.
  • Examples
    -- The return value is 1617174900000. 
    select to_millis(datetime '2021-03-31 15:15:00');
    -- The return value is 1617174900000. 
    set odps.sql.type.system.odps2=true;
    select to_millis(timestamp '2021-03-31 15:15:00');

YEAR

  • Syntax
    int year(datetime|timestamp|date|string <date>)
  • Description

    Returns the year in which the specified date value falls. This function is an extension function of MaxCompute V2.0.

  • Parameters

    date: required. A value of the DATETIME, TIMESTAMP, DATE, or STRING type. The date value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.

  • Return value
    A value of the INT type. The return value is null in the following scenarios:
    • The value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements.
    • The value of date is null.
  • Examples
    -- The return value is 1970. 
    select year('1970-01-01 12:30:00');
    -- The return value is 1970. 
    select year('1970-01-01');
    -- The return value is 70. 
    select year('70-01-01');
    -- The return value is null. 
    select year('1970/03/09'); 
    -- The return value is null. 
    select year(null);