All Products
Search

This Product

    IoT Platform:Date and time functions

    Document Center

    IoT Platform:Date and time functions

    Last Updated:Nov 17, 2023

    The SQL analysis feature provides common date functions. You can call date functions to compute and convert dates based on your business requirements. This topic describes the syntax and parameters of date functions that are supported by the SQL analysis feature. This topic also provides examples on how to call date functions to develop data.

    Function

    Description

    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.

    DATE_ADD

    Adds or subtracts a number of days to or from a date value based on the interval specified by delta. The DATE_ADD function is the inverse of the DATE_SUB function.

    DATE_SUB

    Adds or subtracts a number of days to or from a date value based on the interval specified by delta. The DATE_SUB function is the inverse of the DATE_ADD function.

    DAY

    Returns the day of a date value.

    DAYOFMONTH

    Returns the day of a date value.

    DAYOFWEEK

    Returns the day of the week in which a date value falls.

    DAYOFYEAR

    Returns an integer that represents the sequential day of the year.

    EXTRACT

    Returns a specified component of a timestamp.

    FROM_UNIXTIME

    Converts a UNIX timestamp of the BIGINT type to a date value of the DATETIME type.

    GETDATE

    Returns the current system time.

    HOUR

    Returns the hour component of a date value.

    INTERVAL_DAY_TIME

    Converts a date value of the STRING type to a variable of the INTERVAL_DAY_TIME type.

    ISDATE

    Determines whether a date string can be converted to a date value in a specified format.

    LAST_DAY

    Returns the last day of the month in which a date value falls.

    LASTDAY

    Returns the last day of the month in which a date value falls.

    MINUTE

    Returns the minute component of a date value.

    MONTH

    Returns the month in which a 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 date value and matches the specified week.

    NOW

    Returns the current system date and time.

    QUARTER

    Returns the quarter in which a date value falls.

    SECOND

    Returns the second component of a date value.

    TO_CHAR

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

    TO_DATE

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

    UNIX_TIMESTAMP

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

    WEEKDAY

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

    WEEKOFYEAR

    Returns a number that represents the week of the year in which a date value falls.

    YEAR

    Returns the year in which a date value falls.

    ADD_MONTHS

    • Syntax

      string add_months(date|datetime|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 the value of startdate.

    • Parameters

      • startdate: required. A value of the DATE, DATETIME, TIMESTAMP, or STRING type. The value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss.ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot 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, DATETIME, TIMESTAMP, or STRING type, or is in an invalid format, 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.

    • Return value

      A value of the TIMESTAMP type is returned.

    • Examples 

      select current_timestamp();

    DATE_ADD

    • Syntax 

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

    • Description

      Adds or subtracts a number of days that is specified by delta to or from a date value that is specified by startdate. To add or subtract a number of days to or from the current time, you can call this function together with the GETDATE function.

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

    • Parameters

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

        If you specify a value of the STRING type, the value is implicitly converted to a value of the DATE type before calculation. The value must be in the 'yyyy-mm-dd' format. Example: '2019-12-27'.

      • delta: required. The number of days 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, a number of days is added to the start date. If the value of delta is less than 0, a number of days 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 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, 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

      • -- The return value is 2005-03-01. After one day is added, the result is beyond the last day of February. The first day of March is returned. 
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 a number of days that is specified by delta to or from a date value that is specified by startdate. To add or subtract a number of days to or from the current time, you can call this function together with the GETDATE function.

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

    • Parameters

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

        If you specify a value of the STRING type, the value is implicitly converted to a value of the DATE type before calculation. The value must be in the 'yyyy-mm-dd'. Example: '2019-12-27'.

      • delta: required. The number of days 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, a number of days are subtracted from the start date. If the value of delta is less than 0, a number of days are 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 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, 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

      • -- The return value is 2005-02-28. One day is subtracted. The last day of February is returned. 
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 2021-09-09. 
select date_sub(getdate(),1);

-- The return value is null. 
select date_sub('2005-03-01 00:00:00', null);

    DAY

    • Syntax 

      int day(datetime|timestamp|date|string <date>)

    • Description

      Returns the day in which a date value falls.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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 of a date value.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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 dayofmonth('2014-09-01');

-- The return value is null. 
select dayofmonth('20140901');

-- The return value is null. 
select dayofmonth(null);

    DAYOFWEEK

    • Syntax 

      int dayofweek(datetime|timestamp|date|string <date>)

    • Description

      Returns the day of the week in which a date value falls.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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.

      • The return value ranges from 1 to 7. The value 1 indicates Sunday and the value 2 indicates Monday. The same rule applies to other values.

    • Examples

      -- The return value is 5, which indicates Thursday. 
SELECT dayofweek('2009-07-30');

    DAYOFYEAR

    • Syntax 

      int dayofyear(datetime|timestamp|date|string <date>)

    • Description

      Returns an integer that represents the sequential day of the year.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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 100. 
SELECT dayofyear('2016-04-09');

    EXTRACT

    • Syntax 

      int extract(<datepart> from date|datetime|timestamp <date>)

    • Description

      Extracts the date component specified by datepart from a date value specified by date.

    • Parameters

      • datepart: required. A value that can be set to YEAR, MONTH, DAY, HOUR, or MINUTE.

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

    • Return values

      A value of the INT type is returned. 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 date is not of the DATE, DATETIME, TIMESTAMP, or STRING type or is null, null is returned.

    • Examples

      • 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;
-- The following result is returned: 
+------+-------+------+------+--------+
| year | month | day  | hour | minute |
+------+-------+------+------+--------+
| 2019 | 5     | 1    | 11   | 21     |
+------+-------+------+------+--------+
-- The return value is null. 
select  extract(year from null);

    FROM_UNIXTIME

    • Syntax 

      datetime from_unixtime(bigint <unixtime>)

    • Description

      Converts the value of unixtime, which is of the BIGINT type, to a date value of the DATETIME type.

    • Parameters

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

    • 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 unixtime is null, null is returned.

      Note

      In the Hive-compatible data type edition in which set odps.sql.hive.compatible=true; is run, a date value of the STRING type is returned if the input value is of the STRING type.

    • Examples

      • -- The return value is 1973-11-30 05:33:09. 
select from_unixtime(123456789);

    GETDATE

    • Syntax 

      datetime getdate()

    • Description

      Returns the current system time. UTC+8 is used by the SQL analysis feature as the standard time zone.

    • Return value

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

    HOUR

    • Syntax 

      int hour(datetime|timestamp|string <date>)

    • Description

      Returns the hour component of a date value.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd hh:mi:ss or yyyy-mm-dd hh:mi:ss.ff3 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 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);

    INTERVAL_DAY_TIME

    • Syntax 

      interval_day_time(string <date>)

    • Description

      Converts the value of date to a variable of the INTERVAL_DAY_TIME type.

    • Parameters

      date: required. A date value of the STRING type. The input value is in the dd hh:mi:ss.ff3 format and must include at least the dd hh:mm part. Otherwise, an error is returned.

    • Return value

      A value of the INTERVAL_DAY_TIME type is returned. If the input value is null, null is returned.

    • Examples

      -- The return value is 1 00:00:00.000000000. 
