This topic describes the date functions provided by MaxCompute, which can be used in SQL statements.

DATEADD

  • Syntax
    datetime dateadd(datetime date, bigint delta, string datepart)
  • Description

    This function changes date based on datepart and delta.

  • Parameters
    • date: a date value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.
    • delta: a value of the bigint type, which indicates the interval to add to the specified part of the date value. If the input value is of the string or double type, it is implicitly converted into a value of the bigint type before calculation. If the input value is of another data type, an exception is thrown. If the value of delta is greater than 0, the date value is incremented. A negative integer subtracts the interval from the date value.
    • datepart: a constant of the string type. The value of this parameter is set in compliance with the rules of data type conversion between string and datetime values. That is, the value yyyy indicates that the DATEADD function adds an interval to the year part of the date value, whereas the value mm indicates that the DATEADD function adds an interval to the month part of the date value. For more information about the rules of data type conversion, see Type conversions. Extended date formats are also supported, such as -year, -month, -mon, -day, and -hour. An exception is thrown if the value is in an invalid format or is not a constant of the string type.
    Note
    • Adding or subtracting an interval specified by delta at a date part may result in a carry or return at more significant date parts. The year, month, hour, minute, and second parts are computed according to the base-10, base-12, base-24, and base-60 numeral systems, respectively.
    • Assume that the DATEADD function adds an interval specified by delta to the month part of a date value. If this operation does not cause an overflow of day, keep day unchanged. Otherwise, set day to the last day of the target month.
    • The value of datepart is set in compliance with the rules of data type conversion between string and datetime values. That is, the value yyyy indicates that the DATEADD function adds an interval to the year part of the date value, whereas the value mm indicates that the DATEADD function adds an interval to the month part of the date value. The rules also apply to other built-in functions related to DATETIME. Unless otherwise specified, the datepart parameter of all built-in functions related to DATETIME supports extended date formats, such as -year, -month, -mon, -day, and -hour.
  • Return value

    Returns a value of the datetime type. If any input parameter is NULL, NULL is returned.

  • Examples
    select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd') ;
    -- Return 2005-03-01 00:00:00. After one day is added, the result is beyond the last day of February. The actual value is the first day of March.
    select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
    -- Return 2005-02-27 00:00:00. One day is subtracted.
    select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
    -- Return 2006-10-28 00:00:00. After 20 months are added, the month overflows, and the year increases by 1.
    select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
    -- Return 2005-03-28 00:00:00.
    select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
    -- Return 2005-02-28 00:00:00. There are only 28 days in February, 2005. Therefore, the last day of February is returned.
    select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
    -- Return 2005-02-28 00:00:00.
In MaxCompute SQL statements, a value of the datetime type cannot be directly expressed as a constant. The following statement uses an incorrect expression of the datetime type:
select dateadd(2005-03-30 00:00:00, -1, 'mm') from tbl1;
To use a constant of the datetime type, refer to the following expression:
select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm') from tbl1;
-- Explicitly convert the constant of the string type to the datetime type.

DATE_ADD

  • Syntax
    date date_add(date/timestamp/string startdate, bigint delta)
  • Description

    This function increases or decreases the value of startdate based on the delta value.

  • Parameters
    • startdate: a date value of the date, datetime, or string type. An exception is thrown if it is of another type.

      The input value is implicitly converted into a value of the date type before calculation if it is of the string type and the value of the string type is in the format of 'yyyy-mm-dd', for example, '2019-12-27'.

    • delta: a value of the bigint type, which indicates the interval to add to the specified part of the date value. An exception is thrown if it is of another type. If the value of delta is greater than 0, the date value is incremented. A negative integer subtracts the interval from the date value.
  • Return value

    Returns a value of the date type.

  • Examples
    set odps.sql.type.system.odps2=true;
    -- Enable the new data types introduced in MaxCompute V2.0. You need to commit this statement along with SQL statements.
    
    select date_add(datetime '2005-02-28 00:00:00', 1);
    -- Return 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(date '2005-02-28', -1);
    -- Return 2005-02-27. One day is subtracted.
    
    select date_add( '2005-02-28 00:00:00', 20);
    -- Return 2005-03-20.

