全部产品
Search
文档中心

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

更新时间:Jun 29, 2023

本文介绍数据分析支持的日期函数的命令格式、参数说明及示例。您可根据实际需要选择合适的日期函数,完成日期计算、日期转换。

函数功能
CURRENT_TIMESTAMP返回当前TIMESTAMP类型的时间戳。
DATEADD按照指定的单位和幅度修改日期值。
DATEDIFF计算两个日期的差值并按照指定的单位表示。
FROM_UNIXTIME将数字型的UNIX值转换为日期值。
FROM_UTC_TIMESTAMP将一个UTC时区的时间戳转换成一个指定时区的时间戳。
GETDATE获取当前系统时间。
ISDATE判断一个日期字符串能否根据指定的格式串转换为一个日期值。
TO_DATE将指定格式的字符串转换为日期值。
TO_CHAR将日期按照指定格式转换为字符串。
UNIX_TIMESTAMP将日期转换为整型的UNIX格式的日期值。
LAST_DAY返回日期值所在月份的最后一天日期。

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>)
  • 命令说明

    计算两个时间date1date2的差值,将差值以指定的时间单位datepart表示。

  • 参数说明
    • date1date2:必填。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

单位字符串(忽略大小写)有效值域
yyyy0001~9999
mm01~12
dd01~28|29|30|31
hh00~23
mi00~59
ss00~59
说明
  • 各个单位的值域中,如果首位为0,不可省略。例如2014-1-9 12:12:12为非法的DATETIME格式,无法从STRING类型数据转换为DATETIME类型,必须写为2014-01-09 12:12:12
  • 只有符合上述格式描述的STRING类型才能够转换为DATETIME类型,例如CAST(“2013-12-31 02:34:34” AS DATETIME),将会把STRING类型2013-12-31 02:34:34 转换为DATETIME类型。同理,DATETIME转换为STRING时,默认转换为yyyy-mm-dd hh:mi:ss格式。