全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
MaxCompute

日期函数

更新时间:2017-11-02 09:25:02

MaxCompute SQL 提供了针对 Datetime 类型的操作函数。

DATEADD

命令格式如下

  1. datetime dateadd(datetime date, bigint delta, string datepart)

命令说明如下

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

参数说明

  • date:Datetime 类型,日期值。若输入为 String 类型会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

  • delta:Bigint 类型,修改幅度。若输入为 String 类型或 Double 型会隐式转换到Bigint 类型后参与运算,其他类型会引发异常。若 delta 大于 0,则增,否则减。

  • datepart:String 类型常量。此字段的取值遵循 String 与 Datetime 类型转换的约定,即”yyyy”表示年,”mm”表示月。

    关于类型转换的规则,请参见 String 类型与 Datetime 类型之间的转换。此外也支持扩展的日期格式:年 - year,月 - month 或 mon,日 - day,小时 - hour。非常量、不支持的格式会或其它类型抛异常。

返回值:

返回 Datetime 类型。若任一输入参数为 NULL,返回 NULL。

注意

  • 按照指定的单位增减 delta 时,导致的对更高单位的进位或退位,年、月、时、分、秒分别按照 10 进制、12 进制、24 进制、60 进制进行计算。

  • 当 delta 的单位是月时,计算规则如下:

    若 Datetime 的月部分在增加 delta 值之后不造成 day 溢出,则保持 day 值不变,否则把 day 值设置为结果月份的最后一天。

  • datepart 的取值遵循 String 与 Datetime 类型转换的约定,即”yyyy”表示年,”mm”表示月…,Datetime 相关的内建函数如无特殊说明均遵守此约定。同时如果没有特殊说明,所有 Datetime 相关的内建函数的 part 部分也同样支持扩展的日期格式:年 - year,月 - month 或 mon,日 - day,小时 - hour。

示例如下

  1. trans_date = 2005-02-28 00:00:00
  2. dateadd(trans_date, 1, 'dd') = 2005-03-01 00:00:00
  3. -- 加一天,结果超出当年2月份的最后一天,实际值为下个月的第一天
  4. dateadd(trans_date, -1, 'dd') = 2005-02-27 00:00:00
  5. -- 减一天
  6. dateadd(trans_date, 20, 'mm') = 2006-10-28 00:00:00
  7. -- 20个月,月份溢出,年份加1
  8. trans_date = 2005-02-28 00:00:00, dateadd(transdate, 1, 'mm') = 2005-03-28 00:00:00
  9. trans_date = 2005-01-29 00:00:00, dateadd(transdate, 1, 'mm') = 2005-02-28 00:00:00
  10. -- 20052月没有29日,日期截取至当月最后一天
  11. trans_date = 2005-03-30 00:00:00, dateadd(transdate, -1, 'mm') = 2005-02-28 00:00:00

此处对 trans_date 的数值表示仅作示例使用,在文档中有关 Datetime 的介绍会经常使用到这种简易的表达方式。

在 MaxCompute SQL 中,Datetime 类型没有直接的常数表示方式,如下使用方式是错误的:

  1. select dateadd(2005-03-30 00:00:00, -1, 'mm') from tbl1;

如果一定要描述 Datetime 类型常量,请尝试如下方法:

  1. select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm') from tbl1;
  2. -- String类型常量显式转换为Datetime类型

DATEDIFF

命令格式如下

  1. bigint datediff(datetime date1, datetime date2, string datepart)

命令说明如下

计算两个时间 date1,date2 在指定时间单位 datepart 的差值。

参数说明

  • datet1,date2:Datetime 类型,被减数和减数,若输入为 String 类型会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

  • datepart:String 类型常量。支持扩展的日期格式。若 datepart 不符合指定格式或者其它类型则会发生异常。

返回值

返回 Bigint 类型。任一输入参数是 NULL,返回 NULL。如果 date1 小于 date2,返回值可以为负数。

注意

计算时会按照 datepart 切掉低单位部分,然后再计算结果。

示例如下

  1. start = 2005-12-31 23:59:59end = 2006-01-01 00:00:00:
  2. datediff(end, start, 'dd') = 1
  3. datediff(end, start, 'mm') = 1
  4. datediff(end, start, 'yyyy') = 1
  5. datediff(end, start, 'hh') = 1
  6. datediff(end, start, 'mi') = 1
  7. datediff(end, start, 'ss') = 1
  8. datediff('2013-05-31 13:00:00', '2013-05-31 12:30:00', 'ss') = 1800
  9. datediff('2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi') = 30