DATEDIFF

  • Syntax
    bigint datediff(datetime date1, datetime date2, string datepart)
  • Description

    This function calculates the difference between date1 and date2 for the specified datepart.

  • Parameters
    • datet1 and date2: values of the datetime type, which indicate the value to be subtracted and the value from which the subtraction will take place, respectively. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.
    • datepart: a constant of the string type. It supports extended date formats. An exception is thrown if the value of datepart is in an invalid format or is not a constant of the string type.
    Note This function omits the lower unit based on the unit specified by datepart and then calculates the result.

    If you have set odps.sql.type.system.odps2 to true, datepart can be left unspecified. By default, day is assumed.

  • Return value

    Returns a value of the bigint type. If any input parameter is NULL, NULL is returned. A negative value is returned if date1 is later than date2.

  • Examples
    Assume that the start time is 2005-12-31 23:59:59 and the end time is 2006-01-01 00:00:00.
        datediff(end, start, 'dd') = 1
        datediff(end, start, 'mm') = 1
        datediff(end, start, 'yyyy') = 1
        datediff(end, start, 'hh') = 1
        datediff(end, start, 'mi') = 1
        datediff(end, start, 'ss') = 1
        datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'ss') = 1800
        datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi') = 30
    Assume that 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 therefore cannot be implicitly converted into the datetime type. In this case, explicit conversion is required.
        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') = 16

DATEPART

  • Syntax
    bigint datepart(datetime date, string datepart)
  • Description

    This function extracts the value of the specified datepart in date.

  • Parameters
    • date: a value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.
    • datepart: a constant of the string type. It supports extended date formats. An exception is thrown if the value of datepart is in an invalid format or is not a constant of the string type.
  • Return value

    Returns a value of the bigint type. If any input parameter is NULL, NULL is returned.

  • Examples
    datepart(datetime'2013-06-08 01:10:00', 'yyyy')  =  2013
    datepart(datetime'2013-06-08 01:10:00', 'mm')  =  6

DATETRUNC

  • Syntax
    datetime datetrunc (datetime date, string datepart)
  • Description

    This function truncates a date value based on the specified datepart.

  • Parameters
    • date: a value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.
    • datepart: a constant of the string type. It supports extended date formats. An exception is thrown if the value of datepart is in an invalid format or is not a constant of the string type.
  • Return value

    Returns a value of the datetime type. If any input parameter is NULL, NULL is returned.

  • Examples
    datetrunc(datetime'2011-12-07 16:28:46', 'yyyy') = 2011-01-01 00:00:00
    datetrunc(datetime'2011-12-07 16:28:46', 'month') = 2011-12-01 00:00:00
    datetrunc(datetime'2011-12-07 16:28:46', 'DD') = 2011-12-07 00:00:00

GETDATE

  • Syntax
    datetime getdate()
  • Description

    This function returns the current system time. MaxCompute uses UTC+8 as the standard time.

  • Return value

    Returns the current date and time of the datetime type.

    Note In a MaxCompute SQL task that is executed in a distributed manner, 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. In MaxCompute V2.0, the time is accurate to milliseconds.

ISDATE

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

    This function 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: a date value of the string type. If the input value is of the bigint, double, decimal, or datetime type, it is implicitly converted into a value of the string type before calculation. If the input value is of another data type, an exception is thrown.
    • format: a constant of the string type. It does not support extended date formats. An exception is thrown if the value is in an invalid format or is not a constant of the string type. If redundant format strings exist in format, the function determines whether the date string corresponding to the first format string can be converted into a date value of the first format string. The rest strings are treated as delimiters. For example, isdate ("1234-yyyy", "yyyy-yyyy") returns TRUE.
  • Return value

    Returns a value of the Boolean type. If any input parameter is NULL, NULL is returned.

LASTDAY

  • Syntax
    datetime lastday(datetime date)
  • Description

    This function returns the last day of the current month to which the date belongs. The value is accurate to days. The hour, minute, and second parts are expressed as 00:00:00.

  • Parameters

    date: a value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.

  • Return value

    Returns a value of the datetime type. NULL is returned if the input parameter is NULL.

