IoT Platform:Date functions

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.

• Example

  -- The return value is '2017-08-03 11:50:30.661'. 
      current_timestamp(); 

DATEADD

• Syntax

  datetime dateadd(datetime <date>, bigint <delta>, string <datepart>)

• Description

  Changes a date value based on the time unit specified by datepart and the interval specified by delta. To add or subtract an interval to or from the current time, you can call this function together with the GETDATE function.

• Parameters

  • date: required. 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 error is returned.

  • 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, an interval is added to the date value. Otherwise, an interval is subtracted from the date value.

    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. If the input value is of another data type, an error is returned.

    Note

    • If you add or subtract the interval specified by delta based on the time unit specified by datepart, a carry or return at more significant date components may occur. The year, month, hour, minute, and second components are calculated 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, you can retain the value of the day component. Otherwise, you must 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 or subtracts an interval to the year component of the date value. The value mm indicates that the DATEADD function adds or subtracts an interval to the month component of the date value. The value dd indicates that the DATEADD function adds or subtracts an interval to the day component of the date value. For more information about the rules of data type conversion, see Conversions between the STRING and DATETIME types. 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. If an input parameter is set to NULL, NULL is returned.

• Examples

  • Example 1: trans_date=2005-02-28 00:00:00.

    -- The return value is 2005-03-01 00:00:00. After one day is added, the result is after the last day of February. The first day of March is returned. 
        dateadd(trans_date, 1, 'dd');
    
    -- The return value is 2005-02-27 00:00:00. One day is subtracted. 
        dateadd(trans_date, -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. 
        dateadd(trans_date, 20, 'mm');
    
    -- The return value is 2005-03-28 00:00:00. 
        dateadd(trans_date, 1, 'mm');

  • Example 2: trans_date=2005-01-29 00:00:00.

    -- 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. 
        dateadd(trans_date, 1, 'mm');

  • Example 3: trans_date2005-03-30 00:00:00.

    -- The return value is 2005-02-28 00:00:00. 
        dateadd(trans_date, -1, 'mm');

  • Example 4: Calculate the current time.

    -- If the current time is November 17, 2020, 16:31:44, the return value is 2020-11-16 16:31:44. 
        dateadd(getdate(),-1,'dd');

  • Example 5: Explicitly convert a constant of the STRING type into the DATETIME type.

    -- The return value is 2005-02-28 00:00:00. 
        dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');

DATEDIFF

• Syntax

  bigint datediff(datetime <date1>, datetime <date2>, string <datepart>)

• Description

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

• Parameters

  • date1 and date2: required. Values of the DATETIME type. The values indicate the minuend and subtrahend. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation. If the input value is of another data type, an error is returned.

  • 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 or subtracts an interval to the year component of the date value. The value mm indicates that the DATEADD function adds or subtracts an interval to the month component of the date value. The value dd indicates that the DATEADD function adds or subtracts an interval to the day component of the date value. For more information about the rules of data type conversions, see Conversions between the STRING and DATETIME types. The Extended Date/Time Format (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. If an input parameter is set to NULL, NULL is returned. If date1 is earlier than date2, a negative value is returned.

• Examples

  • Example 1: start=2005-12-31 23:59:59, end=2006-01-01 00:00:00.

    -- The return value is 1. 
        datediff(end, start, 'dd'); 
    
    -- The return value is 1. 
        datediff(end, start, 'mm');
    
    -- The return value is 1. 
        datediff(end, start, 'yyyy');
    
    -- The return value is 1. 
        datediff(end, start, 'hh');
    
    -- The return value is 1. 
        datediff(end, start, 'mi');
    
    -- The return value is 1. 
        datediff(end, start, 'ss');
    
    -- The return value is 1800. 
        datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'ss');
    
    -- The return value is 30. 
        datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi');  

  • Example 2: If 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 that are accurate to the millisecond do not use 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. 
        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');

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 the second. 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 input parameter is set to NULL, NULL is returned.

• Example

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

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.

• 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 time unit is accurate to the millisecond.

  • timezone: required. The destination time zone into which you want to convert the current time zone. For example, you can set this parameter to PST.

    This function supports only the Asia/Shanghai time zone and does not support the GMT+9 time zone.

    The UTC time refers to the time in the UTC+0 time zone. Shanghai, China is in the UTC+8 time zone.

• Return value

  A value of the TIMESTAMP type is returned.

• Examples

  -- The unit of the input value is milliseconds and 2017-08-01 04:24:00.0 is returned. 
      from_utc_timestamp(1501557840000, 'PST'); 
  
  -- The return value is 1970-01-30 08:00:00.0. 
      from_utc_timestamp('1970-01-30 16:00:00','PST'); 
  
  -- The return value is 1970-01-29 16:00:00.0. 
      from_utc_timestamp('1970-01-30','PST') ; 

GETDATE

• Syntax

  datetime getdate()

• Description

  Returns the current system time.

• Return value

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

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. Values 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. If the input value is of another data type, an error is returned.

  • format: required. A constant of the STRING type. This parameter does not support EDTF. If the input value is of another data type, an error 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 other strings are considered delimiters.

• Return value

  A value of the BOOLEAN type is returned. If an input parameter is set to NULL, NULL is returned.

• Example

  -- The return value is true. 
  isdate("1234-yyyy", "yyyy-yyyy");

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 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, 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 error is returned.

  • format: required. A constant of the STRING type. This parameter specifies the date format. If the input value is of another data type, an error is returned. 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 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 an input parameter is set to NULL, NULL is returned.

• Examples

  -- The return value is 2010-12-03 00:00:00. 
      to_date('Test 2010-12*03', 'Test yyyy-mm*dd');
  
  -- The return value is 2008-07-18 00:00:00. 
      to_date('20080718', 'yyyymmdd');
  
  -- The return value is 2008-07-18 20:30:00. 
      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'. 
      to_date('2008718', 'yyyymmdd');
  
  -- 'Test2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be specified as 'Test 2010-12*03'. 
      to_date('Test 2010-12*3', 'Test yyyy-mm*dd');
  
  --'2010-24-01' cannot be converted into a standard date value, and an error is returned. The value must be specified as '2010-01-24'. 
      to_date('2010-24-01', 'yyyy');
  
  -- The return value is 2018-10-30 15:13:12. 
      to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');

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. This parameter specifies the date value that you want to convert. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation. If the input value is of another data type, an error is returned.

  • format: required. A constant of the STRING type. This parameter specifies the date format. If the value is not a constant or not of the STRING type, an error is returned. 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. If an input parameter is set to NULL, NULL is returned.

• Example

  -- The return value is 20080718. 
      to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd');
  
  --'Test 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be specified as 'Test 2010-12*03'. 
      to_char(datetime'Test 2010-12*3', 'Test yyyy-mm*dd');
  
  -- '2010-24-01' cannot be converted into a standard date value, and an error is returned. The value must be specified as '2010-01-24'. 
      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 specified as '20080718'. 
      to_char(datetime'2008718', 'yyyymmdd');

UNIX_TIMESTAMP

• Syntax

  bigint unix_timestamp(datetime <date>)

• Description

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

• Parameters

  date: required. A value of the DATETIME type. If the input value is of the STRING type, the value is implicitly converted into a value of the DATETIME type before calculation. If the input value is of another data type, an error is returned. If you enable a new data type, implicit conversion fails. In this case, you must call the CAST function to convert the data type. Example: unix_timestamp(cast(... as datetime)).

• Return value

  A UNIX timestamp of the BIGINT type is returned. If an input parameter is NULL, NULL is returned.

• Example

  -- The return value is 1237518660. 
      unix_timestamp(datetime'2009-03-20 11:11:00'); 

LAST_DAY

• Syntax

  string last_day(string <date>)

• Description

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

• Parameters

  date: required. A value of the STRING type. The format of the date value must include at least yyyy-mm-dd. Otherwise, NULL is returned.

• Return value

  A value of the STRING type is returned. The return value is in the yyyy-mm-dd format.

• Examples

  -- The return value is 2017-03-31. 
      last_day('2017-03-04');
  
  -- The return value is 2017-07-31. 
      last_day('2017-07-04 11:40:00');
  
  -- The return value is NULL. 
      last_day('20170304');

Conversions between the STRING type and the DATETIME type

When you perform conversions between the STRING type and the DATETIME type, the yyyy-mm-dd hh:mi:ss format is used.