本文为您介绍MaxCompute SQL提供的日期函数。

DATEADD

  • 命令格式 
    datetime dateadd(datetime date, bigint delta, string datepart)
  • 命令说明

    按照指定的单位datepart和幅度delta修改date的值。

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

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

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

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

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

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

  • 返回值说明

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

  • 示例
    • 示例1：DATEADD常见用法。 
      select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd') ;
--返回 2005-03-01 00:00:00。加1天，结果超出当年2月份的最后一天，实际值为下个月的第1天。
select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
--返回 2005-02-27 00:00:00。减1天。
select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
--返回 2006-10-28 00:00:00。加20个月，月份溢出，年份加1。
select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
--返回2005-03-28 00:00:00。
select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
--返回 2005-02-28 00:00:00。2005年2月没有29日，日期截取至当月最后1天。
select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
--返回2005-02-28 00:00:00。
    • 示例2：DATETIME用常量表示。
      在MaxCompute SQL中，DATETIME类型没有直接的常量表示方式，以下为错误的使用方式。 
      select dateadd(2005-03-30 00:00:00, -1, 'mm') from tbl1;
      如果需要描述DATETIME类型的常量，请参考以下用法。 
      --将STRING类型常量显式转换为DATETIME类型。
select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm') from tbl1;

DATE_ADD

  • 命令格式 
    date date_add(date/timestamp/string startdate, bigint delta)
  • 命令说明

    按照delta幅度增减startdate日期的天数。

  • 参数说明
    • startdate：日期值。支持DATE、DATETIME或STRING类型，其它数据类型会返回异常。

      如果参数为STRING类型，则会隐式转换为DATE类型后参与运算，且STRING参数格式必须为'yyyy-mm-dd'。例如'2019-12-27'。

    • delta：修改幅度，BIGINT类型，其他数据类型会返回异常。如果delta大于0，则增，否则减。
  • 返回值说明

    返回值为DATE类型。

  • 示例 
    set odps.sql.type.system.odps2=true;
--开启2.0新类型。此命令需要与SQL语句一起提交。
select date_add(datetime '2005-02-28 00:00:00', 1);
--返回2005-03-01 。加1天，结果超出当年2月份的最后1天，实际值为下个月的第1天。
select date_add(date '2005-02-28', -1);
--返回2005-02-27。减1天。
select date_add( '2005-02-28 00:00:00', 20);
--返回2005-03-20。

DATEDIFF

  • 命令格式 
    bigint datediff(datetime date1, datetime date2, string datepart)
  • 命令说明

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

  • 参数说明
    • date1、date2：DATETIME类型，被减数和减数。如果输入为STRING类型，则会隐式转换为DATETIME类型后参与运算，其它数据类型会返回异常。
    • datepart：时间单位，STRING类型常量。支持扩展的日期格式。如果datepart不符合指定格式或者为其它数据类型，则会返回异常。

      如果您打开了MaxCompute新数据类型开关，可以不填写datepart参数，默认日期格式为天。

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

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

  • 示例 
    --假设start=2005-12-31 23:59:59，end=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
--假设start = 2018-06-04 19:33:23.234，end = 2018-06-04 19:33:23.250，含毫秒的日期不属于标准DATETIME式样，不能直接隐式转换，此处需进行显示转换。
    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

  • 命令格式 
    bigint datepart(datetime date, string datepart)
  • 命令说明

    提取日期date中符合指定时间单位datepart的值。

  • 参数说明
    • date：DATETIME类型。如果输入为STRING类型，则会隐式转换为DATETIME类型后参与运算，其它数据类型会返回异常。
    • datepart：STRING类型常量，支持扩展的日期格式。如果datepart不符合指定格式或数据类型则会返回异常。
  • 返回值说明

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

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

