阿里云物联网平台：日期函数

CURRENT_TIMESTAMP

• 命令格式

  timestamp current_timestamp()

• 命令说明

  返回当前TIMESTAMP类型的时间戳，值不固定。

• 返回值说明

  返回TIMESTAMP类型。

• 示例

  --返回'2017-08-03 11:50:30.661'。
      current_timestamp(); 

DATEADD

• 命令格式

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

• 命令说明

  按照指定的单位datepart和幅度delta修改date的值。如果您需要获取在当前时间基础上指定变动幅度的日期，请结合GETDATE函数使用。

• 参数说明
  • date：必填。日期值，DATETIME类型。

    如果参数为STRING类型，则会隐式转换为DATETIME类型后参与运算。参数为其他数据类型时会返回报错。

  • delta：必填。修改幅度，BIGINT类型。如果delta大于0，则增，否则减。

    如果参数为STRING或DOUBLE类型，则会隐式转换为BIGINT类型后参与运算。参数为其他数据类型时会返回报错。

    说明

    • 按照指定的单位增减delta时，会导致更高单位的进位或退位。年、月、时、分秒分别按照10进制、12进制、24进制、60进制进行计算。
    • 当delta的单位是月时，如果delta的月部分在增加delta值之后不造成Day溢出，则保持Day值不变，否则将Day值设置为结果月份的最后1天。
  • datepart：必填。指定修改的单位，STRING类型常量。非常量、不支持的格式或其他类型会返回报错。

    此字段的取值遵循STRING与DATETIME类型转换的约定，即yyyy表示年，mm表示月，dd表示天。关于类型转换的规则，请参见下文STRING与DATETIME类型之间的转换。该字段也支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。

• 返回值说明

  返回DATETIME类型。如果任一输入参数为NULL，则返回NULL。

• 示例
  • 示例1：trans_date=2005-02-28 00:00:00。

    --返回2005-03-01 00:00:00。加1天，结果超出当年2月份的最后一天，实际值为下个月的第1天。
        dateadd(trans_date, 1, 'dd');
    
    --返回2005-02-27 00:00:00。减1天。
        dateadd(trans_date, -1, 'dd');
    
    --返回2006-10-28 00:00:00。加20个月，月份溢出，年份加1。
        dateadd(trans_date, 20, 'mm');
    
    --返回2005-03-28 00:00:00。
        dateadd(trans_date, 1, 'mm');

  • 示例2：trans_date=2005-01-29 00:00:00。

    --返回2005-02-28 00:00:00。2005年2月没有29日，日期截取至当月最后1天。
        dateadd(trans_date, 1, 'mm');

  • 示例3：trans_date2005-03-30 00:00:00。

    --返回2005-02-28 00:00:00。
        dateadd(trans_date, -1, 'mm');

  • 示例4：计算当前时间。

    --假设当前时间为2020-11-17 16:31:44，返回2020-11-16 16:31:44。
        dateadd(getdate(),-1,'dd');

  • 示例5：将STRING类型常量显式转换为DATETIME类型。

    --返回2005-02-28 00:00:00。
        dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');

DATEDIFF

• 命令格式

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

• 命令说明

  计算两个时间date1、date2的差值，将差值以指定的时间单位datepart表示。

• 参数说明
  • date1、date2：必填。DATETIME类型，被减数和减数。如果输入为STRING类型，则会隐式转换为DATETIME类型后参与运算。其他数据类型会返回报错。
  • datepart：必填。指定修改的单位，STRING类型常量。非常量、不支持的格式或其他类型会返回报错。

    此字段的取值遵循STRING与DATETIME类型转换的约定，即yyyy表示年，mm表示月，dd表示天。关于类型转换的规则，请参见下文STRING与DATETIME类型之间的转换。该字段也支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。

    说明 系统计算时会按照datepart切掉低单位部分，然后再计算结果。
• 返回值说明

  返回BIGINT类型。如果任一输入参数为NULL，则返回NULL。如果date1小于date2，返回值为负数。

• 示例
  • 示例1：start=2005-12-31 23:59:59，end=2006-01-01 00:00:00。

    --返回1。
        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');
    
    --返回1800。
        datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'ss');
    
    --返回30。
        datediff(datetime'2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi');  

  • 示例2：start = 2018-06-04 19:33:23.234，end = 2018-06-04 19:33:23.250，含毫秒的日期不属于标准DATETIME式样，不能直接隐式转换，此处需进行显示转换。

    --返回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

• 命令格式

  datetime from_unixtime(bigint <unixtime>)

• 命令说明

  将数字型的UNIX时间日期值unixtime转为日期值。

• 参数说明

  unixtime：必填。BIGINT类型，秒数，UNIX格式的日期时间值。如果输入为STRING、DOUBLE或DECIMAL类型，则会隐式转换为BIGINT后参与运算。

• 返回值说明

  返回DATETIME类型的日期值。如果输入参数为NULL，则返回NULL。

• 示例

  --返回1973-11-30 05:33:09。
      from_unixtime(123456789);

FROM_UTC_TIMESTAMP

• 命令格式

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

• 命令说明

  将一个UTC时区的时间戳转换成一个指定时区的时间戳。

• 参数说明
  • {any primitive type}*：必填。时间戳，支持TIMESTAMP、DATETIME、TINYINT、SMALLINT、INT或BIGINT数据类型。如果该参数为TINYINT、SMALLINT、INT或BIGINT数据类型，则单位为毫秒。
  • timezone：必填。指定需要转换的目标时区，例如PST。

    本函数只支持Asia、Shanghai这种格式，不支持GMT+9这种格式。

    UTC时间为0时区时间，上海为东八区，对应上海时间为UTC+8小时。