TO_DATE

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

    This function converts a date string in a specified format into a date value.

  • Parameters
    • date: a date value of the string type, which indicates the date string to be converted. If the input value is of the bigint, double, decimal, or datetime type, it is implicitly converted into a value of the string type before calculation. If the input value is of another data type or an empty string, an exception is thrown.
    • format: a constant of the string type, which indicates a date format. An exception is thrown if the value is not a constant or not of the string type. The value of format does not support extended date formats. Other characters are omitted because they are treated as invalid characters in parsing.

      The value of format must contain yyyy. Otherwise, an exception is thrown. If redundant format strings exist in format, the function converts the date string corresponding to the first format string into a date value. The rest strings are treated as delimiters. For example, to_date("1234-2234", "yyyy-yyyy") returns 1234-01-01 00:00:00.

      In the format, yyyy indicates a 4-digit year, mm indicates a 2-digit month, dd indicates a 2-digit day, hh indicates an hour in 24-hour display, mi indicates a 2-digit minute, ss indicates a 2-digit second, and ff3 indicates a 3-digit millisecond.

  • Return value

    Returns a value of the datetime type in the format of yyyy-mm-dd hh:mi:ss. If any input parameter is NULL, NULL is returned.

  • Examples
    to_date('Alibaba 2010-12*03', 'Alibaba yyyy-mm*dd') = 2010-12-03 00:00:00
    to_date('20080718', 'yyyymmdd') = 2008-07-18 00:00:00
    to_date('200807182030','yyyymmddhhmi') = 2008-07-18 20:30:00
    to_date('2008718', 'yyyymmdd') = null -- Invalid format, causing an exception.
    to_date('Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd') = null -- Invalid format, causing an exception.
    To_date ('2010-24-01', 'yyyy') = null -- Invalid format, causing an exception.
    to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3')=2018-10-30 15:13:12

TO_CHAR

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

    This function converts a date value of the datetime type into a string of the specified format.

  • Parameters
    • date: a date value of the datetime type, which indicates the date value to be converted. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.
    • format: a constant of the string type. An exception is thrown if the value is not a constant or not of the string type. In the format, the date format part is replaced with the corresponding data and other characters remain unchanged in the output.
  • Return value

    Returns a value of the string type. If any input parameter is NULL, NULL is returned.

  • Examples
    to_char(datetime'2010-12-03 00:00:00', 'Alibaba Finance yyyy-mm*dd') = 'Alibaba Finance 2010-12*03'
    to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd') = '20080718' 
    to_char(datetime'Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd') -- An exception will be thrown.
    to_char(datetime'2010-24-01', 'yyyy') -- An exception will be thrown.
    to_char(datetime'2008718', 'yyyymmdd') -- An exception will be thrown.

For more information about conversion from other types to the string type, seeTO_CHAR.

UNIX_TIMESTAMP

  • Syntax
    bigint unix_timestamp(datetime date)
  • Description

    This function converts a date value of the datetime type into a value of the integer type in the UNIX format.

  • Parameters

    date: a date value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown. If you have set odps.sql.type.system.odps2 to true, the implicit conversion fails. In this case, you must use the cast function for conversion, for example, unix_timestamp(cast(... as datetime)).

  • Return value

    Returns a value of the bigint type. The return value is a date value in the UNIX format. NULL is returned if date is NULL.

  • Examples
    select unix_timestamp(datetime'2009-03-20 11:11:00'); -- Return 1237518660.

FROM_UNIXTIME

  • Syntax
    datetime from_unixtime(bigint unixtime)
  • Description

    This function converts a date value of the bigint type in the UNIX format into a date value of the datetime type.

  • Parameters

    unixtime: a date value of the bigint type in the UNIX format. Its value is accurate to seconds. If the input value is of the string, double, or decimal type, it is implicitly converted into a value of the bigint type before calculation.

  • Return value
    Returns a value of the datetime type. NULL is returned if the input parameter is NULL.
    Note In the HIVE-compatible mode where set odps.sql.hive.compatible=true; has been run, if the input value is of the string type, a date value of the string type is returned.
  • Examples
    from_unixtime(123456789) = 1973-11-30 05:33:09

WEEKDAY

  • Syntax
    bigint weekday (datetime date)
  • Description

    This function returns the day of the week in which the specified date value falls.

  • Parameters

    date: a value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.

  • Return value

    Returns a value of the bigint type. NULL is returned if the input parameter is NULL. Monday is treated as the first day of a week and its return value is 0. Days are numbered in ascending order starting from 0. The return value of Sunday is 6.

WEEKOFYEAR

  • Syntax
    bigint weekofyear (datetime date)
  • Description
    This function returns the week of the year in which the specified date value falls. Monday is treated as the first day of a week.
    Note To determine whether a week belongs to a year or its next year, find the year in which more than four days of the week fall. If the week belongs to the year, it is treated as the last week of the year. If the week belongs to the next year, it is treated as the first week of the next year.
  • Parameters

    date: a date value of the datetime type. If the input value is of the string type, it is implicitly converted into a value of the datetime type before calculation. If the input value is of another data type, an exception is thrown.

  • Return value

    Returns a value of the bigint type. NULL is returned if the input parameter is NULL.

  • Examples
    select weekofyear(to_date("20141229", "yyyymmdd")) from dual;  
    -- Returned result:
    +------------+
    | _c0        |
    +------------+
    | 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("20141231", "yyyymmdd")) from dual; -- Return 1.  
    select weekofyear(to_date("20151229", "yyyymmdd")) from dual; -- Return 53.