DATEPART

命令格式如下所示

  1. bigint datepart(datetime date, string datepart)

命令说明如下

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

参数说明

  • date:Datetime 类型,若输入为 String 类型会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

  • datepart:String 类型常量,支持扩展的日期格式。若 datepart 不符合指定格式或者其它类型则会发生异常。

返回值

返回 Bigint 类型。若任一输入参数为 NULL,返回 NULL。

示例如下

  1. datepart('2013-06-08 01:10:00', 'yyyy') = 2013
  2. datepart('2013-06-08 01:10:00', 'mm') = 6

DATETRUNC

命令格式如下

  1. datetime datetrunc (datetime date, string datepart)

命令说明如下

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

参数说明

  • date:Datetime 类型,若输入为 String 类型会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

  • datepart:String 类型常量,支持扩展的日期格式。若 datepartt 不符合指定格式或者其它类型则会发生异常。

返回值

Datetime 类型。任意一个参数为 NULL 时,返回 NULL。

示例如下

  1. datetrunc('2011-12-07 16:28:46', 'yyyy') = 2011-01-01 00:00:00
  2. datetrunc('2011-12-07 16:28:46', 'month') = 2011-12-01 00:00:00
  3. datetrunc('2011-12-07 16:28:46', 'DD') = 2011-12-07 00:00:00

FROM_UNIXTIME

命令格式如下

  1. datetime from_unixtime(bigint unixtime)

命令说明如下

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

参数说明

unixtime:Bigint 类型,秒数,unix 格式的日期时间值,若输入为 String,Double,Decimal 类型会隐式转换为 Bigint 后参与运算。

返回值

返回 Datetime 类型的日期值,unixtime 为 NULL 时,返回 NULL。

示例如下

  1. from_unixtime(123456789) = 1973-11-30 05:33:09

GETDATE

命令格式如下

  1. datetime getdate()

命令说明如下

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

返回值

返回当前日期和时间,Datetime 类型。

注意

在一个 MaxCompute SQL 任务中(以分布式方式执行),getdate 总是返回一个固定的值。返回结果会是 MaxCompute SQL 执行期间的任意时间,时间精度精确到秒(2.0版本会精确到毫秒)。

ISDATE

命令格式如下

  1. boolean isdate(string date, string format)

命令说明如下

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

参数说明

  • date:String 格式的日期值,若输入为 Bbigint,Double,Decimal 或 Datetime 类型,会隐式转换为 String 类型后参与运算,其它类型报异常。

  • format:String 类型常量,不支持日期扩展格式。其它类型或不支持的格式会抛异常。如果 format 中出现多余的格式串,则只取第一个格式串对应的日期数值,其余的会被视为分隔符。如 isdate(“1234-yyyy “, “yyyy-yyyy “),会返回 TRUE。

返回值

返回 Boolean 类型,如任意参数为 NULL,返回 NULL。

LASTDAY

命令格式如下

  1. datetime lastday(datetime date)

命令说明如下

取 date 当月的最后一天,截取到天,时分秒部分为 00:00:00。

参数说明

date:Datetime 类型,若输入为 string 类型,会隐式转换为 Datetime 类型后参与运算,其它类型报异常。

返回值

返回 Datetime 类型,如输入为 NULL,返回 NULL。

TO_DATE

命令格式如下

  1. 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。

返回值

返回 Datetime 类型,格式为:yyyy-mm-dd hh:mi:ss 。若任意一个输入的参数为 NULL,则返回 NULL 值。

示例如下

  1. to_date('阿里巴巴2010-12*03', '阿里巴巴yyyy-mm*dd') = 2010-12-03 00:00:00
  2. to_date('20080718', 'yyyymmdd') = 2008-07-18 00:00:00
  3. to_date('200807182030','yyyymmddhhmi')=2008-07-18 20:30:00
  4. to_date('2008718', 'yyyymmdd')
  5. -- 格式不符合,引发异常
  6. to_date('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd')
  7. -- 格式不符合,引发异常
  8. to_date('2010-24-01', 'yyyy')
  9. -- 格式不符合,引发异常

TO_CHAR

命令格式如下

  1. string to_char(datetime date, string format)

命令说明如下

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

参数类型

  • date:Datetime 类型,要转换的日期值,若输入为 String 类型,会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

  • format:String 类型常量。非常量或其他类型会引发异常。format 中的日期格式部分会被替换成相应的数据,其它字符直接输出。

返回值

