presto语法(presto日期函数)

目录

获取当前日期：current_date()获取当前时间：current_time()获取当前日期和时间：current_timestamp()获取当前日期和时间：now()字符串转时间戳：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()

---

获取当前日期：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

字符串转时间戳：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_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)

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)

select date_trunc('month', current_date);
-- 2022-03-01
select date_trunc('year', current_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