Additional functions of MaxCompute V2.0

MaxCompute V2.0 provides additional date functions. If a function that you use in an SQL statement involves new data types, you must insert the following set statement before the SQL statement:
set odps.sql.type.system.odps2 = true; -- Enable new data types.
If you want to commit them at the same time, execute the following statements:
set odps.sql.type.system.odps2 = true;
select year('1970-01-01 12:30:00') ;-- Return 1970.

This section describes the new date functions in MaxCompute V2.0.

YEAR

  • Syntax
    INT year(string date)
  • Description

    This function returns the year in which the specified date value falls.

  • Parameters

    date: a date value of the string type. Its format must include yyyy-mm-dd while excluding redundant strings. Otherwise, NULL is returned.

  • Return value

    Returns a value of the int type.

  • Examples
    year('1970-01-01 12:30:00') = 1970
    year('1970-01-01') = 1970
    year('70-01-01') = 70
    year('1970-01-01') = 1970
    year('1970/03/09') = null
    year(null) -- An exception will be thrown.
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

QUARTER

  • Syntax
    INT quarter (datetime/timestamp/string date)
  • Description

    This function returns the quarter of the year in which the specified date value falls. The quarter is a number ranging from 1 to 4.

  • Parameters

    date: a date value of the datetime, timestamp, or string type. Its format must include yyyy-mm-dd. NULL is returned if the date value is of another data type.

  • Return value

    Returns a value of the int type. NULL is returned if the input parameter is NULL.

  • Examples
    quarter('1970-11-12 10:00:00') = 4
    quarter('1970-11-12') = 4
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

MONTH

  • Syntax
    INT month(string date)
  • Description

    This function returns the month in which the specified date value falls.

  • Parameters

    date: a date value of the string type. An exception is thrown if it is of another data type.

  • Return value

    Returns a value of the int type.

  • Examples
    month('2014-09-01') = 9
    month('20140901') = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

DAY

  • Syntax
    INT day(string date)
  • Description

    This function returns the day in which the specified date value falls.

  • Parameters

    date: a date value of the string type. Its format can be yyyy-mm-dd or yyyy-mm-dd hh:mi:ss. An exception is thrown if it is of another data type.

  • Return value

    Returns a value of the int type.

  • Examples
    day('2014-09-01') = 1
    day('20140901') = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

DAYOFMONTH

  • Syntax
    INT dayofmonth(date)
  • Description

    This function returns the day of the month in which the specified date value falls. For example, if the date is October 13 in 2017, 13 is returned after you run the command int dayofmonth(2017-10-13).

  • Parameters

    date: a date value of the string type. An exception is thrown if it is of another data type.

  • Return value

    Returns a value of the int type.

  • Examples
    dayofmonth('2014-09-01') = 1
    dayofmonth('20140901') = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

HOUR

  • Syntax
    INT hour(string date)
  • Description

    This function returns the hour in which the specified date value falls.

  • Parameters

    date: a date value of the string type. An exception is thrown if it is of another data type.

  • Return value

    Returns a value of the int type.

  • Examples
    hour('2014-09-01 12:00:00') = 12
    hour('12:00:00') = 12
    hour('20140901120000') = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

MINUTE

  • Syntax
    INT minute(string date)
  • Description

    This function returns the minute in which the specified date value falls.

  • Parameters

    date: a date value of the string type. An exception is thrown if it is of another data type.

  • Return value

    Returns a value of the int type.

  • Examples
    minute('2014-09-01 12:30:00') = 30
    minute('12:30:00') = 30
    minute('20140901120000') = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

SECOND

  • Syntax
    INT second(string date)
  • Description

    This function returns the second in which the specified date value falls.

  • Parameters

    date: a date value of the string type. An exception is thrown if it is of another data type.

  • Return value

    Returns a value of the int type.

  • Examples
    second('2014-09-01 12:30:45') = 45
    second('12:30:45') = 45
    second('20140901123045') = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

CURRENT_TIMESTAMP

  • Syntax
    timestamp current_timestamp()
  • Description

    This function returns the current timestamp. The return value is not fixed.

  • Return value

    Returns a value of the timestamp type.

  • Examples
    select current_timestamp() from dual; -- Return '2017-08-03 11:50:30.661'.
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

