全部产品
MaxCompute

日期函数

更新时间:2017-09-20 10:23:51   分享:   

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进制、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) = 2009-01-20 21:06:29

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格式的日期值,若输入为bigint,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)

用途:返回一个日期的天。

参数说明:

  • 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)

用途:返回开始日期startdat增加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
本文导读目录
本文导读目录
以上内容是否对您有帮助?