本文为您介绍MaxCompute支持的日期函数,包括日期函数的命令格式、参数说明和使用示例。您可以根据实际需要选择合适的日期函数,以完成日期计算或转换。
函数 | 功能 |
计算日期值增加指定月数后的日期。 | |
返回当前TIMESTAMP类型的时间戳。 | |
返回当前系统的时区值。 | |
按照指定的幅度增减天数,与 | |
按照指定的单位和幅度修改日期值。 | |
将日期值转换为指定格式的字符串。 | |
按照指定的幅度增减天数,与 | |
计算两个日期的差值并按照指定的单位表示。 | |
提取日期中符合指定时间单位的字段值。 | |
提取日期按照指定时间单位截取后的值。 | |
返回日期值的天。 | |
返回日部分的值。 | |
返回日期的星期值。 | |
返回日期是当年中第几天。 | |
获取日期TIMESTAMP中指定单位的部分。 | |
将数字型的UNIX值转换为日期值。 | |
将一个UTC时区的时间戳转换为一个指定时区的时间戳。 | |
获取当前系统时间。 | |
返回日期小时部分的值。 | |
判断一个日期字符串能否根据指定的格式串转换为一个日期值。 | |
返回日期值所在月份的最后一天日期。 | |
获取日期所在月的最后一天。 | |
返回日期分钟部分的值。 | |
返回日期值所属月份。 | |
返回指定日期值间的月数。 | |
返回大于日期值且与指定周相匹配的第一个日期。 | |
返回当前系统日期与时间。 | |
返回日期值所属季度。 | |
返回日期秒数部分的值。 | |
将日期按照指定格式转换为字符串。 | |
将指定格式的字符串转换为日期值。 | |
将指定日期转换为以毫秒为单位的UNIX时间戳。 | |
将日期转换为整型的UNIX格式的日期值。 | |
返回日期值是当前周的第几天。 | |
返回日期值位于当年的第几周。 | |
返回日期值的年。 |
注意事项
升级到MaxCompute 2.0后,产品扩展了部分函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展函数时,需要执行如下语句开启新数据类型开关:
示例数据
为便于理解各函数的使用方法,本文为您提供源数据,基于源数据提供函数相关示例。创建表mf_date_fun_t,并添加数据,命令示例如下。
create table if not exists mf_date_fun_t(
id int,
date1 date,
datetime1 datetime,
timestamp1 timestamp,
date2 date,
datetime2 datetime,
timestamp2 timestamp,
date3 string,
date4 bigint);
insert into mf_date_fun_t values
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);
查询表mf_date_fun_t中的数据,命令示例如下:
SELECT * FROM mf_date_fun_t;
返回结果如下。
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| id | date1 | datetime1 | timestamp1 | date2 | datetime2 | timestamp2 | date3 | date4 |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 1 | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780 |
| 2 | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781 |
| 3 | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782 |
| 4 | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783 |
| 5 | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784 |
| 6 | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785 |
| 7 | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786 |
| 8 | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787 |
| 9 | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788 |
| 10 | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-29 | 123456789 |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
ADD_MONTHS
命令格式
string add_months(date|datetime|timestamp|string <startdate>, int <num_months>)
命令说明
返回开始日期startdate增加num_months个月后的日期。此函数为MaxCompute 2.0扩展函数。
参数说明
startdate:必填。DATE、DATETIME、TIMESTAMP或STRING类型,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。num_months:必填。INT型数值。
返回值说明
返回STRING类型的日期值,格式为
yyyy-mm-dd
。返回规则如下:startdate非DATE、DATETIME、TIMESTAMP或STRING类型,或格式不符合要求时,返回NULL。
startdate值为NULL时,返回报错。
num_months值为NULL时,返回NULL。
示例
静态数据示例
--返回2017-05-14。 select add_months('2017-02-14',3); --返回0017-05-14。 select add_months('17-2-14',3); --返回2017-05-14。 select add_months('2017-02-14 21:30:00',3); --返回NULL。 select add_months('20170214',3); --返回NULL。 select add_months('2017-02-14 21:30:00',null);
表数据示例
基于示例数据,将date1、datetime1、timestamp1和date3列日期转换为整型的UNIX格式的日期值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, add_months(date1,1) as date1_add_months, datetime1, add_months(datetime1, 2) as datetime1_add_months, timestamp1, add_months(timestamp1,3) as timestamp1_add_months, date3, add_months(date3,4) as date3_add_months from mf_date_fun_t;
返回结果如下。
+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+ | date1 | date1_add_months | datetime1 | datetime1_add_months | timestamp1 | timestamp1_add_months | date3 | date3_add_months | +------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+ | 2021-11-29 | 2021-12-29 | 2021-11-29 00:01:00 | 2022-01-29 | 2021-01-11 00:00:00.123456789 | 2021-04-11 | 2021-11-20 | 2022-03-20 | | 2021-11-28 | 2021-12-28 | 2021-11-28 00:02:00 | 2022-01-28 | 2021-02-11 00:00:00.123456789 | 2021-05-11 | 2021-11-21 | 2022-03-21 | | 2021-11-27 | 2021-12-27 | 2021-11-27 00:03:00 | 2022-01-27 | 2021-03-11 00:00:00.123456789 | 2021-06-11 | 2021-11-22 | 2022-03-22 | | 2021-11-26 | 2021-12-26 | 2021-11-26 00:04:00 | 2022-01-26 | 2021-04-11 00:00:00.123456789 | 2021-07-11 | 2021-11-23 | 2022-03-23 | | 2021-11-25 | 2021-12-25 | 2021-11-25 00:05:00 | 2022-01-25 | 2021-05-11 00:00:00.123456789 | 2021-08-11 | 2021-11-24 | 2022-03-24 | | 2021-11-24 | 2021-12-24 | 2021-11-24 00:06:00 | 2022-01-24 | 2021-06-11 00:00:00.123456789 | 2021-09-11 | 2021-11-25 | 2022-03-25 | | 2021-11-23 | 2021-12-23 | 2021-11-23 00:07:00 | 2022-01-23 | 2021-07-11 00:00:00.123456789 | 2021-10-11 | 2021-11-26 | 2022-03-26 | | 2021-11-22 | 2021-12-22 | 2021-11-22 00:08:00 | 2022-01-22 | 2021-08-11 00:00:00.123456789 | 2021-11-11 | 2021-11-27 | 2022-03-27 | | 2021-11-21 | 2021-12-21 | 2021-11-21 00:09:00 | 2022-01-21 | 2021-09-11 00:00:00.123456789 | 2021-12-11 | 2021-11-28 | 2022-03-28 | | 2021-11-20 | 2021-12-20 | 2021-11-20 00:10:00 | 2022-01-20 | 2021-10-11 00:00:00.123456789 | 2022-01-11 | 2021-11-29 | 2022-03-29 | +------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+
CURRENT_TIMESTAMP
命令格式
timestamp current_timestamp()
命令说明
返回当前TIMESTAMP类型的时间戳,值不固定。此函数为MaxCompute 2.0扩展函数。
返回值说明
返回TIMESTAMP类型。
示例
--返回'2017-08-03 11:50:30.661'。 set odps.sql.type.system.odps2=true; select current_timestamp();
CURRENT_TIMEZONE
命令格式
string current_timezone()
命令说明
返回当前系统的时区信息。
返回值说明
返回STRING类型。
示例
--返回Asia/Shanghai。 select current_timezone();
DATE_ADD
命令格式
date date_add(date|timestamp|string <startdate>, bigint <delta>)
命令说明
按照delta幅度增减startdate日期的天数。如果您需要获取在当前时间基础上指定变动幅度的日期,请结合GETDATE函数使用。
本命令与DATE_SUB的增减逻辑相反。
参数说明
startdate:必填。起始日期值。支持DATE、DATETIME或STRING类型。
如果参数为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATE类型后参与运算,且STRING参数格式至少要包含
'yyyy-mm-dd'
。例如'2019-12-27'
。delta:必填。修改幅度。BIGINT类型。如果delta大于0,则增;delta小于0,则减;delta等于0,不增不减。
返回值说明
返回DATE类型,格式为
yyyy-mm-dd
。返回规则如下:startdate非DATE、DATETIME或STRING类型时,返回报错。
startdate值为NULL时,返回报错。
delta值为NULL时,返回NULL。
示例
静态数据示例
--返回2005-03-01。加1天,结果超出当年2月份的最后1天,实际值为下个月的第1天。 select date_add(datetime '2005-02-28 00:00:00', 1); --返回2005-02-27。减1天。 select date_add(date '2005-02-28', -1); --返回2005-03-20。 set odps.sql.type.system.odps2=false; select date_add('2005-02-28 00:00:00', 20); --假设当前时间为2020-11-17 16:31:44,返回2020-11-16。 select date_add(getdate(),-1); --返回NULL。 select date_add('2005-02-28 00:00:00', null);
表数据示例
基于示例数据,对date1、datetime1和timestamp1列按照幅度变化天数,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, date_add(date1,1) as date1_date_add, datetime1, date_add(datetime1,-1) as datetime1_date_add, timestamp1, date_add(timestamp1,0) as timestamp1_date_add from mf_date_fun_t;
返回结果如下。
+------------+----------------+---------------------+-------------------+-------------------------------+--------------------+ | date1 | date1_date_add | datetime1 | datetime1_date_add| timestamp1 | timestamp1_date_add| +------------+----------------+---------------------+-------------------+-------------------------------+--------------------+ | 2021-11-29 | 2021-11-30 | 2021-11-29 00:01:00 | 2021-11-28 | 2021-01-11 00:00:00.123456789 | 2021-01-11 | | 2021-11-28 | 2021-11-29 | 2021-11-28 00:02:00 | 2021-11-27 | 2021-02-11 00:00:00.123456789 | 2021-02-11 | | 2021-11-27 | 2021-11-28 | 2021-11-27 00:03:00 | 2021-11-26 | 2021-03-11 00:00:00.123456789 | 2021-03-11 | | 2021-11-26 | 2021-11-27 | 2021-11-26 00:04:00 | 2021-11-25 | 2021-04-11 00:00:00.123456789 | 2021-04-11 | | 2021-11-25 | 2021-11-26 | 2021-11-25 00:05:00 | 2021-11-24 | 2021-05-11 00:00:00.123456789 | 2021-05-11 | | 2021-11-24 | 2021-11-25 | 2021-11-24 00:06:00 | 2021-11-23 | 2021-06-11 00:00:00.123456789 | 2021-06-11 | | 2021-11-23 | 2021-11-24 | 2021-11-23 00:07:00 | 2021-11-22 | 2021-07-11 00:00:00.123456789 | 2021-07-11 | | 2021-11-22 | 2021-11-23 | 2021-11-22 00:08:00 | 2021-11-21 | 2021-08-11 00:00:00.123456789 | 2021-08-11 | | 2021-11-21 | 2021-11-22 | 2021-11-21 00:09:00 | 2021-11-20 | 2021-09-11 00:00:00.123456789 | 2021-09-11 | | 2021-11-20 | 2021-11-21 | 2021-11-20 00:10:00 | 2021-11-19 | 2021-10-11 00:00:00.123456789 | 2021-10-11 | +------------+----------------+---------------------+-------------------+-------------------------------+--------------------+
DATEADD
命令格式
date|datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>)
命令说明
按照指定的单位datepart和幅度delta修改date的值。如果您需要获取在当前时间基础上指定变动幅度的日期,请结合GETDATE函数使用。
参数说明
date:必填。日期值,DATE、DATETIME或TIMESTAMP类型。
如果参数为STRING类型,格式符合DATETIME类型的格式,即
yyyy-mm-dd hh:mi:ss
,例如2021-08-28 00:00:00
,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为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
表示天。关于类型转换的规则,详情请参见数据类型转换。该字段也支持扩展的日期格式:年-year
、月-month
或-mon
、日-day
和小时-hour
。
返回值说明
返回DATE或DATETIME类型,格式为
yyyy-mm-dd
或yyyy-mm-dd hh:mi:ss
。返回规则如下:date非DATE、DATETIME或TIMESTAMP类型时,返回报错。
date值为NULL时,返回报错。
delta或datepart值为NULL时,返回NULL。
示例
静态数据示例
示例1:常见用法。
--返回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', -1, 'dd'); --返回2006-10-28 00:00:00。加20个月,月份溢出,年份加1。 select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm'); --返回2005-03-28 00:00:00。 select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm'); --返回2005-02-28 00:00:00。2005年2月没有29日,日期截取至当月最后1天。 select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm'); --返回2005-02-28 00:00:00。 select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm'); --返回2005-03-18。 select dateadd(date '2005-02-18', 1, 'mm'); --开启2.0新类型。此命令需要与SQL语句一起提交。返回2005-03-18 00:00:00.0。 set odps.sql.type.system.odps2=true; select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm'); --假设当前时间为2020-11-17 16:31:44,返回2020-11-16 16:31:44。 select dateadd(getdate(),-1,'dd'); --返回NULL。 select dateadd(date '2005-02-18', 1, null);
示例2:用常量表示。
在MaxCompute SQL中,DATETIME类型没有直接的常量表示方式,错误命令示例如下:
select dateadd(2005-03-30 00:00:00, -1, 'mm');
如果需要描述DATETIME类型的常量,正确命令示例如下:
--将STRING类型常量显式转换为DATETIME类型,返回2005-02-28 00:00:00。 select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');
示例3:输入参数为STRING类型。
--如下输入参数为STRING类型,但不符合DATETIME类型格式,会返回报错。 select dateadd('2021-08-27',1,'dd'); --如下输入参数为STRING类型,符合DATETIME类型格式,且MaxCompute项目的数据类型版本为1.0,返回2005-03-01 00:00:00。 set odps.sql.type.system.odps2=false; select dateadd('2005-02-28 00:00:00', 1, 'dd');
表数据示例
基于示例数据,对date1、datetime1和timestamp1列按照指定单位和幅度变化,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, dateadd(date1,1,'dd') as date1_dateadd, datetime1, dateadd(datetime1,1,'mm') as datetime1_dateadd, timestamp1, dateadd(timestamp1,-1,'yyyy') as timestamp1_dateadd from mf_date_fun_t;
返回结果如下。
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+ | date1 | date1_dateadd | datetime1 | datetime1_dateadd | timestamp1 | timestamp1_dateadd | +------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+ | 2021-11-29 | 2021-11-30 | 2021-11-29 00:01:00 | 2021-12-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2020-01-11 00:00:00.123456789 | | 2021-11-28 | 2021-11-29 | 2021-11-28 00:02:00 | 2021-12-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2020-02-11 00:00:00.123456789 | | 2021-11-27 | 2021-11-28 | 2021-11-27 00:03:00 | 2021-12-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2020-03-11 00:00:00.123456789 | | 2021-11-26 | 2021-11-27 | 2021-11-26 00:04:00 | 2021-12-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2020-04-11 00:00:00.123456789 | | 2021-11-25 | 2021-11-26 | 2021-11-25 00:05:00 | 2021-12-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2020-05-11 00:00:00.123456789 | | 2021-11-24 | 2021-11-25 | 2021-11-24 00:06:00 | 2021-12-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2020-06-11 00:00:00.123456789 | | 2021-11-23 | 2021-11-24 | 2021-11-23 00:07:00 | 2021-12-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2020-07-11 00:00:00.123456789 | | 2021-11-22 | 2021-11-23 | 2021-11-22 00:08:00 | 2021-12-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2020-08-11 00:00:00.123456789 | | 2021-11-21 | 2021-11-22 | 2021-11-21 00:09:00 | 2021-12-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2020-09-11 00:00:00.123456789 | | 2021-11-20 | 2021-11-21 | 2021-11-20 00:10:00 | 2021-12-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2020-10-11 00:00:00.123456789 | +------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
DATE_FORMAT
命令格式
string date_format(date|timestamp|string <date>, string <format>)
命令说明
将date按照format指定的格式转换为字符串。
参数说明
date:必填。待转换的日期值。支持DATE、TIMESTAMP或STRING类型。
DATE和STRING类型只能在Hive兼容模式下使用,您可以执行
set odps.sql.hive.compatible=true;
命令打开Hive模式。如果参数为STRING类型,则格式只支持以下三种日期字段:
'yyyy-MM-dd'
,例如'2019-12-27'
。'yyyy-MM-dd hh:mm:ss'
,例如'2019-12-27 12:23:10'
。'yyyy-MM-dd hh:mm:ss.SSS'
,例如'2019-12-27 12:23:10.123'
。
format:必填。STRING类型常量。format可由如下日期字段组成,例如
yyyy-MM-dd hh:mm:ss:SSS
或yyyy-MM-dd hh:mi:ss:SSS
:YYYY
或yyyy
代表年份,其中yyyy代表为日历年,YYYY代表为周年。说明周年格式可能会与实际年份不一致,请谨慎使用。
MM
代表月份。mm
代表分钟。dd
代表天。HH
代表24小时制时。hh
代表12小时制时。mi
代表分钟。ss
代表秒。SSS
代表毫秒。
重要在非Hive兼容模式下,
HH
和hh
都代表24小时制时。并且只能使用yyyy-MM-dd hh:mi:ss
,如果使用yyyy-MM-dd hh:mm:ss
,mm
部分会取月份值。在Hive兼容模式下,
HH
代表24小时制时,hh
代表12小时制时。并且只能使用yyyy-MM-dd hh:mm:ss
,如果使用yyyy-MM-dd hh:mi:ss
会返回NULL。
返回值说明
返回STRING类型。返回规则如下:
date非DATE或TIMESTAMP类型时,返回NULL。
date值为NULL时,返回报错。
format值为NULL时,返回NULL。
示例
静态数据示例
--打开Hive模式。与SQL语句一起提交。 set odps.sql.hive.compatible=true; --假设当前时间为2022-04-24 15:49。返回2022-04-24 03:49:01.902。 select date_format(from_utc_timestamp(current_timestamp(), 'UTC'),'yyyy-MM-dd hh:mm:ss.SSS'); --返回2022-04-24。 select date_format('2022-04-24','yyyy-MM-dd');
表数据示例
基于示例数据,对datetime1和timestamp1转换为指定格式的字符串,命令示例如下。
--打开Hive模式并开启2.0新类型。与SQL语句一起提交。 set odps.sql.hive.compatible=true; set odps.sql.type.system.odps2=true; select datetime1, timestamp1, date_format(datetime1,'yyyy/MM/dd'), date_format(datetime1,'yyyy/MM/dd HH:mm:ss'), date_format(timestamp1,'yyyy/MM/dd HH:mm:ss') from mf_date_fun_t;
返回结果如下。
+------------+------------+-----+-----+-----+ | datetime1 | timestamp1 | _c2 | _c3 | _c4 | +------------+------------+-----+-----+-----+ | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021/11/29 | 2021/11/29 00:01:00 | 2021/01/11 00:00:00 | | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021/11/28 | 2021/11/28 00:02:00 | 2021/02/11 00:00:00 | | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021/11/27 | 2021/11/27 00:03:00 | 2021/03/11 00:00:00 | | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021/11/26 | 2021/11/26 00:04:00 | 2021/04/11 00:00:00 | | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021/11/25 | 2021/11/25 00:05:00 | 2021/05/11 00:00:00 | | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021/11/24 | 2021/11/24 00:06:00 | 2021/06/11 00:00:00 | | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021/11/23 | 2021/11/23 00:07:00 | 2021/07/11 00:00:00 | | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021/11/22 | 2021/11/22 00:08:00 | 2021/08/11 00:00:00 | | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021/11/21 | 2021/11/21 00:09:00 | 2021/09/11 00:00:00 | | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021/11/20 | 2021/11/20 00:10:00 | 2021/10/11 00:00:00 | +------------+------------+-----+-----+-----+
DATE_SUB
命令格式
date date_sub(date|timestamp|string <startdate>, bigint <delta>)
命令说明
按照delta幅度增减startdate日期的天数。如果您需要获取在当前时间基础上指定变动幅度的日期,请结合GETDATE函数使用。
本命令与DATE_ADD的增减逻辑相反。
参数说明
startdate:必填。起始日期值。支持DATE、DATETIME或STRING类型。
如果参数为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATE类型后参与运算,且STRING参数格式至少要包含
'yyyy-mm-dd'
。例如'2019-12-27'
。delta:必填。修改幅度。BIGINT类型。如果delta大于0,则减;delta小于0,则增;delta等于0,不增不减。
返回值说明
返回DATE类型,格式为
yyyy-mm-dd
。返回规则如下:startdate非DATE、DATETIME或STRING类型时,返回报错。
startdate值为NULL时,返回报错。
delta值为NULL时,返回NULL。
示例
静态数据示例
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; --返回2005-02-28。减1天,实际值为上个月的最后1天。 select date_sub(datetime '2005-03-01 00:00:00', 1); --返回2005-03-01。增1天。 select date_sub(date '2005-02-28', -1); --返回2005-02-27。减2天。 set odps.sql.type.system.odps2=false; select date_sub('2005-03-01 00:00:00', 2); --假设当前时间为2021-09-10 16:31:44,返回2021-09-09。 select date_sub(getdate(),1); --返回NULL。 select date_sub('2005-03-01 00:00:00', null);
表数据示例
基于示例数据,对date1、datetime1和timestamp1列按照幅度变化天数,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, date_sub(date1,1) as date1_date_sub, datetime1, date_sub(datetime1,-1) as datetime1_date_sub, timestamp1, date_sub(timestamp1,0) as timestamp1_date_sub from mf_date_fun_t;
返回结果如下。
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+ | date1 | date1_date_sub | datetime1 | datetime1_date_sub | timestamp1 | timestamp1_date_sub | +------------+----------------+---------------------+--------------------+-------------------------------+---------------------+ | 2021-11-29 | 2021-11-28 | 2021-11-29 00:01:00 | 2021-11-30 | 2021-01-11 00:00:00.123456789 | 2021-01-11 | | 2021-11-28 | 2021-11-27 | 2021-11-28 00:02:00 | 2021-11-29 | 2021-02-11 00:00:00.123456789 | 2021-02-11 | | 2021-11-27 | 2021-11-26 | 2021-11-27 00:03:00 | 2021-11-28 | 2021-03-11 00:00:00.123456789 | 2021-03-11 | | 2021-11-26 | 2021-11-25 | 2021-11-26 00:04:00 | 2021-11-27 | 2021-04-11 00:00:00.123456789 | 2021-04-11 | | 2021-11-25 | 2021-11-24 | 2021-11-25 00:05:00 | 2021-11-26 | 2021-05-11 00:00:00.123456789 | 2021-05-11 | | 2021-11-24 | 2021-11-23 | 2021-11-24 00:06:00 | 2021-11-25 | 2021-06-11 00:00:00.123456789 | 2021-06-11 | | 2021-11-23 | 2021-11-22 | 2021-11-23 00:07:00 | 2021-11-24 | 2021-07-11 00:00:00.123456789 | 2021-07-11 | | 2021-11-22 | 2021-11-21 | 2021-11-22 00:08:00 | 2021-11-23 | 2021-08-11 00:00:00.123456789 | 2021-08-11 | | 2021-11-21 | 2021-11-20 | 2021-11-21 00:09:00 | 2021-11-22 | 2021-09-11 00:00:00.123456789 | 2021-09-11 | | 2021-11-20 | 2021-11-19 | 2021-11-20 00:10:00 | 2021-11-21 | 2021-10-11 00:00:00.123456789 | 2021-10-11 | +------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
DATEDIFF
命令格式
bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>)
命令说明
计算两个时间date1、date2的差值,将差值以指定的时间单位datepart表示。
参数说明
date1、date2:必填。DATE、DATETIME或TIMESTAMP类型。被减数和减数。
如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。
说明MaxCompute数据类型版本2.0才支持TIMESTAMP类型。关于2.0数据类型版本详情,请参见2.0数据类型版本。
datepart:可选。时间单位,STRING类型常量。
如果您打开了MaxCompute新数据类型开关,可以不填写datepart参数,默认日期格式为天。该字段也支持扩展的日期格式:年
-year
、月-month
或-mon
、日-day
和小时-hour
。说明系统计算时会按照datepart切掉低单位部分,然后再计算结果。
返回值说明
返回BIGINT类型。返回规则如下:
date1、date2非DATE、DATETIME或TIMESTAMP类型时,返回报错。
如果date1小于date2,返回值为负数。
date1或date2值为NULL时,返回NULL。
datepart值为NULL时,返回NULL。
示例
静态数据示例
示例1
SET odps.sql.type.system.odps2=true; SELECT DATEDIFF(TIMESTAMP '2006-01-01 00:00:00', TIMESTAMP '2005-12-31 23:59:59', 'dd');
返回结果如下。
+------------+ | _c0 | +------------+ | 1 | +------------+
示例2
SET odps.sql.type.system.odps2=true; SELECT DATEDIFF(TIMESTAMP '2006-01-01 00:00:00', TIMESTAMP '2005-12-31 23:59:59', 'mm');
返回结果如下。
+------------+ | _c0 | +------------+ | 1 | +------------+
示例3
SELECT DATEDIFF(DATETIME '2013-05-31 13:00:00', DATETIME '2013-05-31 12:30:00','ss');
返回结果如下。
+------------+ | _c0 | +------------+ | 1800 | +------------+
示例4
SET odps.sql.type.system.odps2 = false; SELECT DATEDIFF('2013-05-31 13:00:00','2013-05-31 12:30:00','mi');
返回结果如下。
+------------+ | _c0 | +------------+ | 30 | +------------+
示例5
--含毫秒的日期不属于标准DATETIME式样,不能直接隐式转换,此处需进行显示转换 SELECT 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');
返回结果如下。
+------------+ | _c0 | +------------+ | 16 | +------------+
示例6
SET odps.sql.type.system.odps2=true; SELECT DATEDIFF(DATE '2013-05-21',DATE '2013-05-10',NULL);
返回结果如下。
+------------+ | _c0 | +------------+ | NULL | +------------+
表数据示例
基于示例数据,计算date1和date2、datetime1和datetime2、timestamp1和timestamp2列的差值,并以指定单位表示,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 SET odps.sql.type.system.odps2 = true; SELECT date1 ,date2 ,DATEDIFF(date1,date2,'dd') AS date1_date2_datediff ,datetime1 ,datetime2 ,DATEDIFF(datetime1,datetime2,'dd') AS datetime1_datetime2_datediff ,timestamp1 ,timestamp2 ,DATEDIFF(timestamp1,timestamp2,'mm') AS timestamp1_timestamp2_datediff FROM mf_date_fun_t;
返回结果如下。
+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+ | date1 | date2 | date1_date2_datediff | datetime1 | datetime2 | datetime1_datetime2_datediff | timestamp1 | timestamp2 | timestamp1_timestamp2_datediff | +------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+ | 2021-11-29 | 2021-10-29 | 31 | 2021-11-29 00:01:00 | 2021-10-29 00:00:00 | 31 | 2021-01-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -9 | | 2021-11-28 | 2021-10-29 | 30 | 2021-11-28 00:02:00 | 2021-10-29 00:00:00 | 30 | 2021-02-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -8 | | 2021-11-27 | 2021-10-29 | 29 | 2021-11-27 00:03:00 | 2021-10-29 00:00:00 | 29 | 2021-03-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -7 | | 2021-11-26 | 2021-10-29 | 28 | 2021-11-26 00:04:00 | 2021-10-29 00:00:00 | 28 | 2021-04-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -6 | | 2021-11-25 | 2021-10-29 | 27 | 2021-11-25 00:05:00 | 2021-10-29 00:00:00 | 27 | 2021-05-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -5 | | 2021-11-24 | 2021-10-29 | 26 | 2021-11-24 00:06:00 | 2021-10-29 00:00:00 | 26 | 2021-06-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -4 | | 2021-11-23 | 2021-10-29 | 25 | 2021-11-23 00:07:00 | 2021-10-29 00:00:00 | 25 | 2021-07-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -3 | | 2021-11-22 | 2021-10-29 | 24 | 2021-11-22 00:08:00 | 2021-10-29 00:00:00 | 24 | 2021-08-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -2 | | 2021-11-21 | 2021-10-29 | 23 | 2021-11-21 00:09:00 | 2021-10-29 00:00:00 | 23 | 2021-09-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -1 | | 2021-11-20 | 2021-10-29 | 22 | 2021-11-20 00:10:00 | 2021-10-29 00:00:00 | 22 | 2021-10-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | 0 | +------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+
DATEPART
命令格式
bigint datepart(date|datetime|timestamp <date>, string <datepart>)
命令说明
提取日期date中符合指定时间单位datepart的值。
参数说明
date:必填。DATE、DATETIME或TIMESTAMP类型。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。
datepart:必填。STRING类型常量,支持扩展的日期格式。
此字段的取值遵循STRING与DATETIME类型转换的约定,即
yyyy
表示年,mm
表示月,dd
表示天。关于类型转换的规则,详情请参见数据类型转换。该字段也支持扩展的日期格式:年-year
、月-month
或-mon
、日-day
和小时-hour
。
返回值说明
返回BIGINT类型。返回规则如下:
date非DATE、DATETIME或TIMESTAMP类型时,返回报错。
date值为NULL时,返回报错。
datepart值为NULL时,返回NULL。
示例
静态数据示例
--返回2013。 select datepart(datetime'2013-06-08 01:10:00', 'yyyy'); --返回6。 select datepart(datetime'2013-06-08 01:10:00', 'mm'); --返回2013。 select datepart(date '2013-06-08', 'yyyy'); --开启2.0新类型。此命令需要与SQL语句一起提交。返回2013。 set odps.sql.type.system.odps2=true; select datepart(timestamp '2013-06-08 01:10:00', 'yyyy'); --返回2013。 set odps.sql.type.system.odps2=false; select datepart('2013-06-08 01:10:00', 'yyyy'); --返回NULL。 select datepart(date '2013-06-08', null);
表数据示例
基于示例数据,提取date1、datetime1和timestamp1中符合指定时间单位的值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, datepart(date1,'yyyy') as date1_datepart, datetime1, datepart(datetime1,'dd') as datetime1_datepart, timestamp1, datepart(timestamp1,'mm') as timestamp1_datepart from mf_date_fun_t;
返回结果如下。
+------------+----------------+---------------------+-------------------------+-------------------------------+---------------------+ | date1 | date1_datepart | datetime1 | datetime1_datepart | timestamp1 | timestamp1_datepart | +------------+----------------+---------------------+-------------------------+-------------------------------+---------------------+ | 2021-11-29 | 2021 | 2021-11-29 00:01:00 | 29 | 2021-01-11 00:00:00.123456789 | 1 | | 2021-11-28 | 2021 | 2021-11-28 00:02:00 | 28 | 2021-02-11 00:00:00.123456789 | 2 | | 2021-11-27 | 2021 | 2021-11-27 00:03:00 | 27 | 2021-03-11 00:00:00.123456789 | 3 | | 2021-11-26 | 2021 | 2021-11-26 00:04:00 | 26 | 2021-04-11 00:00:00.123456789 | 4 | | 2021-11-25 | 2021 | 2021-11-25 00:05:00 | 25 | 2021-05-11 00:00:00.123456789 | 5 | | 2021-11-24 | 2021 | 2021-11-24 00:06:00 | 24 | 2021-06-11 00:00:00.123456789 | 6 | | 2021-11-23 | 2021 | 2021-11-23 00:07:00 | 23 | 2021-07-11 00:00:00.123456789 | 7 | | 2021-11-22 | 2021 | 2021-11-22 00:08:00 | 22 | 2021-08-11 00:00:00.123456789 | 8 | | 2021-11-21 | 2021 | 2021-11-21 00:09:00 | 21 | 2021-09-11 00:00:00.123456789 | 9 | | 2021-11-20 | 2021 | 2021-11-20 00:10:00 | 20 | 2021-10-11 00:00:00.123456789 | 10 | +------------+----------------+---------------------+-------------------------+-------------------------------+---------------------+
DATETRUNC
命令格式
date|datetime datetrunc (date|datetime|timestamp <date>, string <datepart>)
命令说明
返回将日期date按照datepart指定的时间单位进行截取后的日期值。
参数说明
date:必填。DATE、DATETIME或TIMESTAMP类型。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。
datepart:必填。STRING类型常量。支持扩展的日期格式。
此字段的取值遵循STRING与DATETIME类型转换的约定,即
yyyy
表示年,mm
表示月,dd
表示天。关于类型转换的规则,详情请参见数据类型转换。该字段也支持扩展的日期格式:年-year
、月-month
或-mon
、日-day
和小时-hour
。
返回值说明
返回DATE或DATETIME类型,格式为
yyyy-mm-dd
或yyyy-mm-dd hh:mi:ss
。返回规则如下:date非DATE、DATETIME或TIMESTAMP类型时,返回报错。
date值为NULL时,返回报错。
datepart值为NULL时,返回NULL。
示例
静态数据示例
--返回2011-01-01 00:00:00。 select datetrunc(datetime'2011-12-07 16:28:46', 'yyyy'); --返回2011-12-01 00:00:00。 select datetrunc(datetime'2011-12-07 16:28:46', 'month'); --返回2011-12-07 00:00:00。 select datetrunc(datetime'2011-12-07 16:28:46', 'DD'); --返回2011-01-01。 select datetrunc(date '2011-12-07', 'yyyy'); --开启2.0新类型。此命令需要与SQL语句一起提交。返回2011-01-01 00:00:00.0。 set odps.sql.type.system.odps2=true; select datetrunc(timestamp '2011-12-07 16:28:46', 'yyyy'); --返回2011-01-01 00:00:00.0。 set odps.sql.type.system.odps2=false; select datetrunc('2011-12-07 16:28:46', 'yyyy'); --返回NULL。 select datetrunc(date '2011-12-07', null);
表数据示例
基于示例数据,返回date1、datetime1和timestamp1按照指定时间单位截取后的值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, datetrunc(date1,'yyyy') as date1_datetrunc, datetime1, datetrunc(datetime1,'dd') as datetime1_datetrunc, timestamp1, datetrunc(timestamp1,'mm') as timestamp1_datetrunc from mf_date_fun_t;
返回结果如下。
+------------+-----------------+---------------------+---------------------+-------------------------------+----------------------+ | date1 | date1_datetrunc | datetime1 | datetime1_datetrunc | timestamp1 | timestamp1_datetrunc | +------------+-----------------+---------------------+---------------------+-------------------------------+----------------------+ | 2021-11-29 | 2021-01-01 | 2021-11-29 00:01:00 | 2021-11-29 00:00:00 | 2021-01-11 00:00:00.123456789 | 2021-01-01 00:00:00 | | 2021-11-28 | 2021-01-01 | 2021-11-28 00:02:00 | 2021-11-28 00:00:00 | 2021-02-11 00:00:00.123456789 | 2021-02-01 00:00:00 | | 2021-11-27 | 2021-01-01 | 2021-11-27 00:03:00 | 2021-11-27 00:00:00 | 2021-03-11 00:00:00.123456789 | 2021-03-01 00:00:00 | | 2021-11-26 | 2021-01-01 | 2021-11-26 00:04:00 | 2021-11-26 00:00:00 | 2021-04-11 00:00:00.123456789 | 2021-04-01 00:00:00 | | 2021-11-25 | 2021-01-01 | 2021-11-25 00:05:00 | 2021-11-25 00:00:00 | 2021-05-11 00:00:00.123456789 | 2021-05-01 00:00:00 | | 2021-11-24 | 2021-01-01 | 2021-11-24 00:06:00 | 2021-11-24 00:00:00 | 2021-06-11 00:00:00.123456789 | 2021-06-01 00:00:00 | | 2021-11-23 | 2021-01-01 | 2021-11-23 00:07:00 | 2021-11-23 00:00:00 | 2021-07-11 00:00:00.123456789 | 2021-07-01 00:00:00 | | 2021-11-22 | 2021-01-01 | 2021-11-22 00:08:00 | 2021-11-22 00:00:00 | 2021-08-11 00:00:00.123456789 | 2021-08-01 00:00:00 | | 2021-11-21 | 2021-01-01 | 2021-11-21 00:09:00 | 2021-11-21 00:00:00 | 2021-09-11 00:00:00.123456789 | 2021-09-01 00:00:00 | | 2021-11-20 | 2021-01-01 | 2021-11-20 00:10:00 | 2021-11-20 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-10-01 00:00:00 | +------------+-----------------+---------------------+---------------------+-------------------------------+----------------------+
DAY
命令格式
int day(datetime|timestamp|date|string <date>)
命令说明
返回一个日期的天。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回1。 select day('2014-09-01'); --返回NULL。 select day('20140901'); --返回NULL。 select day(null);
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期对应的天,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, day(date1) as date1_day, datetime1, day(datetime1) as datetime1_day, timestamp1, day(timestamp1) as timestamp1_day, date3, day(date3) as date3_day from mf_date_fun_t;
返回结果如下。
+------------+-----------+---------------------+---------------+-------------------------------+----------------+------------+-----------+ | date1 | date1_day | datetime1 | datetime1_day | timestamp1 | timestamp1_day | date3 | date3_day | +------------+-----------+---------------------+---------------+-------------------------------+----------------+------------+-----------+ | 2021-11-29 | 29 | 2021-11-29 00:01:00 | 29 | 2021-01-11 00:00:00.123456789 | 11 | 2021-11-20 | 20 | | 2021-11-28 | 28 | 2021-11-28 00:02:00 | 28 | 2021-02-11 00:00:00.123456789 | 11 | 2021-11-21 | 21 | | 2021-11-27 | 27 | 2021-11-27 00:03:00 | 27 | 2021-03-11 00:00:00.123456789 | 11 | 2021-11-22 | 22 | | 2021-11-26 | 26 | 2021-11-26 00:04:00 | 26 | 2021-04-11 00:00:00.123456789 | 11 | 2021-11-23 | 23 | | 2021-11-25 | 25 | 2021-11-25 00:05:00 | 25 | 2021-05-11 00:00:00.123456789 | 11 | 2021-11-24 | 24 | | 2021-11-24 | 24 | 2021-11-24 00:06:00 | 24 | 2021-06-11 00:00:00.123456789 | 11 | 2021-11-25 | 25 | | 2021-11-23 | 23 | 2021-11-23 00:07:00 | 23 | 2021-07-11 00:00:00.123456789 | 11 | 2021-11-26 | 26 | | 2021-11-22 | 22 | 2021-11-22 00:08:00 | 22 | 2021-08-11 00:00:00.123456789 | 11 | 2021-11-27 | 27 | | 2021-11-21 | 21 | 2021-11-21 00:09:00 | 21 | 2021-09-11 00:00:00.123456789 | 11 | 2021-11-28 | 28 | | 2021-11-20 | 20 | 2021-11-20 00:10:00 | 20 | 2021-10-11 00:00:00.123456789 | 11 | 2021-11-29 | 29 | +------------+-----------+---------------------+---------------+-------------------------------+----------------+------------+-----------+
DAYOFMONTH
命令格式
int dayofmonth(datetime|timestamp|date|string <date>)
命令说明
返回日期日部分的值。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回1。 select dayofmonth('2014-09-01'); --返回NULL。 select dayofmonth('20140901'); --返回NULL。 select dayofmonth(null);
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期日部分的值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, dayofmonth(date1) as date1_dayofmonth, datetime1, dayofmonth(datetime1) as datetime1_dayofmonth, timestamp1, dayofmonth(timestamp1) as timestamp1_dayofmonth, date3, dayofmonth(date3) as date3_dayofmonth from mf_date_fun_t;
返回结果如下。
+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+ | date1 | date1_dayofmonth | datetime1 | datetime1_dayofmonth | timestamp1 | timestamp1_dayofmonth | date3 | date3_dayofmonth | +------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+ | 2021-11-29 | 29 | 2021-11-29 00:01:00 | 29 | 2021-01-11 00:00:00.123456789 | 11 | 2021-11-20 | 20 | | 2021-11-28 | 28 | 2021-11-28 00:02:00 | 28 | 2021-02-11 00:00:00.123456789 | 11 | 2021-11-21 | 21 | | 2021-11-27 | 27 | 2021-11-27 00:03:00 | 27 | 2021-03-11 00:00:00.123456789 | 11 | 2021-11-22 | 22 | | 2021-11-26 | 26 | 2021-11-26 00:04:00 | 26 | 2021-04-11 00:00:00.123456789 | 11 | 2021-11-23 | 23 | | 2021-11-25 | 25 | 2021-11-25 00:05:00 | 25 | 2021-05-11 00:00:00.123456789 | 11 | 2021-11-24 | 24 | | 2021-11-24 | 24 | 2021-11-24 00:06:00 | 24 | 2021-06-11 00:00:00.123456789 | 11 | 2021-11-25 | 25 | | 2021-11-23 | 23 | 2021-11-23 00:07:00 | 23 | 2021-07-11 00:00:00.123456789 | 11 | 2021-11-26 | 26 | | 2021-11-22 | 22 | 2021-11-22 00:08:00 | 22 | 2021-08-11 00:00:00.123456789 | 11 | 2021-11-27 | 27 | | 2021-11-21 | 21 | 2021-11-21 00:09:00 | 21 | 2021-09-11 00:00:00.123456789 | 11 | 2021-11-28 | 28 | | 2021-11-20 | 20 | 2021-11-20 00:10:00 | 20 | 2021-10-11 00:00:00.123456789 | 11 | 2021-11-29 | 29 | +------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+
DAYOFWEEK
命令格式
int dayofweek(datetime|timestamp|date|string <date>)
命令说明
返回日期的星期值。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
返回值的取值范围为1~7,与星期的对应关系为1=Sunday, 2=Monday,...,7=Saturday。
示例
--返回5,即Thursday。 SELECT dayofweek('2009-07-30');
DAYOFYEAR
命令格式
int dayofyear(datetime|timestamp|date|string <date>)
命令说明
返回日期是当年中的第几天。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
--返回100。 SELECT dayofyear('2016-04-09');
EXTRACT
命令格式
int extract(<datepart> from date|datetime|timestamp <date>)
命令说明
提取日期date中指定单位datepart的部分。此函数为MaxCompute 2.0扩展函数。
参数说明
datepart:必填。支持YEAR、MONTH、DAY、HOUR或MINUTE等时间取值。
date:必填。DATE、DATETIME、TIMESTAMP或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。
返回值
返回INT类型。返回规则如下:
datepart非YEAR、MONTH、DAY、HOUR或MINUTE等时间取值时,返回报错。
datepart值为NULL时,返回报错。
date非DATE、DATETIME、TIMESTAMP或STRING类型或为NULL时,返回NULL。
示例
静态数据示例
--开启2.0新类型。此命令需要与SQL语句一起提交。 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 | +------+-------+------+------+--------+ --返回NULL。 select extract(year from null);
表数据示例
基于示例数据,提取timestamp1列的指定部分,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select timestamp1, extract(year from timestamp1) year, timestamp2, extract(month from timestamp2) month from mf_date_fun_t;
返回结果如下。
+-------------------------------+------+-------------------------------+-------+ | timestamp1 | year | timestamp2 | month | +-------------------------------+------+-------------------------------+-------+ | 2021-01-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-02-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-03-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-04-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-05-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-06-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-07-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-08-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-09-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | | 2021-10-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10 | +-------------------------------+------+-------------------------------+-------+
FROM_UNIXTIME
命令格式
datetime from_unixtime(bigint <unixtime>)
命令说明
将数字型的UNIX时间日期值unixtime转为日期值。
参数说明
unixtime:必填。BIGINT类型,秒数,UNIX格式的日期时间值,取值范围:[-62167305600, 253402387200]。
说明如果输入为STRING、DOUBLE或DECIMAL类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为BIGINT后参与运算。
返回值说明
返回DATETIME类型,格式为
yyyy-mm-dd hh:mi:ss
。unixtime值为NULL时,返回NULL。说明Hive兼容模式下(
set odps.sql.hive.compatible=true;
):如果输入参数是STRING类型,返回值也是STRING类型。
可以传入第二个参数,来决定输出的日期时间格式。如果设置格式为“yyyy-MM-dd” 或者“YYYY-MM-dd” ,其中yyyy代表为年,YYYY代表为周年,请谨慎使用周年方式,周年格式可能会与实际年份不一致。
示例
静态数据示例
--返回1973-11-30 05:33:09。 select from_unixtime(123456789); --返回1973-11-30 05:33:09。 set odps.sql.type.system.odps2=false; select from_unixtime('123456789'); --返回NULL。 select from_unixtime(null);
表数据示例
基于示例数据,将date4转换为日期值,命令示例如下。
select date4, from_unixtime(date4) as date4_from_unixtime from mf_date_fun_t;
返回结果如下。
+------------+---------------------+ | date4 | date4_from_unixtime | +------------+---------------------+ | 123456780 | 1973-11-30 05:33:00 | | 123456781 | 1973-11-30 05:33:01 | | 123456782 | 1973-11-30 05:33:02 | | 123456783 | 1973-11-30 05:33:03 | | 123456784 | 1973-11-30 05:33:04 | | 123456785 | 1973-11-30 05:33:05 | | 123456786 | 1973-11-30 05:33:06 | | 123456787 | 1973-11-30 05:33:07 | | 123456788 | 1973-11-30 05:33:08 | | 123456789 | 1973-11-30 05:33:09 | +------------+---------------------+
FROM_UTC_TIMESTAMP
命令格式
timestamp from_utc_timestamp({any primitive type}*, string <timezone>)
命令说明
将一个UTC时区的时间戳转换成一个指定时区的时间戳,即将一个UTC时区的时间戳按照指定的时区显示。此函数为MaxCompute 2.0扩展函数。
重要FROM_UTC_TIMESTAMP函数还会受到Project级别flag(
odps.sql.timezone
)配置的影响。假如odps.sql.timezone=Asia/Shanghai
即东八区时区,会比UTC时区多八个小时,所以会在FROM_UTC_TIMESTAMP函数转换后的基础上再加八个小时显示。示例:FROM_UTC_TIMESTAMP(0, 'Asia/Shanghai')
的逻辑是把UTC-0时区的0,向北京时区偏移,得到的是UTC-0时间为0 + 8 * 3600 = 28800
。但由于Project配置了odps.sql.timezone=Asia/Shanghai
,会导致继续偏移八小时,最终显示为1970-01-01 16:00:00
。参数说明
{any primitive type}*:必填。时间戳,支持TIMESTAMP、DATETIME、TINYINT、SMALLINT、INT或BIGINT数据类型。如果该参数为TINYINT、SMALLINT、INT或BIGINT数据类型,则单位为毫秒。
timezone:必填。指定需要转换的目标时区。
说明您可直接在搜索引擎中查找相关时区列表详情。
返回值说明
返回TIMESTAMP类型,格式为
yyyy-mm-dd hh:mi:ss.ff3
。返回规则如下:{any primitive type}*非TIMESTAMP、DATETIME、TINYINT、SMALLINT、INT或BIGINT时,返回报错。
{any primitive type}*值为NULL时,返回报错。
timezone值为NULL时,返回NULL。
示例
静态数据示例
--输入参数为毫秒(ms),返回2017-08-01 04:24:00.0。 select from_utc_timestamp(1501557840000, 'PST'); --返回1970-01-30 08:00:00.0。 select from_utc_timestamp('1970-01-30 16:00:00','PST'); --返回1970-01-29 16:00:00.0。 select from_utc_timestamp('1970-01-30','PST'); --开启2.0新类型。此命令需要与SQL语句一起提交。返回2011-12-25 17:00:00.123。 set odps.sql.type.system.odps2=true; select from_utc_timestamp(timestamp '2011-12-25 09:00:00.123456', 'Asia/Shanghai'); --开启2.0新类型。此命令需要与SQL语句一起提交。返回2011-12-25 01:55:00.0。 set odps.sql.type.system.odps2=true; select from_utc_timestamp(timestamp '2011-12-25 06:55:00', 'America/Toronto'); --返回NULL。 select from_utc_timestamp('1970-01-30',null);
表数据示例
基于示例数据,将datetime1和timestamp1列转换为指定时区的时间戳,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select datetime1, from_utc_timestamp(datetime1,'PST') pst, timestamp1, from_utc_timestamp(timestamp1,'Asia/Shanghai') asia from mf_date_fun_t;
返回结果如下。
+---------------------+---------------------+-------------------------------+-------------------------------+ | datetime1 | pst | timestamp1 | asia | +---------------------+---------------------+-------------------------------+-------------------------------+ | 2021-11-29 00:01:00 | 2021-11-28 16:01:00 | 2021-01-11 00:00:00.123456789 | 2021-01-11 08:00:00.123456789 | | 2021-11-28 00:02:00 | 2021-11-27 16:02:00 | 2021-02-11 00:00:00.123456789 | 2021-02-11 08:00:00.123456789 | | 2021-11-27 00:03:00 | 2021-11-26 16:03:00 | 2021-03-11 00:00:00.123456789 | 2021-03-11 08:00:00.123456789 | | 2021-11-26 00:04:00 | 2021-11-25 16:04:00 | 2021-04-11 00:00:00.123456789 | 2021-04-11 08:00:00.123456789 | | 2021-11-25 00:05:00 | 2021-11-24 16:05:00 | 2021-05-11 00:00:00.123456789 | 2021-05-11 08:00:00.123456789 | | 2021-11-24 00:06:00 | 2021-11-23 16:06:00 | 2021-06-11 00:00:00.123456789 | 2021-06-11 08:00:00.123456789 | | 2021-11-23 00:07:00 | 2021-11-22 16:07:00 | 2021-07-11 00:00:00.123456789 | 2021-07-11 08:00:00.123456789 | | 2021-11-22 00:08:00 | 2021-11-21 16:08:00 | 2021-08-11 00:00:00.123456789 | 2021-08-11 08:00:00.123456789 | | 2021-11-21 00:09:00 | 2021-11-20 16:09:00 | 2021-09-11 00:00:00.123456789 | 2021-09-11 08:00:00.123456789 | | 2021-11-20 00:10:00 | 2021-11-19 16:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-11 08:00:00.123456789 | +---------------------+---------------------+-------------------------------+-------------------------------+
GETDATE
命令格式
datetime getdate()
命令说明
获取当前系统时间。使用东八区时间作为MaxCompute标准时间。
返回值说明
返回当前日期和时间,DATETIME类型。
说明在MaxCompute SQL中,
getdate
总是返回一个固定的值。返回结果会是MaxCompute SQL执行期间的任意时间,时间精度精确到秒。如果开启了新数据类型2.0,时间精度精确到毫秒。
HOUR
命令格式
int hour(datetime|timestamp|string <date>)
命令说明
返回日期小时部分的值。
参数说明
date:必填。DATETIME、TIMESTAMP或STRING类型日期值,格式为
yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。此函数为MaxCompute 2.0扩展函数。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回12。 select hour('2014-09-01 12:00:00'); --返回12。 select hour('12:00:00'); --返回NULL。 select hour('20140901120000'); --返回NULL。 select hour(null);
表数据示例
基于示例数据,返回datetime1和timestamp1小时部分的值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select datetime1, hour(datetime1) as datetime1_hour, timestamp1, hour(timestamp1) as timestamp1_hour from mf_date_fun_t;
返回结果如下。
+---------------------+----------------+-------------------------------+-----------------+ | datetime1 | datetime1_hour | timestamp1 | timestamp1_hour | +---------------------+----------------+-------------------------------+-----------------+ | 2021-11-29 00:01:00 | 0 | 2021-01-11 00:00:00.123456789 | 0 | | 2021-11-28 00:02:00 | 0 | 2021-02-11 00:00:00.123456789 | 0 | | 2021-11-27 00:03:00 | 0 | 2021-03-11 00:00:00.123456789 | 0 | | 2021-11-26 00:04:00 | 0 | 2021-04-11 00:00:00.123456789 | 0 | | 2021-11-25 00:05:00 | 0 | 2021-05-11 00:00:00.123456789 | 0 | | 2021-11-24 00:06:00 | 0 | 2021-06-11 00:00:00.123456789 | 0 | | 2021-11-23 00:07:00 | 0 | 2021-07-11 00:00:00.123456789 | 0 | | 2021-11-22 00:08:00 | 0 | 2021-08-11 00:00:00.123456789 | 0 | | 2021-11-21 00:09:00 | 0 | 2021-09-11 00:00:00.123456789 | 0 | | 2021-11-20 00:10:00 | 0 | 2021-10-11 00:00:00.123456789 | 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类型。date或format值为NULL时,返回NULL。
示例
静态数据示例
--返回True。 select isdate('2021-10-11','yyyy-mm-dd'); --返回false。 set odps.sql.type.system.odps2=false; select isdate(1678952314,'yyyy-mm-dd');
表数据示例
基于示例数据,判断date3是否能转换为指定格式的日期值,命令示例如下。
select date3, isdate(date3,'yyyy-mm-dd') as date3_isdate from mf_date_fun_t;
返回结果如下。
+------------+--------------+ | date3 | date3_isdate | +------------+--------------+ | 2021-11-20 | true | | 2021-11-21 | true | | 2021-11-22 | true | | 2021-11-23 | true | | 2021-11-24 | true | | 2021-11-25 | true | | 2021-11-26 | true | | 2021-11-27 | true | | 2021-11-28 | true | | 2021-11-29 | true | +------------+--------------+
LAST_DAY
命令格式
string last_day(date|datetime|timestamp|string <date>)
命令说明
返回该日期所在月份的最后一天日期。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATE、DATETIME、TIMESTAMP或STRING类型日期值。取值为STRING类型格式时,至少要包含
yyyy-mm-dd
且不含多余的字符串。返回值说明
返回STRING类型的日期值,格式为
yyyy-mm-dd
。返回规则如下:date非DATE、DATETIME、TIMESTAMP或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回报错。
示例
静态数据示例
--返回2017-03-31。 select last_day('2017-03-04'); --返回2017-07-31。 select last_day('2017-07-04 11:40:00'); --返回NULL。 select last_day('20170304');
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期所在月的最后一天,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, last_day(date1) as date1_lastday, datetime1, last_day(datetime1) as datetime1_lastday, timestamp1, last_day(timestamp1) as timestamp1_lastday, date3, last_day(date3) as date3_lastday from mf_date_fun_t;
返回结果如下。
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+ | date1 | date1_lastday | datetime1 | datetime1_lastday | timestamp1 | timestamp1_lastday | date3 | date3_lastday | +------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+ | 2021-11-29 | 2021-11-30 | 2021-11-29 00:01:00 | 2021-11-30 | 2021-01-11 00:00:00.123456789 | 2021-01-31 | 2021-11-20 | 2021-11-30 | | 2021-11-28 | 2021-11-30 | 2021-11-28 00:02:00 | 2021-11-30 | 2021-02-11 00:00:00.123456789 | 2021-02-28 | 2021-11-21 | 2021-11-30 | | 2021-11-27 | 2021-11-30 | 2021-11-27 00:03:00 | 2021-11-30 | 2021-03-11 00:00:00.123456789 | 2021-03-31 | 2021-11-22 | 2021-11-30 | | 2021-11-26 | 2021-11-30 | 2021-11-26 00:04:00 | 2021-11-30 | 2021-04-11 00:00:00.123456789 | 2021-04-30 | 2021-11-23 | 2021-11-30 | | 2021-11-25 | 2021-11-30 | 2021-11-25 00:05:00 | 2021-11-30 | 2021-05-11 00:00:00.123456789 | 2021-05-31 | 2021-11-24 | 2021-11-30 | | 2021-11-24 | 2021-11-30 | 2021-11-24 00:06:00 | 2021-11-30 | 2021-06-11 00:00:00.123456789 | 2021-06-30 | 2021-11-25 | 2021-11-30 | | 2021-11-23 | 2021-11-30 | 2021-11-23 00:07:00 | 2021-11-30 | 2021-07-11 00:00:00.123456789 | 2021-07-31 | 2021-11-26 | 2021-11-30 | | 2021-11-22 | 2021-11-30 | 2021-11-22 00:08:00 | 2021-11-30 | 2021-08-11 00:00:00.123456789 | 2021-08-31 | 2021-11-27 | 2021-11-30 | | 2021-11-21 | 2021-11-30 | 2021-11-21 00:09:00 | 2021-11-30 | 2021-09-11 00:00:00.123456789 | 2021-09-30 | 2021-11-28 | 2021-11-30 | | 2021-11-20 | 2021-11-30 | 2021-11-20 00:10:00 | 2021-11-30 | 2021-10-11 00:00:00.123456789 | 2021-10-31 | 2021-11-29 | 2021-11-30 | +------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
LASTDAY
命令格式
datetime lastday(datetime <date>)
命令说明
取date所在月的最后一天,截取到天,时分秒部分为
00:00:00
。参数说明
date:DATETIME类型日期值,格式为
yyyy-mm-dd hh:mi:ss
。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。返回值说明
返回DATETIME类型,格式为
yyyy-mm-dd hh:mi:ss
。返回规则如下:date非DATETIME或STRING类型,或格式不符合要求时,会返回报错。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回2013-06-30 00:00:00。 select lastday (datetime '2013-06-08 01:10:00'); --返回2013-06-30 00:00:00。 set odps.sql.type.system.odps2=false; select lastday ('2013-06-08 01:10:00'); --返回NULL。 select lastday (null);
表数据示例
基于示例数据,返回datetime1列日期所在月的最后一天,命令示例如下。
select datetime1, lastday(datetime1) as datetime1_lastday from mf_date_fun_t;
返回结果如下。
+---------------------+---------------------+ | datetime1 | datetime1_lastday | +---------------------+---------------------+ | 2021-11-29 00:01:00 | 2021-11-30 00:00:00 | | 2021-11-28 00:02:00 | 2021-11-30 00:00:00 | | 2021-11-27 00:03:00 | 2021-11-30 00:00:00 | | 2021-11-26 00:04:00 | 2021-11-30 00:00:00 | | 2021-11-25 00:05:00 | 2021-11-30 00:00:00 | | 2021-11-24 00:06:00 | 2021-11-30 00:00:00 | | 2021-11-23 00:07:00 | 2021-11-30 00:00:00 | | 2021-11-22 00:08:00 | 2021-11-30 00:00:00 | | 2021-11-21 00:09:00 | 2021-11-30 00:00:00 | | 2021-11-20 00:10:00 | 2021-11-30 00:00:00 | +---------------------+---------------------+
MINUTE
命令格式
int minute(datetime|timestamp|string <date>)
命令说明
返回日期分钟部分的值。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP或STRING类型日期值,格式为
yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回30。 select minute('2014-09-01 12:30:00'); --返回30。 select minute('12:30:00'); --返回NULL。 select minute('20140901120000'); --返回NULL。 select minute(null);
表数据示例
基于示例数据,返回datetime1和timestamp1分钟部分的值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select datetime1, minute(datetime1) as datetime1_minute, timestamp1, minute(timestamp1) as timestamp1_minute from mf_date_fun_t;
返回结果如下。
+---------------------+------------------+-------------------------------+-------------------+ | datetime1 | datetime1_minute | timestamp1 | timestamp1_minute | +---------------------+------------------+-------------------------------+-------------------+ | 2021-11-29 00:01:00 | 1 | 2021-01-11 00:00:00.123456789 | 0 | | 2021-11-28 00:02:00 | 2 | 2021-02-11 00:00:00.123456789 | 0 | | 2021-11-27 00:03:00 | 3 | 2021-03-11 00:00:00.123456789 | 0 | | 2021-11-26 00:04:00 | 4 | 2021-04-11 00:00:00.123456789 | 0 | | 2021-11-25 00:05:00 | 5 | 2021-05-11 00:00:00.123456789 | 0 | | 2021-11-24 00:06:00 | 6 | 2021-06-11 00:00:00.123456789 | 0 | | 2021-11-23 00:07:00 | 7 | 2021-07-11 00:00:00.123456789 | 0 | | 2021-11-22 00:08:00 | 8 | 2021-08-11 00:00:00.123456789 | 0 | | 2021-11-21 00:09:00 | 9 | 2021-09-11 00:00:00.123456789 | 0 | | 2021-11-20 00:10:00 | 10 | 2021-10-11 00:00:00.123456789 | 0 | +---------------------+------------------+-------------------------------+-------------------+
MONTH
命令格式
int month(datetime|timestamp|date|string <date>)
命令说明
返回一个日期的月份。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回9。 select month('2014-09-01'); --返回NULL。 select month('20140901'); --返回NULL。 select month(null);
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期所属月份,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, month(date1) as date1_month, datetime1, month(datetime1) as datetime1_month, timestamp1, month(timestamp1) as timestamp1_month, date3, month(date3) as date3_month from mf_date_fun_t;
返回结果如下。
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+ | date1 | date1_month | datetime1 | datetime1_month | timestamp1 | timestamp1_month | date3 | date3_month | +------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+ | 2021-11-29 | 11 | 2021-11-29 00:01:00 | 11 | 2021-01-11 00:00:00.123456789 | 1 | 2021-11-20 | 11 | | 2021-11-28 | 11 | 2021-11-28 00:02:00 | 11 | 2021-02-11 00:00:00.123456789 | 2 | 2021-11-21 | 11 | | 2021-11-27 | 11 | 2021-11-27 00:03:00 | 11 | 2021-03-11 00:00:00.123456789 | 3 | 2021-11-22 | 11 | | 2021-11-26 | 11 | 2021-11-26 00:04:00 | 11 | 2021-04-11 00:00:00.123456789 | 4 | 2021-11-23 | 11 | | 2021-11-25 | 11 | 2021-11-25 00:05:00 | 11 | 2021-05-11 00:00:00.123456789 | 5 | 2021-11-24 | 11 | | 2021-11-24 | 11 | 2021-11-24 00:06:00 | 11 | 2021-06-11 00:00:00.123456789 | 6 | 2021-11-25 | 11 | | 2021-11-23 | 11 | 2021-11-23 00:07:00 | 11 | 2021-07-11 00:00:00.123456789 | 7 | 2021-11-26 | 11 | | 2021-11-22 | 11 | 2021-11-22 00:08:00 | 11 | 2021-08-11 00:00:00.123456789 | 8 | 2021-11-27 | 11 | | 2021-11-21 | 11 | 2021-11-21 00:09:00 | 11 | 2021-09-11 00:00:00.123456789 | 9 | 2021-11-28 | 11 | | 2021-11-20 | 11 | 2021-11-20 00:10:00 | 11 | 2021-10-11 00:00:00.123456789 | 10 | 2021-11-29 | 11 | +------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
MONTHS_BETWEEN
命令格式
double months_between(datetime|timestamp|date|string <date1>, datetime|timestamp|date|string <date2>)
命令说明
返回日期date1和date2之间的月数。此函数为MaxCompute 2.0扩展函数。
参数说明
date1、date2:必填。DATETIME、TIMESTAMP、DATE或STRING类型,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回DOUBLE类型。返回规则如下:
当date1晚于date2时,返回值为正。当date2晚于date1时,返回值为负。
当date1和date2分别对应两个月的最后一天,返回整数月;否则计算方式为date1减去date2的天数除以31天。
date1或date2值为NULL时,返回NULL。
示例
静态数据示例
--返回3.9495967741935485。 select months_between('1997-02-28 10:30:00', '1996-10-30'); --返回-3.9495967741935485。 select months_between('1996-10-30','1997-02-28 10:30:00' ); --返回-3.0。 select months_between('1996-09-30','1996-12-31'); --返回NULL。 select months_between('1996-09-30',null);
表数据示例
基于示例数据,计算timestamp1和timestamp2列之间的月数,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select timestamp1, timestamp2, months_between(timestamp1, timestamp2) from mf_date_fun_t;
返回结果如下。
+-------------------------------+-------------------------------+------------+ | timestamp1 | timestamp2 | _c2 | +-------------------------------+-------------------------------+------------+ | 2021-01-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -9.0 | | 2021-02-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -8.0 | | 2021-03-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -7.0 | | 2021-04-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -6.0 | | 2021-05-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -5.0 | | 2021-06-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -4.0 | | 2021-07-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -3.0 | | 2021-08-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -2.0 | | 2021-09-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -1.0 | | 2021-10-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | 0.0 | +------------+------------+------------+
NEXT_DAY
命令格式
string next_day(timestamp|date|datetime|string <startdate>, string <week>)
命令说明
返回大于指定日期startdate并且与week相匹配的第一个日期,即下周几的具体日期。此函数为MaxCompute 2.0扩展函数。
参数说明
startdate:必填。TIMESTAMP、DATE、DATETIME或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。week:必填。STRING类型,一个星期前2个或3个字母,或者一个星期的全名。例如MO、TUE或FRIDAY。
返回值说明
返回STRING类型的日期值,格式为
yyyy-mm-dd
。返回规则如下:date非TIMESTAMP、DATE、DATETIME或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回报错。
week值为NULL时,返回NULL。
示例
静态数据示例
--返回2017-08-08。 select next_day('2017-08-01','TU'); --返回2017-08-08。 select next_day('2017-08-01 23:34:00','TU'); --返回NULL。 select next_day('20170801','TU'); --返回NULL。 select next_day('2017-08-01 23:34:00',null);
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列对应下周的具体日期,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, next_day(date1,'MON') as date1_next_day, datetime1, next_day(datetime1,'TUE') as datetime1_next_day, timestamp1, next_day(timestamp1,'WED') as timestamp1_next_day, date3, next_day(date3,'THU') as date3_next_day from mf_date_fun_t;
返回结果如下。
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+ | date1 | date1_next_day | datetime1 | datetime1_next_day | timestamp1 | timestamp1_next_day | date3 | date3_next_day | +------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+ | 2021-11-29 | 2021-12-06 | 2021-11-29 00:01:00 | 2021-11-30 | 2021-01-11 00:00:00.123456789 | 2021-01-13 | 2021-11-20 | 2021-11-25 | | 2021-11-28 | 2021-11-29 | 2021-11-28 00:02:00 | 2021-11-30 | 2021-02-11 00:00:00.123456789 | 2021-02-17 | 2021-11-21 | 2021-11-25 | | 2021-11-27 | 2021-11-29 | 2021-11-27 00:03:00 | 2021-11-30 | 2021-03-11 00:00:00.123456789 | 2021-03-17 | 2021-11-22 | 2021-11-25 | | 2021-11-26 | 2021-11-29 | 2021-11-26 00:04:00 | 2021-11-30 | 2021-04-11 00:00:00.123456789 | 2021-04-14 | 2021-11-23 | 2021-11-25 | | 2021-11-25 | 2021-11-29 | 2021-11-25 00:05:00 | 2021-11-30 | 2021-05-11 00:00:00.123456789 | 2021-05-12 | 2021-11-24 | 2021-11-25 | | 2021-11-24 | 2021-11-29 | 2021-11-24 00:06:00 | 2021-11-30 | 2021-06-11 00:00:00.123456789 | 2021-06-16 | 2021-11-25 | 2021-12-02 | | 2021-11-23 | 2021-11-29 | 2021-11-23 00:07:00 | 2021-11-30 | 2021-07-11 00:00:00.123456789 | 2021-07-14 | 2021-11-26 | 2021-12-02 | | 2021-11-22 | 2021-11-29 | 2021-11-22 00:08:00 | 2021-11-23 | 2021-08-11 00:00:00.123456789 | 2021-08-18 | 2021-11-27 | 2021-12-02 | | 2021-11-21 | 2021-11-22 | 2021-11-21 00:09:00 | 2021-11-23 | 2021-09-11 00:00:00.123456789 | 2021-09-15 | 2021-11-28 | 2021-12-02 | | 2021-11-20 | 2021-11-22 | 2021-11-20 00:10:00 | 2021-11-23 | 2021-10-11 00:00:00.123456789 | 2021-10-13 | 2021-11-29 | 2021-12-02 | +------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+
NOW
命令格式
datetime NOW()
命令说明
返回当前系统日期与时间。
返回值说明
返回DATETIME类型,格式为
yyyy-mm-dd hh:mi:ss.SSS
。示例
不指定格式,返回毫秒位可能为1位、2位或者3位:
select now();
返回结果如下:
+------+ | _c0 | +------+ | 2023-06-13 10:53:24.967 | +------+
指定时间格式:
select date_format(now(),'yyyy-MM-dd hh:mm:ss.SSS') ;
返回结果如下:
+-----+ | _c0 | +-----+ | 2023-06-13 10:53:53.899 | +-----+
QUARTER
命令格式
int quarter (datetime|timestamp|date|string <date>)
命令说明
返回一个日期的季度,范围是1~4。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回4。 select quarter('1970-11-12 10:00:00'); --返回4。 select quarter('1970-11-12'); --返回NULL。 select quarter(null);
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期所属季度,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, quarter(date1) as date1_quarter, datetime1, quarter(datetime1) as datetime1_quarter, timestamp1, quarter(timestamp1) as timestamp1_quarter, date3, quarter(date3) as date3_quarter from mf_date_fun_t;
返回结果如下。
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+ | date1 | date1_quarter | datetime1 | datetime1_quarter | timestamp1 | timestamp1_quarter | date3 | date3_quarter | +------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+ | 2021-11-29 | 4 | 2021-11-29 00:01:00 | 4 | 2021-01-11 00:00:00.123456789 | 1 | 2021-11-20 | 4 | | 2021-11-28 | 4 | 2021-11-28 00:02:00 | 4 | 2021-02-11 00:00:00.123456789 | 1 | 2021-11-21 | 4 | | 2021-11-27 | 4 | 2021-11-27 00:03:00 | 4 | 2021-03-11 00:00:00.123456789 | 1 | 2021-11-22 | 4 | | 2021-11-26 | 4 | 2021-11-26 00:04:00 | 4 | 2021-04-11 00:00:00.123456789 | 2 | 2021-11-23 | 4 | | 2021-11-25 | 4 | 2021-11-25 00:05:00 | 4 | 2021-05-11 00:00:00.123456789 | 2 | 2021-11-24 | 4 | | 2021-11-24 | 4 | 2021-11-24 00:06:00 | 4 | 2021-06-11 00:00:00.123456789 | 2 | 2021-11-25 | 4 | | 2021-11-23 | 4 | 2021-11-23 00:07:00 | 4 | 2021-07-11 00:00:00.123456789 | 3 | 2021-11-26 | 4 | | 2021-11-22 | 4 | 2021-11-22 00:08:00 | 4 | 2021-08-11 00:00:00.123456789 | 3 | 2021-11-27 | 4 | | 2021-11-21 | 4 | 2021-11-21 00:09:00 | 4 | 2021-09-11 00:00:00.123456789 | 3 | 2021-11-28 | 4 | | 2021-11-20 | 4 | 2021-11-20 00:10:00 | 4 | 2021-10-11 00:00:00.123456789 | 4 | 2021-11-29 | 4 | +------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
SECOND
命令格式
int second(datetime|timestamp|string <date>)
命令说明
返回日期秒数部分的值。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP或STRING类型日期值,格式为
yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回45。 select second('2014-09-01 12:30:45'); --返回45。 select second('12:30:45'); --返回NULL。 select second('20140901123045'); --返回NULL。 select second(null);
表数据示例
基于示例数据,返回datetime1和timestamp1列秒数部分的值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; elect datetime1, second(datetime1) as datetime1_second, timestamp1, second(timestamp1) as timestamp1_second from mf_date_fun_t;
返回结果如下。
+---------------------+------------------+-------------------------------+-------------------+ | datetime1 | datetime1_second | timestamp1 | timestamp1_second | +---------------------+------------------+-------------------------------+-------------------+ | 2021-11-29 00:01:00 | 0 | 2021-01-11 00:00:00.123456789 | 0 | | 2021-11-28 00:02:00 | 0 | 2021-02-11 00:00:00.123456789 | 0 | | 2021-11-27 00:03:00 | 0 | 2021-03-11 00:00:00.123456789 | 0 | | 2021-11-26 00:04:00 | 0 | 2021-04-11 00:00:00.123456789 | 0 | | 2021-11-25 00:05:00 | 0 | 2021-05-11 00:00:00.123456789 | 0 | | 2021-11-24 00:06:00 | 0 | 2021-06-11 00:00:00.123456789 | 0 | | 2021-11-23 00:07:00 | 0 | 2021-07-11 00:00:00.123456789 | 0 | | 2021-11-22 00:08:00 | 0 | 2021-08-11 00:00:00.123456789 | 0 | | 2021-11-21 00:09:00 | 0 | 2021-09-11 00:00:00.123456789 | 0 | | 2021-11-20 00:10:00 | 0 | 2021-10-11 00:00:00.123456789 | 0 | +---------------------+------------------+-------------------------------+-------------------+
TO_CHAR
命令格式
string to_char(datetime <date>, string <format>)
命令说明
将日期类型date按照format指定的格式转成字符串。
参数说明
date:必填。DATETIME类型日期值,格式为
yyyy-mm-dd hh:mi:ss
。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,会隐式转换为DATETIME类型后参与运算。
format:必填。STRING类型常量。format中的日期格式部分会被替换成相应的数据,其他字符直接输出。
返回值说明
返回STRING类型。返回规则如下:
date非DATETIME或STRING类型时,返回报错。
date值为NULL时,返回报错。
format值为NULL时,返回NULL。
示例
静态数据示例
示例1
--需要注意在Windows环境的MaxCompute客户端运行时返回结果中的中文字符显示存在问题 SELECT TO_CHAR(DATETIME '2010-12-03 00:00:00','阿里金融yyyy-mm*dd');
返回结果如下。
+------------+ | _c0 | +------------+ | 阿里金融2010-12*03 | +------------+
示例2
SELECT TO_CHAR(DATETIME '2008-07-18 00:00:00','yyyymmdd');
返回结果如下。
+------------+ | _c0 | +------------+ | 20080718 | +------------+
示例3
SET odps.sql.type.system.odps2=false; SELECT TO_CHAR('2008-07-18 00:00:00', 'yyyymmdd');
返回结果如下。
+------------+ | _c0 | +------------+ | 20080718 | +------------+
示例4
--'阿里巴巴2010-12*3'无法转换为符合要求的格式,会引发异常,应该为'阿里巴巴2010-12*03'。 SELECT TO_CHAR(datetime'阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd');
示例5
--'20102401'不是标准日期值,引发异常,应该为'2010-01-24 00:00:00'。 SELECT TO_CHAR(datetime'20102401', 'yyyy');
示例6
SELECT TO_CHAR(datetime'2010-12-03 00:00:00', null);
返回结果如下。
+------------+ | _c0 | +------------+ | NULL | +------------+
表数据示例
基于示例数据,将datetime1列日期转换为满足指定格式的字符串,命令示例如下。
SELECT datetime1 ,TO_CHAR(datetime1,'yyyy-mm-dd') AS datetime1_to_char FROM mf_date_fun_t;
返回结果如下。
+---------------------+-------------------+ | datetime1 | datetime1_to_char | +---------------------+-------------------+ | 2021-11-29 00:01:00 | 2021-11-29 | | 2021-11-28 00:02:00 | 2021-11-28 | | 2021-11-27 00:03:00 | 2021-11-27 | | 2021-11-26 00:04:00 | 2021-11-26 | | 2021-11-25 00:05:00 | 2021-11-25 | | 2021-11-24 00:06:00 | 2021-11-24 | | 2021-11-23 00:07:00 | 2021-11-23 | | 2021-11-22 00:08:00 | 2021-11-22 | | 2021-11-21 00:09:00 | 2021-11-21 | | 2021-11-20 00:10:00 | 2021-11-20 | +---------------------+-------------------+
TO_DATE
命令格式
datetime|date to_date(string <date>[, string <format>])
命令说明
将date转换成符合format格式的日期值。
参数说明
date:必填。STRING类型,要转换的字符串格式的日期值。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算。该函数还支持ISO8601时间格式的字符串。
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位精度毫秒。
返回值说明
返回DATE或DATETIME类型。
当函数入参无format参数,且待转换的字符串格式为yyyy-mm-dd或
yyyy-mm-dd hh:mi:ss
时,返回DATE类型,格式为yyyy-mm-dd
;否则,返回NULL。当函数入参有format参数时,返回DATETIME类型,格式为
yyyy-mm-dd hh:mi:ss
。date或format值为NULL时,返回NULL。
示例
静态数据示例
--返回2010-12-03 00:00:00。 select to_date('阿里巴巴2010-12*03', '阿里巴巴yyyy-mm*dd'); --返回2008-07-18 00:00:00。 select to_date('20080718', 'yyyymmdd'); --返回2008-07-18 20:30:00。 select to_date('200807182030','yyyymmddhhmi'); --'2008718'无法转为标准日期值,引发异常,应该为'20080718'。 select to_date('2008718', 'yyyymmdd'); --'阿里巴巴2010-12*3'无法转为标准日期值,引发异常,应该为'阿里巴巴2010-12*03'。 select to_date('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd'); --'2010-24-01'无法转为标准日期值,引发异常,应该为'2010-01-24'。 select to_date('2010-24-01', 'yyyy-mm-dd'); --返回2018-10-30 15:13:12。 select to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3'); --返回NULL。 select to_date(null,'yyyymmdd hh-mi-ss.ff3'); --返回NULL。 select to_date('20181030 15-13-12.345',null); --ISO8601时间格式,返回2021-09-24 13:39:34。 select to_date('2021-09-24T13:39:34.119Z', 'yyyy-MM-ddThh:mi:ss.ff3Z'); --返回2021-09-24,数据类型为Date。 select to_date('2021-09-24'); --返回2021-09-24,数据类型为Date。 select to_date('2021-09-24 13:39:34'); --返回NULL。 select to_date('20210924');
表数据示例
基于示例数据,将date3列日期转换为满足指定格式的日期值,命令示例如下。
select date3, to_date(date3, 'yyyy-mm-dd') as date3_to_date from mf_date_fun_t;
返回结果如下。
+------------+---------------------+ | date3 | date3_to_date | +------------+---------------------+ | 2021-11-20 | 2021-11-20 00:00:00 | | 2021-11-21 | 2021-11-21 00:00:00 | | 2021-11-22 | 2021-11-22 00:00:00 | | 2021-11-23 | 2021-11-23 00:00:00 | | 2021-11-24 | 2021-11-24 00:00:00 | | 2021-11-25 | 2021-11-25 00:00:00 | | 2021-11-26 | 2021-11-26 00:00:00 | | 2021-11-27 | 2021-11-27 00:00:00 | | 2021-11-28 | 2021-11-28 00:00:00 | | 2021-11-29 | 2021-11-29 00:00:00 | +------------+---------------------+
TO_MILLIS
命令格式
bigint to_millis(datetime|timestamp <date>);
命令说明
将给定日期date转换为以毫秒为单位的UNIX时间戳。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME或TIMESTAMP类型日期值,格式为
yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。返回值说明
返回BIGINT类型。返回规则如下:
date非DATETIME或TIMESTAMP类型时,返回报错。
date值为NULL时,返回报错。
示例
静态数据示例
--返回1617174900000。 select to_millis(datetime '2021-03-31 15:15:00'); --返回1617174900000。 set odps.sql.type.system.odps2=true; select to_millis(timestamp '2021-03-31 15:15:00');
表数据示例
基于示例数据,将datetime1和timestamp1转换为以毫秒为单位的UNIX时间戳,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select datetime1, to_millis(datetime1) as datetime1_to_millis, timestamp1, to_millis(timestamp1) as timestamp1_to_millis from mf_date_fun_t;
返回结果如下。
+---------------------+---------------------+-------------------------------+----------------------+ | datetime1 | datetime1_to_millis | timestamp1 | timestamp1_to_millis | +---------------------+---------------------+-------------------------------+----------------------+ | 2021-11-29 00:01:00 | 1638115260000 | 2021-01-11 00:00:00.123456789 | 1610294400123 | | 2021-11-28 00:02:00 | 1638028920000 | 2021-02-11 00:00:00.123456789 | 1612972800123 | | 2021-11-27 00:03:00 | 1637942580000 | 2021-03-11 00:00:00.123456789 | 1615392000123 | | 2021-11-26 00:04:00 | 1637856240000 | 2021-04-11 00:00:00.123456789 | 1618070400123 | | 2021-11-25 00:05:00 | 1637769900000 | 2021-05-11 00:00:00.123456789 | 1620662400123 | | 2021-11-24 00:06:00 | 1637683560000 | 2021-06-11 00:00:00.123456789 | 1623340800123 | | 2021-11-23 00:07:00 | 1637597220000 | 2021-07-11 00:00:00.123456789 | 1625932800123 | | 2021-11-22 00:08:00 | 1637510880000 | 2021-08-11 00:00:00.123456789 | 1628611200123 | | 2021-11-21 00:09:00 | 1637424540000 | 2021-09-11 00:00:00.123456789 | 1631289600123 | | 2021-11-20 00:10:00 | 1637338200000 | 2021-10-11 00:00:00.123456789 | 1633881600123 | +---------------------+---------------------+-------------------------------+----------------------+
UNIX_TIMESTAMP
命令格式
bigint unix_timestamp(datetime|date|timestamp|string <date>)
命令说明
将日期date转化为整型的UNIX格式的日期时间值。
参数说明
date:必填。DATETIME、DATE、TIMESTAMP或STRING类型日期值,格式为
yyyy-mm-dd hh:mi:ss
、yyyy-mm-dd
或yyyy-mm-dd hh:mi:ss.ff3
。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0数据类型版本,则会隐式转换为DATETIME类型后参与运算。当使用MaxCompute2.0数据类型版本(2.0数据类型版本)时,隐式转换会失败,此时需要通过cast
函数转换或关闭新数据类型,例如unix_timestamp(cast(... as datetime))
。说明当入参为常量数据(非表数据)时,格式应写为:
DATETIME'yyyy-mm-dd hh:mi:ss'
、DATE'yyyy-mm-dd'
或TIMESTAMP'yyyy-mm-dd hh:mi:ss.ff3'
,无数据类型关键字时(即写为'yyyy-mm-dd hh:mi:ss'
),会识别为STRING类型。返回值说明
返回BIGINT类型,表示UNIX格式日期值。返回规则如下:
date非DATETIME、DATE、TIMESTAMP或STRING类型,或格式不符合要求时,返回报错或NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回1699585860。 SELECT unix_timestamp(DATETIME'2023-11-10 11:11:00'); --返回1699545600 set odps.sql.type.system.odps2=true; SELECT unix_timestamp(DATE'2023-11-10'); --返回1699585860 set odps.sql.type.system.odps2=true; SELECT unix_timestamp(TIMESTAMP'2023-11-10 11:11:00.123456789'); --返回1237518660。 set odps.sql.type.system.odps2=false; select unix_timestamp('2009-03-20 11:11:00'); --返回NULL。 select unix_timestamp(null);
表数据示例
基于示例数据,将date1、datetime1和timestamp1列日期转换为整型的UNIX格式的日期值,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, unix_timestamp(date1) as date1_unix_timestamp, datetime1, unix_timestamp(datetime1) as datetime1_unix_timestamp, timestamp1, unix_timestamp(timestamp1) as timestamp1_unix_timestamp from mf_date_fun_t;
返回结果如下。
+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+ | date1 | date1_unix_timestamp | datetime1 | datetime1_unix_timestamp | timestamp1 | timestamp1_unix_timestamp | +------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+ | 2021-11-29 | 1638115200 | 2021-11-29 00:01:00 | 1638115260 | 2021-01-11 00:00:00.123456789 | 1610294400 | | 2021-11-28 | 1638028800 | 2021-11-28 00:02:00 | 1638028920 | 2021-02-11 00:00:00.123456789 | 1612972800 | | 2021-11-27 | 1637942400 | 2021-11-27 00:03:00 | 1637942580 | 2021-03-11 00:00:00.123456789 | 1615392000 | | 2021-11-26 | 1637856000 | 2021-11-26 00:04:00 | 1637856240 | 2021-04-11 00:00:00.123456789 | 1618070400 | | 2021-11-25 | 1637769600 | 2021-11-25 00:05:00 | 1637769900 | 2021-05-11 00:00:00.123456789 | 1620662400 | | 2021-11-24 | 1637683200 | 2021-11-24 00:06:00 | 1637683560 | 2021-06-11 00:00:00.123456789 | 1623340800 | | 2021-11-23 | 1637596800 | 2021-11-23 00:07:00 | 1637597220 | 2021-07-11 00:00:00.123456789 | 1625932800 | | 2021-11-22 | 1637510400 | 2021-11-22 00:08:00 | 1637510880 | 2021-08-11 00:00:00.123456789 | 1628611200 | | 2021-11-21 | 1637424000 | 2021-11-21 00:09:00 | 1637424540 | 2021-09-11 00:00:00.123456789 | 1631289600 | | 2021-11-20 | 1637337600 | 2021-11-20 00:10:00 | 1637338200 | 2021-10-11 00:00:00.123456789 | 1633881600 | +------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+
WEEKDAY
命令格式
bigint weekday (datetime <date>)
命令说明
返回date日期是当前周的第几天。
参数说明
date:必填。DATETIME类型日期值。格式为
yyyy-mm-dd hh:mi:ss
。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。返回值说明
返回BIGINT类型。返回规则如下:
周一作为一周的第一天,返回值为0。其他日期依次递增,周日返回6。
date非DATETIME或STRING类型,或格式不符合要求时,返回报错。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回4。 select weekday (datetime '2009-03-20 11:11:00'); --返回4。 set odps.sql.type.system.odps2=false; select weekday ('2009-03-20 11:11:00'); --返回NULL。 select weekday (null);
表数据示例
基于示例数据,计算datetime1列日期为所在周的第几天,命令示例如下。
select datetime1, weekday(datetime1) as datetime1_weekday from mf_date_fun_t;
返回结果如下。
+---------------------+-------------------+ | datetime1 | datetime1_weekday | +---------------------+-------------------+ | 2021-11-29 00:01:00 | 0 | | 2021-11-28 00:02:00 | 6 | | 2021-11-27 00:03:00 | 5 | | 2021-11-26 00:04:00 | 4 | | 2021-11-25 00:05:00 | 3 | | 2021-11-24 00:06:00 | 2 | | 2021-11-23 00:07:00 | 1 | | 2021-11-22 00:08:00 | 0 | | 2021-11-21 00:09:00 | 6 | | 2021-11-20 00:10:00 | 5 | +---------------------+-------------------+
WEEKOFYEAR
命令格式
bigint weekofyear (datetime <date>)
命令说明
返回日期date位于那一年的第几周。周一作为一周的第一天。
说明这一周算上一年还是下一年,取决于这一周的大多数日期(4天以上)在哪一年。算在前一年,就是前一年的最后一周;算在后一年就是后一年的第一周。
参数说明
date:必填。DATETIME类型日期值。格式为
yyyy-mm-dd hh:mi:ss
。如果输入为STRING类型,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。返回值说明
返回BIGINT类型。返回规则如下:
date非DATETIME或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回1。虽然20141229属于2014年,但是这一周的大多数日期是在2015年,因此返回结果为1,表示是2015年的第一周。 select weekofyear(to_date("20141229", "yyyymmdd")); --返回1。 select weekofyear(to_date("20141231", "yyyymmdd")); --返回53。 select weekofyear(to_date("20151229", "yyyymmdd")); --返回48。 set odps.sql.type.system.odps2=false; select weekofyear('2021-11-29 00:01:00'); --返回NULL。 select weekofyear('20141231'); --返回NULL。 select weekofyear(null);
表数据示例
基于示例数据,计算datetime1列日期为所在年的第几周,命令示例如下。
select datetime1, weekofyear(datetime1) as datetime1_weekofyear from mf_date_fun_t;
返回结果如下。
+---------------------+----------------------+ | datetime1 | datetime1_weekofyear | +---------------------+----------------------+ | 2021-11-29 00:01:00 | 48 | | 2021-11-28 00:02:00 | 47 | | 2021-11-27 00:03:00 | 47 | | 2021-11-26 00:04:00 | 47 | | 2021-11-25 00:05:00 | 47 | | 2021-11-24 00:06:00 | 47 | | 2021-11-23 00:07:00 | 47 | | 2021-11-22 00:08:00 | 47 | | 2021-11-21 00:09:00 | 46 | | 2021-11-20 00:10:00 | 46 | +---------------------+----------------------+
YEAR
命令格式
int year(datetime|timestamp|date|string <date>)
命令说明
返回日期date的年。此函数为MaxCompute 2.0扩展函数。
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为
yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。返回值
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为NULL时,返回NULL。
示例
静态数据示例
--返回1970。 select year('1970-01-01 12:30:00'); --返回1970。 select year('1970-01-01'); --返回70。 select year('70-01-01'); --返回NULL。 select year('1970/03/09'); --返回NULL。 select year(null);
表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期所属年份,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。 set odps.sql.type.system.odps2=true; select date1, year(date1) as date1_year, datetime1, year(datetime1) as datetime1_year, timestamp1, year(timestamp1) as timestamp1_year, date3, year(date3) as date3_year from mf_date_fun_t;
返回结果如下。
+------------+------------+---------------------+----------------+-------------------------------+-----------------+------------+------------+ | date1 | date1_year | datetime1 | datetime1_year | timestamp1 | timestamp1_year | date3 | date3_year | +------------+------------+---------------------+----------------+-------------------------------+-----------------+------------+------------+ | 2021-11-29 | 2021 | 2021-11-29 00:01:00 | 2021 | 2021-01-11 00:00:00.123456789 | 2021 | 2021-11-20 | 2021 | | 2021-11-28 | 2021 | 2021-11-28 00:02:00 | 2021 | 2021-02-11 00:00:00.123456789 | 2021 | 2021-11-21 | 2021 | | 2021-11-27 | 2021 | 2021-11-27 00:03:00 | 2021 | 2021-03-11 00:00:00.123456789 | 2021 | 2021-11-22 | 2021 | | 2021-11-26 | 2021 | 2021-11-26 00:04:00 | 2021 | 2021-04-11 00:00:00.123456789 | 2021 | 2021-11-23 | 2021 | | 2021-11-25 | 2021 | 2021-11-25 00:05:00 | 2021 | 2021-05-11 00:00:00.123456789 | 2021 | 2021-11-24 | 2021 | | 2021-11-24 | 2021 | 2021-11-24 00:06:00 | 2021 | 2021-06-11 00:00:00.123456789 | 2021 | 2021-11-25 | 2021 | | 2021-11-23 | 2021 | 2021-11-23 00:07:00 | 2021 | 2021-07-11 00:00:00.123456789 | 2021 | 2021-11-26 | 2021 | | 2021-11-22 | 2021 | 2021-11-22 00:08:00 | 2021 | 2021-08-11 00:00:00.123456789 | 2021 | 2021-11-27 | 2021 | | 2021-11-21 | 2021 | 2021-11-21 00:09:00 | 2021 | 2021-09-11 00:00:00.123456789 | 2021 | 2021-11-28 | 2021 | | 2021-11-20 | 2021 | 2021-11-20 00:10:00 | 2021 | 2021-10-11 00:00:00.123456789 | 2021 | 2021-11-29 | 2021 | +------------+------------+---------------------+----------------+-------------------------------+-----------------+------------+------------+