• 返回值说明

  返回TIMESTAMP类型。

• 示例

  --输入参数为毫秒（ms），返回2017-08-01 04:24:00.0。
      from_utc_timestamp(1501557840000, 'PST'); 
  
  --返回1970-01-30 08:00:00.0。
      from_utc_timestamp('1970-01-30 16:00:00','PST'); 
  
  --返回1970-01-29 16:00:00.0。
      from_utc_timestamp('1970-01-30','PST') ; 

GETDATE

• 命令格式

  datetime getdate()

• 命令说明

  获取当前系统时间。

• 返回值说明

  返回当前日期和时间，DATETIME类型。

ISDATE

• 命令格式

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

• 命令说明

  判断一个日期字符串能否根据指定的格式串转换为一个日期值。如果能转换成功，返回True；否则返回False。

• 参数说明
  • date：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，会隐式转换为STRING类型后参与运算。输入为其他数据类型会返回报错。
  • format：必填。STRING类型常量，不支持日期扩展格式。输入其他数据类型会返回报错。如果format中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。
• 返回值说明

  返回BOOLEAN类型。如果任一输入参数为NULL，则返回NULL。

• 示例

  --返回True。
  isdate("1234-yyyy", "yyyy-yyyy");

TO_DATE

• 命令格式

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

• 命令说明

  将date转换成符合format格式的日期值。

• 参数说明
  • date：必填。STRING类型，要转换的字符串格式的日期值。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，输入为其他类型或者空串时会返回报错。
  • format：必填。STRING类型常量，日期格式。输入其他数据类型会返回报错。format不支持日期扩展格式，其他字符在解析时当作无用字符忽略。

    format：参数至少包含yyyy，否则会返回NULL。如果format中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。例如to_date("1234-2234", "yyyy-yyyy")会返回1234-01-01 00:00:00。

    format格式：yyyy为4位数的年，mm为2位数的月，dd为2位数的日，hh为24小时制的时，mi为2位数的分钟，ss为2位数秒，ff3为3位精度毫秒。

• 返回值说明

  返回DATETIME类型，格式为yyyy-mm-dd hh:mi:ss。如果任一输入参数为NULL，则返回NULL。

• 示例

  --返回2010-12-03 00:00:00。
      to_date('测试2010-12*03', '测试yyyy-mm*dd');
  
  --返回2008-07-18 00:00:00。
      to_date('20080718', 'yyyymmdd');
  
  --返回2008-07-18 20:30:00。
      to_date('200807182030','yyyymmddhhmi');
  
  --'2008718'无法转为标准日期值，引发异常，应该为'20080718'。
      to_date('2008718', 'yyyymmdd');
  
  --'测试2010-12*3'无法转为标准日期值，引发异常，应该为'测试2010-12*03'。
      to_date('测试2010-12*3', '测试yyyy-mm*dd');
  
  --'2010-24-01'无法转为标准日期值，引发异常，应该为'2010-01-24'。
      to_date('2010-24-01', 'yyyy');
  
  --返回2018-10-30 15:13:12。
      to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');

TO_CHAR

• 命令格式

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

• 命令说明

  将日期类型date按照format指定的格式转成字符串。

• 参数说明
  • date：必填。DATETIME类型，要转换的日期值。如果输入为STRING类型，会隐式转换为DATETIME类型后参与运算。输入为其他数据类型会返回报错。
  • format：必填。STRING类型常量。非常量或其他数据类型会返回报错。format中的日期格式部分会被替换成相应的数据，其他字符直接输出。
• 返回值说明

  返回STRING类型。如果任一输入参数为NULL，则返回NULL。

• 示例

  --返回20080718。
      to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd');
  
  --'测试2010-12*3'无法转换为符合要求的格式，会引发异常，应该为'测试2010-12*03'。
      to_char(datetime'测试2010-12*3', '测试yyyy-mm*dd');
  
  --'2010-24-01'不是标准日期值，引发异常，应该为'2010-01-24'。
      to_char(datetime'2010-24-01', 'yyyy');
  
  --'2008718'不是标准日期值，引发异常，应该为'20080718'。
      to_char(datetime'2008718', 'yyyymmdd');

UNIX_TIMESTAMP

• 命令格式

  bigint unix_timestamp(datetime <date>)

• 命令说明

  将日期date转化为整型的UNIX格式的日期时间值。

• 参数说明

  date：必填。DATETIME类型日期值。如果输入为STRING类型，则会隐式转换为DATETIME类型后参与运算。输入为其他数据类型会返回报错。当打开新数据类型属性时，隐式转换会失败，此时需要通过cast函数转换，例如unix_timestamp(cast(... as datetime))。

• 返回值说明

  返回BIGINT类型，表示UNIX格式日期值。参数输入值为NULL时，返回NULL值。

• 示例

  --返回1237518660。
      unix_timestamp(datetime'2009-03-20 11:11:00'); 

LAST_DAY

• 命令格式

  string last_day(string <date>)

• 命令说明

  返回该日期所在月份的最后一天日期。

• 参数说明

  date：必填。STRING类型日期值，格式至少包含yyyy-mm-dd，其他类型将返回NULL。

• 返回值说明

  返回STRING类型的日期值，格式为yyyy-mm-dd。

• 示例

  --返回2017-03-31。
      last_day('2017-03-04');
  
  --返回2017-07-31。
      last_day('2017-07-04 11:40:00');
  
  --返回NULL。
      last_day('20170304');

STRING与DATETIME类型之间的转换

STRING类型和DATETIME类型之间的相互转换时，使用的格式为yyyy-mm-dd hh:mi:ss。