DATETRUNC

  • 命令格式 
    datetime datetrunc (datetime date, string datepart)
  • 命令说明

    返回日期date被截取指定时间单位datepart后的日期值。

  • 参数说明
    • date：DATETIME类型。如果输入为STRING类型，则会隐式转换为DATETIME类型后参与运算。输入为其它数据类型则会返回异常。
    • datepart：STRING类型常量。支持扩展的日期格式。如果datepart不符合指定格式或者数据类型则会返回异常。
  • 返回值说明

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

  • 示例 
    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

  • 命令格式 
    datetime getdate()
  • 命令说明

    获取当前系统时间。使用东八区时间作为MaxCompute标准时间。

  • 返回值说明

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

    说明 在一个MaxCompute SQL任务中（以分布式方式执行），getdate总是返回一个固定的值。返回结果会是MaxCompute SQL执行期间的任意时间，时间精度精确到秒（如果开启了新数据类型2.0，时间精度精确到毫秒）。

ISDATE

  • 命令格式 
    boolean isdate(string date, string format)
  • 命令说明

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

  • 参数说明
    • date：STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，会隐式转换为STRING类型后参与运算。输入为其它数据类型会返回异常。
    • format：STRING类型常量，不支持日期扩展格式。输入其它数据类型会引发异常。如果format中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。例如isdate("1234-yyyy", "yyyy-yyyy")，会返回TRUE。
  • 返回值说明

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

LASTDAY

  • 命令格式 
    datetime lastday(datetime date)
  • 命令说明

    取date所在月的最后一天，截取到天，时分秒部分为00:00:00。

  • 参数说明

    date：DATETIME类型。如果输入为STRING类型，则会隐式转换为DATETIME类型后参与运算。输入为其它数据类型会引发异常。

  • 返回值说明

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

TO_DATE

  • 命令格式 
    datetime to_date(string date, string format)
  • 命令说明

    将一个format格式的字符串date转成日期值。

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

      format：参数至少包含yyyy，否则会引发异常。如果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。

  • 示例 
    to_date('阿里巴巴2010-12*03', '阿里巴巴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 --格式不符合，引发异常。
to_date('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd') = null --格式不符合，引发异常。
to_date('2010-24-01', 'yyyy') = null --格式不符合，引发异常。
to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3')=2018-10-30 15:13:12

TO_CHAR

  • 命令格式 
    string to_char(datetime date, string format)
  • 命令说明

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

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

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

  • 示例 
    to_char(datetime'2010-12-03 00:00:00', '阿里金融yyyy-mm*dd') = '阿里金融2010-12*03'
to_char(datetime'2008-07-18 00:00:00', 'yyyymmdd') = '20080718' 
to_char(datetime'阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd') --会引发异常。
to_char(datetime'2010-24-01', 'yyyy') --会引发异常。
to_char(datetime'2008718', 'yyyymmdd') --会引发异常。

UNIX_TIMESTAMP

  • 命令格式 
    bigint unix_timestamp(datetime date)
  • 命令说明

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

  • 参数说明

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

  • 返回值说明

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

  • 示例 
    select unix_timestamp(datetime'2009-03-20 11:11:00'); --返回结果为1237518660。

FROM_UNIXTIME

  • 命令格式 
    datetime from_unixtime(bigint unixtime)
  • 命令说明

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

  • 参数说明

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

  • 返回值说明
    返回DATETIME类型的日期值。如果输入参数为NULL，则返回NULL。
    说明 Hive兼容模式下（set odps.sql.hive.compatible=true;），如果您的入参是STRING类型，返回值也是STRING类型。
  • 示例 
    from_unixtime(123456789) = 1973-11-30 05:33:09

WEEKDAY

  • 命令格式 
    bigint weekday (datetime date)
  • 命令说明

    返回date日期是当前周的第几天。

  • 参数说明

    date：DATETIME类型日期值。

  • 返回值说明

    返回BIGINT类型。如果输入参数为NULL，则返回NULL。周一作为一周的第一天，返回值为0。其他日期依次递增，周日返回6。

WEEKOFYEAR

  • 命令格式 
    bigint weekofyear (datetime date)
  • 命令说明
    返回日期date位于那一年的第几周。周一作为一周的第一天。
    说明 这一周算上一年还是下一年，取决于这一周的大多数日期（4天以上）在哪一年。算在前一年，就是前一年的最后一周；算在后一年就是后一年的第一周。
  • 参数说明

    date：DATETIME类型日期值。

  • 返回值说明

    返回BIGINT类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    select weekofyear(to_date("20141229", "yyyymmdd")) from dual;  
--返回结果如下。
+------------+
| _c0        |
+------------+
| 1          |
+------------+
--虽然20141229属于2014年，但是这一周的大多数日期是在2015年，因此返回结果为1，表示是2015年的第一周。    
select weekofyear(to_date("20141231", "yyyymmdd")) from dual；--返回结果为1。  
select weekofyear(to_date("20151229", "yyyymmdd")) from dual；--返回结果为53。

MaxCompute2.0扩展函数

升级到MaxCompute2.0后，产品扩展了部分日期函数。如果您用到的函数涉及新数据类型（TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY），在使用扩展日期函数时，需要执行如下set语句开启新数据类型开关。
  • Session级别：如果使用新数据类型，您需要在SQL语句前加上set语句set odps.sql.type.system.odps2=true;，并与SQL语句一起提交执行。
  • Project级别：Project Owner可根据需要对Project进行设置，等待10~15分钟后才会生效。命令如下。 
    setproject odps.sql.type.system.odps2=true;
    对setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项，请参见数据类型版本说明。

YEAR

  • 命令格式 
    INT year(string date)
  • 命令说明

    返回日期date的年。此函数为MaxCompute2.0扩展函数。

  • 参数说明

    date：STRING类型日期值，格式至少包含yyyy-mm-dd且不含多余的字符串，否则返回NULL值。

  • 返回值

    返回INT类型。

  • 示例 
    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) --返回异常。