FROM_UTC_TIMESTAMP

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

    This function converts a UTC timestamp to a timestamp for a specified time zone.

  • Parameters
    • {any primitive type}*: a value of the timestamp, datetime, tinyint, smallint, int, or bigint type.
    • timezone: the destination time zone to convert the timestamp, such as PST.
    • This function supports only the Asia and Shanghai formats, and does not support the GMT+9 format.
  • Return value

    Returns a value of the timestamp type.

  • Examples
    SELECT from_utc_timestamp(1501557840,'PST') ; -- Return 1970-01-18 09:05:57.84.
    SELECT from_utc_timestamp('1970-01-30 16:00:00','PST') ; -- Return 1970-01-30 08:00:00.0.
    SELECT from_utc_timestamp('1970-01-30','PST') ; -- Return 1970-01-29 16:00:00.0.
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

ADD_MONTHS

  • Syntax
    string add_months(string startdate, int nummonths)
  • Description

    This function returns the date that is nummonths months later than startdate.

  • Parameters
    • startdate: a value of the string type. Its format must include yyyy-mm-dd. Otherwise, NULL is returned.
    • num_months: a value of the int type.
  • Return value

    Returns a date value of the string type in the format of yyyy-mm-dd.

  • Examples
    add_months('2017-02-14',3) = '2017-05-14'
    add_months('17-2-14',3) = '0017-05-14'
    add_months('2017-02-14 21:30:00',3) = '2017-05-14'
    add_months('20170214',3) = null
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

LAST_DAY

  • Syntax
    string last_day(string date)
  • Description

    This function returns the date of the last day of the month.

  • Parameters

    date: a date value of the string type in the format of yyyy-MM-dd HH:mi:ss or yyyy-MM-dd.

  • Return value

    Returns a date value of the string type in the format of yyyy-mm-dd.

  • Examples
    last_day('2017-03-04') = '2017-03-31'
    last_day('2017-07-04 11:40:00') = '2017-07-31'
    last_day('20170304') = null

NEXT_DAY

  • Syntax
    string next_day(string startdate, string week)
  • Description

    This function returns the date of the first day that is later than startdate and matches the week value. That is, the date of the specified day in the next week.

  • Parameters
    • startdate: a date value of the string type in the format of yyyy-MM-dd HH:mi:ss or yyyy-MM-dd.
    • week: a value of the string type, which can be the first two or three letters or full name of a day in a week, such as MO, TUE, or FRIDAY.
  • Return value

    Returns a date value of the string type in the format of yyyy-mm-dd.

  • Examples
    next_day('2017-08-01','TU') = '2017-08-08'
    next_day('2017-08-01 23:34:00','TU') = '2017-08-08'
    next_day('20170801','TU') = null

MONTHS_BETWEEN

  • Syntax
    double months_between(datetime/timestamp/string date1, datetime/timestamp/string date2)
  • Description

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

  • Parameters
    • date1: a date value of the datetime, timestamp, or string type in the format of yyyy-MM-dd HH:mi:ss or yyyy-MM-dd.
    • date2: a date value of the datetime, timestamp, or string type in the format of yyyy-MM-dd HH:mi:ss or yyyy-MM-dd.
  • Return value
    Returns a value of the double type.
    • A positive value is returned if date1 is later than date2. A negative value is returned if date2 is later than date1.
    • If both date1 and date2 correspond to the last days of two months, the return value is an integer representing the number of months. Otherwise, the return value is computed according to the formula (date1 - date2)/31.
  • Examples
    months_between('1997-02-28 10:30:00', '1996-10-30') = 3.9495967741935485
    months_between('1996-10-30','1997-02-28 10:30:00' ) = -3.9495967741935485
    months_between('1996-09-30','1996-12-31') = -3.0
Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
  • Project level: You can enable a new data type at the project level. The project owner can run the following command to configure a project:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

EXTRACT

  • Syntax
    INT EXTRACT(<datepart> from <timestamp>)
  • Description

    This function extracts a part from a date.

  • Parameters
    • datepart: a value that can be set to YEAR, MONTH, DAY, HOUR, or MINUTE.
    • timestamp: a value of the timestamp type.
    Note When a new data type, such as tinyint, smallint, int, float, varchar, timestamp, or binary, is used in a MaxCompute SQL statement, you must insert a set statement before the SQL statement to enable the new data type.
    • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and run it along with the SQL statement.
    • Project level: You can enable a new data type at the project level. The project owner can run the sql setproject odps.sql.type.system.odps2=true; command to configure the project as needed.

    For more information about setproject, see Other operations. For the precautions you must take when enabling data types at the project level, see Data types.

  • Return value

    Returns a value of the int type.

  • 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;
    
    -- Returned result:
    +------+-------+------+------+--------+
    | year | month | day  | hour | minute |
    +------+-------+------+------+--------+
    | 2019 | 5     | 1    | 11   | 21     |
    +------+-------+------+------+--------+