返回 String 类型。若任意一个输入的参数为 NULL,则返回 NULL 值。

示例如下

  1. to_char('2010-12-03 00:00:00', '阿里金融yyyy-mm*dd') = '阿里金融2010-12*03'
  2. to_char('2008-07-18 00:00:00', 'yyyymmdd') = '20080718'
  3. to_char('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd') -- 引发异常
  4. to_char('2010-24-01', 'yyyy') -- 会引发异常
  5. to_char('2008718', 'yyyymmdd') -- 会引发异常

关于其他类型向 String 类型转换请参见 字符串函数 > TO_CHAR

UNIX_TIMESTAMP

命令格式如下

  1. bigint unix_timestamp(datetime date)

命令说明如下

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

参数说明

date:Datetime 类型日期值,若输入为 String 类型,会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

返回值

返回 Bigint 类型,表示 unix 格式日期值,date 为 NULL 时,返回 NULL 值。

WEEKDAY

命令格式如下

  1. bigint weekday (datetime date)

命令说明如下

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

参数说明

date:Datetime 类型,若输入为 String 类型,会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

返回值

返回 Bigint 类型,若输入参数为 NULL,返回 NULL 值。周一作为一周的第一天,返回值为 0。其他日期依次递增,周日返回 6。

WEEKOFYEAR

命令格式如下

  1. bigint weekofyear(datetime date)

命令说明如下

返回日期 date 位于那一年的第几周。周一作为一周的第一天。

注意

关于这一周算上一年, 还是下一年,主要是看这一周大多数日期(4 天以上)在哪一年多。 算在前一年,就是前一年的最后一周,算在后一年就是后一年的第一周。

参数说明:

date:Datetime 类型日期值,若输入为 String 类型,会隐式转换为 Datetime 类型后参与运算,其它类型抛异常。

返回值

返回 Bigint 类型。若输入为 NULL,返回 NULL 值。

示例如下

  1. select weekofyear(to_date("20141229", "yyyymmdd")) from dual;
  2. 返回结果:
  3. +------------+
  4. | _c0 |
  5. +------------+
  6. | 1 |
  7. +------------+
  8. -虽然20141229属于2014年,但是这一周的大多数日期是在2015年,因此返回结果为1,表示是2015年的第一周。
  9. select weekofyear(to_date("20141231", "yyyymmdd")) from dual;--返回结果为1
  10. select weekofyear(to_date("20151229", "yyyymmdd")) from dual;--返回结果为53

新扩展的日期函数

升级到 MaxCompute2.0 后,产品扩展了部分日期函数。您如果用到的函数涉及新数据类型,请在使用新函数的 SQL 前,需要加个 set 语句:

  1. set odps.sql.type.system.odps2=true;--开启新类型

若想同时提交,执行以下语句:

  1. set odps.sql.type.system.odps2=true;
  2. select year('1970-01-01 12:30:00')=1970 from dual;

下文将为您介绍新扩展的函数的详情。

YEAR

命令格式如下

  1. INT year(string date)

命令说明如下

返回一个日期的年。

参数说明

  • date:String 类型日期值,格式至少包含 ‘yyyy-mm-dd’ 且不含多余的字符串,否则返回 null 值。

返回值

返回 INT 类型。

示例如下

  1. year('1970-01-01 12:30:00')=1970
  2. year('1970-01-01')=1970
  3. year('70-01-01')=70
  4. year(1970-01-01)=null
  5. year('1970/03/09')=null
  6. year(null)返回异常

QUARTER

命令格式如下

  1. INT quarter(datetime/timestamp/string date )

命令说明如下

返回一个日期的季度,范围是 1-4。

参数说明

date:Datetime/Timestamp/String 类型日期值,日期格式至少包含 yyyy-mm-dd,其他会返回 null 值。

返回值

返回 INT 类型,输入 null,则返回 null 值。

示例如下

  1. quarter('1970-11-12 10:00:00')=4
  2. quarter('1970-11-12')=4

MONTH

命令格式如下

  1. INT month(string date)

命令说明如下

返回一个日期的月份。

参数说明

date:String 类型日期值,其他类型将返回异常。

返回值

返回 INT 类型。

示例如下

  1. month('2014-09-01')=9
  2. month('20140901')=null

DAY

命令格式如下

  1. INT day(string date)

命令说明如下

返回一个日期的天。

参数说明

date:String 类型日期值,其他类型将返回异常。

返回值

返回 INT 类型。

示例如下

  1. day('2014-09-01')=1
  2. day(
  3. '20140901')=null