QUARTER

  • 命令格式 
    INT quarter (datetime/timestamp/string date)
  • 命令说明

    返回一个日期的季度，范围是1~4。此函数为MaxCompute2.0扩展函数。

  • 参数说明

    date：DATETIME、TIMESTAMP或STRING类型日期值，日期格式至少包含yyyy-mm-dd，其他类型会返回NULL值。

  • 返回值说明

    返回INT类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    quarter('1970-11-12 10:00:00') = 4
quarter('1970-11-12') = 4

MONTH

  • 命令格式 
    INT month(string date)
  • 命令说明

    返回一个日期的月份。此函数为MaxCompute2.0扩展函数。

  • 参数说明

    date：STRING类型日期值，其他类型将返回异常。

  • 返回值说明

    返回INT类型。

  • 示例 
    month('2014-09-01') = 9
month('20140901') = null

DAY

  • 命令格式 
    INT day(string date)
  • 命令说明

    返回一个日期的天。此函数为MaxCompute2.0扩展函数。

  • 参数说明

    date：STRING类型日期值（格式为yyyy-mm-dd或yyyy-mm-dd hh:mi:ss），其他类型将返回异常。

  • 返回值说明

    返回INT类型。

  • 示例 
    day('2014-09-01') = 1
day('20140901') = null

DAYOFMONTH

  • 命令格式 
    INT dayofmonth(date)
  • 命令说明

    返回日期日部分的值。例如2017年10月13日，执行命令int dayofmonth(2017-10-13)返回结果为13。此函数为MaxCompute2.0扩展函数。

  • 参数说明

    date：STRING类型日期值，其他类型将返回异常。

  • 返回值说明

    返回INT类型。

  • 示例 
    dayofmonth('2014-09-01') = 1
dayofmonth('20140901') = null

HOUR

  • 命令格式 
    INT hour(string date)
  • 命令说明

    返回日期小时部分的值。

  • 参数说明

    date：STRING类型日期值，其他类型将返回异常。此函数为MaxCompute2.0扩展函数。

  • 返回值说明

    返回INT类型。

  • 示例 
    hour('2014-09-01 12:00:00') = 12
hour('12:00:00') = 12
hour('20140901120000') = null

MINUTE

  • 命令格式 
    INT minute(string date)
  • 命令说明

    返回日期分钟部分的值。此函数为MaxCompute2.0扩展函数。

  • 参数说明

    date：STRING类型日期值，其他类型将返回异常。

  • 返回值说明

    返回INT类型。

  • 示例 
    minute('2014-09-01 12:30:00') = 30
