目录
- 获取当前日期:current_date()
- 获取当前时间:current_time()
- 获取当前日期和时间:current_timestamp()
- 获取当前日期和时间:now()
- 获取当前时区:current_timezone()
- 字符串转时间戳:cast(string, timestamp)
- 字符串转时间戳:date_parse(string, fromat)
- bigint转时间戳
- 时间戳转biging
- 时间戳格式化: format_datetime(timestamp,format)
- 时间戳取年月日
- 字符串转年月日
- bigint转年月日
- 时间间隔:date_diff(unit, timestamp1, timestamp2)
- 几天前后几天后:interval、date_add
- 月初、年初、周一和季度第一天:date_trunc(unit, timestamp)
- 时间提取函数 extract、year、month、day
- 日期是周几:day_of_week()
日期时间运算符
运算符 | 示例 | 结果 |
---|---|---|
+ | date ‘2012-08-08’ + interval ‘2’ day ) | 2012-08-10 |
+ | time ‘01:00’ + interval ‘3’ hour | 04:00:00.000 |
+ | timestamp ‘2012-08-08 01:00’ + interval ‘29’ hour | 2012-08-09 06:00:00.000 |
+ | timestamp ‘2012-10-31 01:00’ + interval ‘1’ month | 2012-11-30 01:00:00.000 |
+ | interval ‘2’ day + interval ‘3’ hour | 2 03:00:00.000 |
+ | interval ‘3’ year + interval ‘5’ month | 3-5 |
- | date ‘2012-08-08’ - interval ‘2’ day | 2012-08-06 |
- | time ‘01:00’ - interval ‘3’ hour | 22:00:00.000 |
- | dtimestamp ‘2012-08-08 01:00’ - interval ‘29’ hour | 2012-08-06 20:00:00.000 |
- | timestamp ‘2012-10-31 01:00’ - interval ‘1’ month | 2012-09-30 01:00:00.000 |
- | interval ‘2’ day - interval ‘3’ hour | 1 21:00:00.000 |
- | interval ‘3’ year - interval ‘5’ month | 2-7 |
时区转换
运算符:AT TIME ZONE
,用于设置一个时间戳的时区:
select timestamp '2022-03-17 01:00 UTC';
2022-03-17 09:00:00
select timestamp '2022-03-17 01:00 UTC' AT TIME ZONE 'America/Los_Angeles';
2022-03-17 09:00:00
获取当前日期:current_date()
select current_date();
-- 2022-03-17
获取当前时间:current_time()
select current_time();
-- 17:07:16
获取当前日期和时间:current_timestamp()
select current_timestamp();
-- 2022-03-17 21:17:49.035
获取当前日期和时间:now()
select now();
-- 2022-03-17 21:27:03.604
获取当前时区:current_timezone()
select current_timezone();
-- Asia/Shanghai
字符串转时间戳:cast(string, timestamp)
select cast('2022-03-17' as timestamp);
-- 2022-03-17 00:00:00.0
select cast('2022-03-17 00:00:00' as timestamp);
-- 2022-03-17 00:00:00.0
字符串转时间戳:date_parse(string, fromat)
select date_parse('2022-03-17', '%Y-%m-%d');
-- 2022-03-17 00:00:00.0
select date_parse('2022-03-17 00:00:00', '%Y-%m-%d %H:%i:%S');
-- 2022-03-17 00:00:00.0
注意: 字符串格式和format格式需保持一致。
以下为错误示例:
select date_parse('2022-03-17', '%Y-%m-%d %H:%i:%S');
-- Invalid format: "2022-03-17" is too short
bigint转时间戳
select from_unixtime(1647500800);
-- 2022-03-17 15:06:40.0
时间戳转biging
select to_unixtime(current_date);
-- 1647446400
时间戳格式化: format_datetime(timestamp,format)
select format_datetime(cast('2022-03-17' as timestamp),'yyyy-MM-dd HH:mm:ss');
-- 2022-03-17 00:00:00
select format_datetime(cast('2022-03-17' as timestamp),'yyyy-MM-dd HH');
-- 2022-03-17 00
select date_trunc('second', current_timestamp());
-- 2022-05-03 13:37:12.0
select date_trunc('minute', current_timestamp());
--
时间戳取年月日
select date_format(current_date,'%Y-%m-%d');
select date(current_date);
select cast(current_date as date);
-- 2022-03-17
字符串转年月日
select date(cast('2021-03-17 10:28:00' as TIMESTAMP));
select date('2021-03-17');
select date_format(cast('2021-03-17 10:28:00' as TIMESTAMP),'%Y-%m-%d');
select to_date('2021-03-17','yyyy-mm-dd');
-- 2021-03-17
注意: 格式不同时date、to_date无法使用
select date('2021-03-17 10:28:00');
-- Value cannot be cast to date: 2021-03-17 10:28:00
select to_date('2021-03-17 10:28:00','yyyy-mm-dd');
-- Invalid format: "2021-03-17 10:28:00" is malformed at " 10:28:00"
bigint转年月日
select date(from_unixtime(1647500800));
-- 2022-03-17
select date_format(from_unixtime(1647500800),'%Y-%m-%d');
-- 2022-03-17
时间间隔:date_diff(unit, timestamp1, timestamp2)
函数支持如下所列的间隔单位:
Unit | Description |
---|---|
second | Seconds |
minute | Minutes |
hour | Hours |
day | Days |
week | Weeks |
month | Months |
quarter | Quarters of a year |
year | Years |
select date_diff('day',cast('2022-03-17' as TIMESTAMP),cast('2022-03-26' as TIMESTAMP));
-- 9
select date_diff('month',cast('2022-02-17' as TIMESTAMP),cast('2022-03-26' as TIMESTAMP));
-- 1
select date_diff('year',cast('2021-02-17' as TIMESTAMP),cast('2022-03-26' as TIMESTAMP));
-- 1
注意: 与hive差异!!!
presto中 date_diff('day',date1,date2)【后-前】
hive,mysql中 datediff(date1,date2) 【前-后】
几天前后几天后:interval、date_add
select current_date, (current_date - interval '7' month), date_add('day', -7, current_date);
-- 2022-03-17 | 2021-08-17 | 2022-03-10
select current_date, (current_date + interval '7' month), date_add('day', 7, current_date);
-- 2022-03-17 | 2022-10-17 | 2022-03-24
月初、年初、周一和季度第一天:date_trunc(unit, timestamp)
函数 date_trunc支持如下单位:
单位 | Example Truncated Value |
---|---|
second | 2001-08-22 03:04:05.000 |
minute | 2001-08-22 03:04:00.000 |
hour | 2001-08-22 03:00:00.000 |
day | 2001-08-22 00:00:00.000 |
week | 2001-08-20 00:00:00.000 |
month | 2001-08-01 00:00:00.000 |
quarter | 2001-07-01 00:00:00.000 |
year | 2001-01-01 00:00:00.000 |
上面的例子使用时间戳: 2001-08-22 03:04:05.321 作为输入。
select date_trunc('second', current_timestamp());
-- 2022-05-03 13:39:38.0
select date_trunc('minute', current_timestamp());
-- 2022-05-03 13:40:00.0
select date_trunc('hour', current_timestamp());
-- 2022-05-03 13:00:00.0
select date_trunc('day', current_timestamp());
-- 2022-05-03 00:00:00.0
select date_trunc('week', cast('2022-03-17' as date));
-- 2022-03-14
select date_trunc('month', cast('2022-03-17' as date));
-- 2022-03-01
select date_trunc('quarter', cast('2022-03-17' as date));
-- 2022-01-01
select date_trunc('year', cast('2022-03-17' as date));
-- 2022-01-01
时间提取函数 extract、year、month、day
select
extract(year from current_date),
year(current_date),
extract(month from current_date),
month(current_date),
extract(day from current_date),
day(current_date);
-- 2022 | 2022 | 3 | 3 | 17 | 17
日期是周几:day_of_week()
select day_of_week(current_date)
-- 4