select interval_day_time("01 00:00:00");

-- The return value is 1 11:22:00.000000000. 
select interval_day_time("01 11:22");

-- The return value is 12 01:02:03.999000000. 
select interval_day_time("12 01:02:03.999");

    ISDATE

    • Syntax 

      boolean isdate(string <date>, string <format>)

    • Description

      Determines whether a date string can be converted to a date value in a specified format. If the date string can be converted to 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 to 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 to a date value. The other 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.

    • Examples

      • -- The return value is true. 
select isdate('2021-10-11','yyyy-mm-dd');

-- The return value is false. 
select isdate(1678952314,'yyyy-mm-dd');

    LAST_DAY

    • Syntax 

      string last_day(date|datetime|timestamp|string <date>)

    • Description

      Returns the last day of the month in which a date value falls.

    • Parameters

      date: required. A date value of the DATE, DATETIME, TIMESTAMP, or STRING type. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot 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, DATETIME, 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');

    LASTDAY

    • Syntax

      datetime lastday(datetime <date>)

    • Description

      Returns the last day of the month in which a date value falls. Only the day component is truncated. The hour, minute, and second components are expressed as 00:00:00.

    • Parameters

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

    • Return value

      A value of the DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss format. 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 2013-06-30 00:00:00. 
select lastday ('2013-06-08 01:10:00');