DAYOFMONTH

命令格式如下

  1. INT dayofmonth(date)

命令说明如下

返回年/月/日中的具体日期。

例如 2017 年 10 月 13 日,执行命令int dayofmonth(2017-10-13)返回结果为 13。

参数说明

date:String 类型日期值,其他类型将返回异常。

返回值

返回 INT 类型。

示例如下

  1. dayofmonth('2014-09-01')=1
  2. dayofmonth('20140901')=null

HOUR

命令格式如下

  1. INT hour(string date)

命令说明如下

返回一个日期的小时。

参数说明

date:String 类型日期值,其他类型将返回异常。

返回值

返回 INT 类型。

示例如下

  1. hour('2014-09-01 12:00:00')=12
  2. hour('12:00:00')=12
  3. hour('20140901120000')=null

MINUTE

命令格式如下

  1. INT minute(string date)

命令说明如下

返回一个日期的分钟。

参数说明

date:String 类型日期值,其他类型将返回异常。

返回值

返回 INT 类型。

示例如下

  1. minute('2014-09-01 12:30:00')=30
  2. minute('12:30:00')=30
  3. minute('20140901120000')=null

SECOND

命令格式如下

  1. INT second(string date)

命令说明如下

返回一个日期的秒钟。

参数说明

date:String 类型日期值,其他类型将返回异常。

返回值

返回 INT 类型。

示例如下

  1. second('2014-09-01 12:30:45')=45
  2. second('12:30:45')=45
  3. second('20140901123045')=null

FROM_UTC_TIMESTAMP

命令格式如下

  1. timestamp from_utc_timestamp({any primitive type}*, string timezone)

命令说明如下

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

参数说明

  • {any primitive type}*:时间戳,类型包含 timestamp/datetime/tinyint/smallint/int/bigin。

  • timezone:指定需要转换的目标时区,如 PST。

返回值

返回 timestamp 类型。

示例如下

  1. from_utc_timestamp(1501557840,'PST')='1970-01-19 01:05:57.84'
  2. from_utc_timestamp('1970-01-30 16:00:00','PST')='
  3. 1970-01-31 00:00:00'
  4. from_utc_timestamp('1970-01-30','PST')='1970-01-30 08:00:00'

CURRENT_TIMESTAMP

命令格式如下

  1. timestamp current_timestamp()

命令说明如下

返回当前 timestamp 类型的时间戳,值不固定。

返回值

返回 timestamp 类型。

示例如下

  1. select current_timestamp() from dual;--返回'2017-08-03 11:50:30.661'

ADD_MONTHS

命令格式如下

  1. string add_months(string startdate, int nummonths)

命令说明如下

返回开始日期 startdate 增加 nummonths 个月后的日期。

参数说明

  • startdate:String 类型,格式至少包含 年-月-日 的日期,否则返回 null 值。

  • num_months:Int 型数值。

返回值

返回 String 类型的日期,格式为 yyyy-mm-dd。

示例如下

  1. add_months('2017-02-14',3)='2017-05-14'
  2. add_months('17-2-14',3)='0017-05-14'
  3. add_months('2017-02-14 21:30:00',3)='2017-05-14'
  4. add_months('20170214',3)=null

LAST_DAY

命令格式如下

  1. string last_day(string date)

命令说明如下

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

参数说明

date:String 类型,格式为 yyyy-MM-dd HH:mi:ss 或 yyyy-MM-dd。

返回值

返回 String 类型的日期,格式为 yyyy-mm-dd。

示例如下

  1. last_day('2017-03-04')='2017-03-31'
  2. last_day('2017-07-04 11:40:00')='2017-07-31'
  3. last_day('20170304')=null

NEXT_DAY

命令格式如下

  1. 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。

示例如下

  1. next_day('2017-08-01','TU')='2017-08-08'
  2. next_day('2017-08-01 23:34:00','TU')='2017-08-08'
  3. next_day('20170801','TU')=null

MONTHS_BETWEEN

命令格式如下

  1. double months_between(datetime/timestamp/string date1, datetime/timestamp/string date2)

命令说明如下

返回日期 date1 和 date2 之间的月数。

参数说明

  • 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 天。

示例如下

  1. months_between('1997-02-28 10:30:00', '1996-10-30')=3.9495967741935485
  2. months_between('1996-10-30','1997-02-28 10:30:00' )=-3.9495967741935485
  3. months_between('1996-09-30','1996-12-31')=-3.0
本文导读目录