一、日期函数
0、概述
格式：yyyy-mm-dd或yyyy-mm-dd hh:mi:ss
| 函数 | 功能 | |
|---|---|---|
| 增减日期 | DATEADD | 按照指定的单位和幅度修改日期值。 |
| 增减日期 | DATE_ADD | 按照指定的幅度增减天数，与date_sub的增减逻辑相反。 |
| 增减日期 | DATE_SUB | 按照指定的幅度增减天数，与date_add的增减逻辑相反。 |
| 增减日期 | ADD_MONTHS | 计算日期值增加指定月数后的日期。 |
| 格式转换 | ISDATE | 判断一个日期字符串能否根据指定的格式串转换为一个日期值。 |
| 格式转换 | DATE_FORMAT | 将日期值(date/timestamp)转换为指定格式的字符串。 |
| 格式转换 | TO_DATE | 将指定格式的字符串转换为日期值(datetime/date)。 |
| 格式转换 | TO_CHAR | 将日期(datetime)按照指定格式转换为字符串。 |
| 格式转换 | FROM_UNIXTIME | 将数字型的UNIX值转换为日期值(datetime)。 |
| 格式转换 | UNIX_TIMESTAMP | 将日期(datetime\date\timestamp)转换为整型的UNIX格式的日期值。 |
| 格式转换 | FROM_UTC_TIMESTAMP | 将一个UTC时区的时间戳转换为一个指定时区的时间戳。 |
| 格式转换 | TO_MILLIS | 将指定日期(datetime)转换为以毫秒为单位的UNIX时间戳 |
| 获取时间中值 | YEAR | 返回日期值的年。 |
| 获取时间中值 | MONTH | 返回日期值所属月份。 |
| 获取时间中值 | DAY | 返回日期值的天。 |
| 获取时间中值 | HOUR | 返回日期小时部分的值。 |
| 获取时间中值 | MINUTE | 返回日期分钟部分的值。 |
| 获取时间中值 | SECOND | 返回日期秒数部分的值。 |
| 获取时间中值 | WEEKOFYEAR | 返回日期值位于当年的第几周。 |
| 获取时间中值 | DAYOFYEAR | 返回日期是当年中的第几天。 |
| 获取时间中值 | DAYOFMONTH | 返回日部分的值。 |
| 获取时间中值 | DAYOFWEEK | 返回日期的星期值。 |
| 获取时间中值 | WEEKDAY | 返回日期值是当前周的第几天。 |
| 获取时间中值 | QUARTER | 返回日期值所属季度。 |
| 获取时间中值 | LAST_DAY | 返回日期值所在月份的最后一天日期。 |
| 获取时间中值 | LASTDAY | 获取日期所在月的最后一天。 |
| 获取时间中值 | DATETRUNC | 提取日期按照指定时间单位截取后的值。 |
| 获取时间中值 | DATEPART | 提取日期中符合指定时间单位的字段值。 |
| 获取时间中值 | EXTRACT | 获取日期TIMESTAMP中指定单位的部分。 |
| 当前时间 | GETDATE | 获取当前系统时间。 |
| 当前时间 | CURRENT_TIMESTAMP | 返回当前TIMESTAMP类型的时间戳。 |
| 当前时间 | CURRENT_TIMEZONE | 返回当前系统的时区信息。 |
| 其它 | MONTHS_BETWEEN | 返回指定日期值间的月数。 |
| 其它 | DATEDIFF | 计算两个日期的差值并按照指定的单位表示。 |
| 其它 | NEXT_DAY | 返回大于日期值且与指定周相匹配的第一个日期。 |
UNIXTIME：BIGINT类型，秒数，UNIX格式的日期时间值，取值范围：[-62167305600, 253402387200]。TIMESTAMP：TIMESTAMP类型存储的是UTC 时间，读取或插入时会根据当前连接的时区进行自动转换。
1、增减日期
DATEADD：按照指定的单位和幅度修改日期值。
SELECT DATEADD(CAST('2021-11-28 00:02:00' AS DATETIME ),1,'hh');
SELECT DATEADD(DATETIME '2021-11-28 00:02:00' ,1,'mm');
DATE_ADD：按照指定的幅度增减天数，与date_sub的增减逻辑相反。
SELECT DATE_ADD('2021-11-28 00:02:00' ,1);
SELECT DATE_ADD('2021-11-28' ,1);
SELECT DATE_ADD(CAST('2021-11-28' AS DATE) ,1);
DATE_SUB：按照指定的幅度增减天数，与date_add的增减逻辑相反。
SELECT DATE_SUB('2021-11-28 00:02:00',1);
SELECT DATE_SUB( CAST('2021-11-28 00:02:00' AS DATETIME),1 )
ADD_MONTHS：计算日期值增加指定月数后的日期。
SELECT ADD_MONTHS('2020-10-10 10:10:10' ,1);
SELECT ADD_MONTHS(CAST('2020-10-10 10:10:10' AS DATETIME),1);
2、格式转换
ISDATE：判断一个日期字符串能否根据指定的格式串转换为一个日期值。
SELECT ISDATE('2020-10-10' ,'yyyy-mm-dd')
DATE_FORMAT：将日期值转换为指定格式的字符串。-
在非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_format(date|timestamp|string <date>, string <format>)
-- 打开Hive模式。与SQL语句一起提交。
set odps.sql.hive.compatible=true;
-- 假设当前时间为2023-11-20 17:52。返回2023-11-20 17:52:41.544。
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');
TO_DATE：将指定格式的字符串转换为日期值。yyyy为4位数的年，mm为2位数的月，dd为2位数的日，hh为24小时制的时，mi为2位数的分钟，ss为2位数秒，ff3为3位精度毫秒。
DATETIME|DATE TO_DATE(STRING <date>[, STRING <format>])
-- 无format参数时，返回DATE类型，格式为yyyy-mm-dd。
SELECT TO_DATE('2020-10-10 10:10:10')
-- 返回Datetime类型
SELECT TO_DATE('2020-10-10','yyyy-mm-dd')
SELECT TO_DATE('2020-10-10 10:10:10','yyyy-mm-dd hh:mi:ss')
TO_CHAR：将日期按照指定格式转换为字符串。- DATETIME类型日期值，格式为
yyyy-mm-dd hh:mi:ss
- DATETIME类型日期值，格式为
STRING TO_CHAR(DATATIME <date>, STRING <format>)
SELECT TO_CHAR(DATETIME '2020-10-10 10:10:10','yyyy-mm-dd hh:mi:ss')
SELECT TO_CHAR(CAST('2020-10-10 10:10:10' AS DATETIME ),'yyyy-mm-dd hh:mi:ss')
FROM_UNIXTIME：将数字型的UNIX值转换为日期值。
datetime from_unixtime(bigint <unixtime>)
-- 1970-01-01 08:00:00
SELECT FROM_UNIXTIME(0);
SELECT FROM_UNIXTIME(123456789);
UNIX_TIMESTAMP：将日期转换为整型的UNIX格式的日期值。
bigint unix_timestamp(datetime|date|timestamp|string <date>)
-- Datetime类型
SELECT UNIX_TIMESTAMP(CAST('2020-10-10 10:10:10' AS DATETIME));
-- 字符串类型
set odps.sql.type.system.odps2=TRUE ;
select unix_timestamp('2009-03-20 11:11:00');
FROM_UTC_TIMESTAMP：将一个UTC时区的时间戳转换为一个指定时区的时间戳。
timestamp from_utc_timestamp({any primitive type}*, string <timezone>)
--开启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');
TO_MILLIS：将指定日期转换为以毫秒为单位的UNIX时间戳。
bigint to_millis(datetime|timestamp <date>);
SELECT to_millis(cast('2020-10-10 10:10:10' as datetime));
3、获取时间中值
YEAR：返回日期值的年。
int year(datetime|timestamp|date|string <date>)
SELECT YEAR('2020-08-09 10:11:12'); -- 字符串
SELECT YEAR('2020-08-09'); -- 2020
SELECT YEAR(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss')); -- datetime
SELECT YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'))));
MONTH：返回日期值所属月份。
int month(datetime|timestamp|date|string <date>)
SELECT MONTH('2020-08-09 10:11:12'); -- 字符串
SELECT MONTH('2020-08-09'); -- 8
SELECT MONTH(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss')); -- datetime
SELECT MONTH(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'))));
DAY：返回日期值的天。
int day(datetime|timestamp|date|string <date>)
SELECT DAY('2020-08-09 10:11:12'); -- 字符串
SELECT DAY('2020-08-09'); -- 9
SELECT DAY(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss')); -- datetime
SELECT DAY(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'))));
HOUR：返回日期小时部分的值。
int hour(datetime|timestamp|string <date>)
SELECT HOUR('2020-08-09 10:11:12'); -- 字符串
SELECT HOUR('05:07:08'); -- 5
SELECT HOUR(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss')); -- datetime
SELECT HOUR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'))));
MINUTE：返回日期分钟部分的值。
int minute(datetime|timestamp|string <date>)
SELECT MINUTE('2020-08-09 10:11:12'); -- 字符串
select MINUTE('12:07:08'); -- 7
SELECT MINUTE(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss')); -- datetime
SELECT MINUTE(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'))));
SECOND：返回日期秒数部分的值。
int second(datetime|timestamp|string <date>)
SELECT SECOND('2020-08-09 10:11:12'); -- 字符串
select SECOND('12:30:08'); -- 8
SELECT SECOND(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss')); -- datetime
SELECT SECOND(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('2020-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'))));
WEEKOFYEAR：返回日期值位于当年的第几周。
bigint weekofyear (datetime <date>)
SELECT WEEKOFYEAR(TO_DATE('2025-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'));
SELECT WEEKOFYEAR(DATETIME '2025-08-09 10:11:12');
DAYOFYEAR：返回日期是当年中的第几天。
int dayofyear(datetime|timestamp|date|string <date>)
SELECT DAYOFYEAR('2025-08-09');
SELECT DAYOFYEAR('2025-08-09 10:11:12');
SELECT DAYOFYEAR(DATETIME '2025-08-09 10:11:12');
SELECT DAYOFYEAR(TO_DATE('2025-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'));
DAYOFMONTH：返回日部分的值。
int dayofmonth(datetime|timestamp|date|string <date>)
SELECT DAYOFMONTH('2025-08-09');
SELECT DAYOFMONTH('2025-08-09 10:11:12');
SELECT DAYOFMONTH(DATETIME '2025-08-09 10:11:12');
SELECT DAYOFMONTH(TO_DATE('2025-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'));
DAYOFWEEK：返回日期的星期值。- 周日为第一天。返回1
- 周日：1。周一：2。周二：3。周三：4。周四：5。周五：6。周六：7。
int dayofweek(datetime|timestamp|date|string <date>)
SELECT DAYOFWEEK('2025-08-09');
SELECT DAYOFWEEK('2025-08-09 10:11:12');
SELECT DAYOFWEEK(DATETIME '2025-08-09 10:11:12');
SELECT DAYOFWEEK(TO_DATE('2025-08-09 10:11:12','yyyy-mm-dd hh:mi:ss'));
WEEKDAY：返回日期值是当前周的第几天。- 周一作为一周的第一天，返回值为0。其他日期依次递增。
- 周一：0。周二：1。周三：2。周四：3。周五：4。周六：5。周日：6。
bigint weekday (datetime <date>)
SELECT WEEKDAY(DATETIME '2025-06-27 10:11:12');
SELECT WEEKDAY(TO_DATE('2025-06-27 10:11:12','yyyy-mm-dd hh:mi:ss'));
QUARTER：返回日期值所属季度。
int quarter (datetime|timestamp|date|string <date>)
-- 2
SELECT QUARTER('2025-06-27');
SELECT QUARTER('2025-06-27 10:11:12');
SELECT QUARTER(DATETIME '2025-06-27 10:11:12');
SELECT QUARTER(TO_DATE('2025-06-27 10:11:12','yyyy-mm-dd hh:mi:ss'));
LAST_DAY：返回日期值所在月份的最后一天日期。
string last_day(date|datetime|timestamp|string <date>)
-- 2025-06-30
SELECT LAST_DAY('2025-06-27');
SELECT LAST_DAY('2025-06-27 10:11:12');
SELECT LAST_DAY(DATETIME '2025-06-27 10:11:12');
SELECT LAST_DAY(TO_DATE('2025-06-27 10:11:12','yyyy-mm-dd hh:mi:ss'));
LASTDAY：获取日期所在月的最后一天。
datetime lastday(datetime <date>)
-- 2025-06-30 00:00:00
SELECT LASTDAY(DATETIME '2025-06-27 10:11:12');
SELECT LASTDAY(TO_DATE('2025-06-27 10:11:12','yyyy-mm-dd hh:mi:ss'));
DATETRUNC：提取日期按照指定时间单位截取后的值。- 时间转换格式：
yyyy-mm-dd hh:mi:ss - 支持扩展的日期格式：年
-year、月-month或-mon、日-day和小时-hour。
- 时间转换格式：
date|datetime datetrunc (date|datetime|timestamp <date>, string <datepart>)
-- 2025-01-01 00:00:00
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','yyyy');
-- 2025-06-01 00:00:00
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','mm');
-- 2025-06-01 00:00:00
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','month');
-- 2025-06-27 00:00:00
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','dd');
-- 2025-06-27 10:00:00
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','hh');
-- 2025-06-27 10:11:00
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','mi');
-- 2025-06-27 10:11:12
SELECT DATETRUNC(DATETIME '2025-06-27 10:11:12','ss');
DATEPART：提取日期中符合指定时间单位的字段值。- 时间转换格式：
yyyy-mm-dd hh:mi:ss - 支持扩展的日期格式：年
-year、月-month或-mon、日-day和小时-hour。
- 时间转换格式：
bigint datepart(date|datetime|timestamp <date>, string <datepart>)
-- 2025
SELECT DATEPART(DATETIME '2025-06-27 10:11:12','yyyy');
EXTRACT：获取日期TIMESTAMP中指定单位的部分。
int extract(<datepart> from date|datetime|timestamp <date>)
-- my_year：2019
-- my_month：5
-- my_day：1
-- my_hour：11
-- my_minute：21
--开启2.0新类型。此命令需要与SQL语句一起提交。
SET odps.sql.type.system.odps2=true;
SELECT EXTRACT(YEAR FROM '2019-05-01 11:21:00') AS my_year
,EXTRACT(MONTH FROM '2019-05-01 11:21:00') AS my_month
,EXTRACT(DAY FROM '2019-05-01 11:21:00') AS my_day
,EXTRACT(HOUR FROM '2019-05-01 11:21:00') AS my_hour
,EXTRACT(MINUTE FROM '2019-05-01 11:21:00') AS my_minute;
4、当前时间
GETDATE：获取当前系统时间。
datetime getdate()
-- 2025-06-30 16:38:56
SELECT GETDATE();
CURRENT_TIMESTAMP：返回当前TIMESTAMP类型的时间戳。
timestamp current_timestamp()
-- 2025-06-30 16:43:21.571
SELECT CURRENT_TIMESTAMP()
CURRENT_TIMEZONE：返回当前系统的时区信息。
string current_timezone()
-- Asia/Shanghai
SELECT CURRENT_TIMEZONE();
5、其它
MONTHS_BETWEEN：返回指定日期值间的月数。
double months_between(datetime|timestamp|date|string <date1>, datetime|timestamp|date|string <date2>)
-- 3.0(整月的差距)
SELECT MONTHS_BETWEEN(DATETIME '2025-08-20 10:11:13',DATETIME '2025-05-20 10:11:13');
-- 2.838709677419355(date1减去date2的天数除以31天。)
SELECT MONTHS_BETWEEN(DATETIME '2025-08-15 10:11:13',DATETIME '2025-05-20 10:11:13');
DATEDIFF：计算两个日期的差值并按照指定的单位表示。
bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>)
-- 2(取日之前进行计算<年、月、日>)
SELECT DATEDIFF(DATETIME '2005-02-03 00:00:00',DATETIME '2005-02-01 00:00:00');
-- 2(取日之前进行计算<年、月、日>)
SELECT DATEDIFF(DATETIME '2005-02-03 23:59:59',DATETIME '2005-02-01 00:00:00');
-- 3(取日之前进行计算<年、月、日>)
SELECT DATEDIFF(DATETIME '2005-02-04 00:00:00',DATETIME '2005-02-01 00:00:00');
-- 10
SELECT DATEDIFF(DATETIME '2015-02-04 00:00:00',DATETIME '2005-02-01 00:00:00','yyyy');
-- 1440(取分之前计算<年、月、日、时、分>)
SELECT DATEDIFF(DATETIME '2005-02-02 00:00:00',DATETIME '2005-02-01 00:00:00','mi');
NEXT_DAY：返回大于日期值且与指定周相匹配的第一个日期。
string next_day(timestamp|date|datetime|string <startdate>, string <week>)
-- 2025-06-22(下一周的周日)
SELECT NEXT_DAY('2025-06-20','SU');
-- 2025-06-23(下一周的周一)
SELECT NEXT_DAY('2025-06-20','MO');
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