-- The return value is null. 
select lastday (null);

    MINUTE

    • Syntax 

      int minute(datetime|timestamp|string <date>)

    • Description

      Returns the minute component of a date value.

    • Parameters

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

    • 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 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 a date value falls.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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 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.

    • Parameters

      date1 and date2: required. Values of the DATETIME, TIMESTAMP, DATE, or STRING type. The input values are in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, yyyy-mm-dd hh:mi:ss.ff3 format. If the input values are of the STRING type, the values 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 the value of date1 is later than the value of date2, a positive value is returned. If the value of date2 is later than the value of date1, a negative value is returned.

      • If the values of 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 calculated 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|datetime|string <startdate>, string <week>)

    • Description

      Returns the date of the first day that is later than the value of startdate and matches the value of week. The date of the specified day in the next week is returned.

    • Parameters

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

      • week: required. A value of the STRING type, which can be the first two or three letters 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, DATETIME, 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);

    NOW

    • Syntax 

      datetime NOW()

    • Description

      A topic is added to describe the NOW function. The function is called to return the current system date and time.

    • Return value

      A value of the DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss.SSS format.

    • Examples

      • No format is specified. The millisecond part in the returned value may include 1, 2, or 3 bits.

        select now();

        Sample return value:

        +------+
| _c0  |
+------+
| 2023-06-13 10:53:24.967 |
+------+

      • The time format is specified.

        select  date_format(now(),'yyyy-MM-dd hh:mm:ss.SSS') ;

        The following table describes the columns in the result returned by the function.

        +-----+
| _c0 |
+-----+
| 2023-06-13 10:53:53.899 |
+-----+

    QUARTER

    • Syntax 

      int quarter (datetime|timestamp|date|string <date>)

    • Description

      Returns the quarter in which a date value falls. Valid values: 1 to 4.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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 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.

    • Parameters

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

    • 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_CHAR

    • Syntax 

      string to_char(datetime <date>, string <format>)

    • Description

      Converts the value of date, which is of the DATETIME type to a string in a specified format.

    • Parameters

      • date: required. 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 to a value of the DATETIME type before calculation.

      • format: required. A constant of the STRING type. This parameter specifies the date format. In the format parameter, the date format part is replaced by the related data and the other characters remain unchanged in the output.

    • Return values

      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, an error 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 20080718. 
select to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd');

-- The return value is 20080718. 
select to_char('2008-07-18 00:00:00', 'yyyymmdd');

-- 'Alibaba 2010-12*3' cannot be converted to 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');

-- '20102401' is not a standard DATETIME value, and an error is returned. The value must be written as '2010-01-24 00:00:00' 
select to_char(datetime'20102401', 'yyyy');

-- '2008718' is not a standard DATETIME value, and an error is returned. The value must be written as '2008-07-18 00:00:00'. 
select to_char(datetime'2008718', 'yyyymmdd');

-- The return value is null. 
select to_char(datetime'2010-12-03 00:00:00', null);

    TO_DATE

    • Syntax 

      datetime to_date(string <date>, string <format>)

    • Description

      Converts a string to the value of date in a specified format.

    • Parameters

      • date: required. A 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 data type, the value is implicitly converted to a value of the STRING type before calculation. The date string can also be in the ISO 8601 format.

      • format: required. A constant of the STRING type. This parameter specifies the 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 to a date value. The other 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 24-hour clock. 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 20:30:00. 
select to_date('200807182030','yyyymmddhhmi');

-- '2008718' cannot be converted to 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 to 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 to 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);

-- If the input value is in the ISO 8601 format, the return value is 2021-09-24 13:39:34. 
select to_date('2021-09-24T13:39:34.119Z', 'yyyy-MM-ddThh:mi:ss.ff3Z');

    UNIX_TIMESTAMP

    • Syntax 

      bigint unix_timestamp(datetime|date|timestamp|string <date>)

    • Description

      Converts the value of date to a UNIX timestamp that is an integer.

    • Parameters

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

    • 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 1237518660. 
select unix_timestamp('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 a date value falls.

    • Parameters

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

    • 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 a week and the return value is 0. Days of a 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 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 4. 
select weekday (datetime '2009-03-20 11:11:00');

-- The return value is 4. 
select weekday ('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 a date value 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, the week is considered the last week of the year. If the week belongs to the next year, the week is considered the first week of the next year.

    • Parameters

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

    • 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 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. 20141229 is in year 2014, but most days of the week fall in year 2015. In this case, 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 48. 
select weekofyear('2021-11-29 00:01:00');

-- The return value is null. 
select weekofyear('20141231');

-- The return value is null. 
select weekofyear(null);

    YEAR

    • Syntax 

      int year(datetime|timestamp|date|string <date>)

    • Description

      Returns the year in which a date value falls.

    • Parameters

      date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 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 values

      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 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);