minute('12:30:00') = 30
minute('20140901120000') = null

SECOND

  • 命令格式 
    INT second(string date)
  • 命令说明

    返回日期秒数部分的值。

  • 参数说明

    date：STRING类型日期值，其他类型将返回异常。此函数为MaxCompute2.0扩展函数。

  • 返回值说明

    返回INT类型。

  • 示例 
    second('2014-09-01 12:30:45') = 45
second('12:30:45') = 45
second('20140901123045') = null

CURRENT_TIMESTAMP

  • 命令格式 
    timestamp current_timestamp()
  • 命令说明

    返回当前TIMESTAMP类型的时间戳，值不固定。此函数为MaxCompute2.0扩展函数。

  • 返回值说明

    返回TIMESTAMP类型。

  • 示例 
    select current_timestamp() from dual; --返回'2017-08-03 11:50:30.661'。

FROM_UTC_TIMESTAMP

  • 命令格式 
    timestamp from_utc_timestamp({any primitive type}*, string timezone)
  • 命令说明

    将一个UTC时区的时间戳转换成一个指定时区的时间戳。此函数为MaxCompute2.0扩展函数。

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

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

  • 返回值说明

    返回TIMESTAMP类型。

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

ADD_MONTHS

  • 命令格式 
    string add_months(string startdate, int nummonths)
  • 命令说明

    返回开始日期startdate增加num_months个月后的日期。此函数为MaxCompute2.0扩展函数。

  • 参数说明
    • startdate：STRING类型，格式至少包含yyyy-mm-dd，否则返回NULL值。
    • num_months：INT型数值。
  • 返回值说明

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

  • 示例 
    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

LAST_DAY

  • 命令格式 
    string last_day(string date)
  • 命令说明

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

  • 参数说明

    date：STRING类型，格式为yyyy-MM-dd HH:mi:ss或yyyy-MM-dd。

  • 返回值说明

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

  • 示例 
    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

  • 命令格式 
    string next_day(string startdate, string week)
  • 命令说明

    返回大于指定日期startdate并且与week相匹配的第一个日期，即下周几的具体日期。

  • 参数说明
    • startdate：STRING类型，格式为yyyy-MM-dd HH:mi:ss或yyyy-MM-dd。
    • week：STRING类型，一个星期前2个或3个字母，或者一个星期的全名。例如MO、TUE或FRIDAY。
  • 返回值说明

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

  • 示例 
    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

  • 命令格式 
    double months_between(datetime/timestamp/string date1, datetime/timestamp/string date2)
  • 命令说明

    返回日期date1和date2之间的月数。此函数为MaxCompute2.0扩展函数。

  • 参数说明
    • date1：DATETIME、TIMESTAMP或STRING类型，格式为yyyy-MM-dd HH:mi:ss或yyyy-MM-dd。
    • date2：DATETIME、TIMESTAMP或STRING类型，格式为yyyy-MM-dd HH:mi:ss或yyyy-MM-dd。
  • 返回值说明
    返回DOUBLE类型。
    • 当date1晚于date2时，返回值为正。当date2晚于date1时，返回值为负。
    • 当date1和date2分别对应两个月的最后一天，返回整数月；否则计算方式为date1减去date2的天数除以31天。
  • 示例 
    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

EXTRACT

  • 命令格式 
    INT EXTRACT(<datepart> from <timestamp>)
  • 命令说明

    提取日期timestamp中指定单位datepart的部分。此函数为MaxCompute2.0扩展函数。

  • 参数说明
    • datepart：支持YEAR、MONTH、DAY、HOUR或MINUTE等时间描述。
    • timestamp：TIMESTAMP类型。
  • 返回值

    返回INT类型。

  • 示例 
    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;

--返回值如下。
+------+-------+------+------+--------+
| year | month | day  | hour | minute |
+------+-------+------+------+--------+
| 2019 | 5     | 1    | 11   | 21     |
+------+-------+------+------+--------+