hive/sparksql/presto 时区转换和时间类型转换

时间相关函数

hive

字符串与timestamp转换

select cast(current_timestamp()as string),cast('2023-07-28 09:01:39'astimestamp);-- 2023-09-07 15:47:57.225    2023-07-28 09:01:39  

hive中可以把timestamp和时间字符串等同看待，一般不用转换。

毫秒与字符串(timestamp)转换

select from_unixtime(1694071430),from_unixtime(1694071430,'yyyy-MM-dd HH:mm:ss');--  2023-09-07 07:23:50    2023-09-07 07:23:50select unix_timestamp('2023-09-07 07:23:50'),unix_timestamp('2023:09:07 07:23:50','yyyy:MM:dd HH:mm:ss')-- 1694071430    1694071430 

from_unixtime/unix_timestamp，要求10位的bigint类型（精确到秒）
都可以指定字符串的格式，不指定默认是yyyy-MM-dd HH:mm:ss
使用默认时区（hive）

时区转换

主要有四个相关时区：
原始时区：来源存储数据对应的时区
目标时区：需要转换到的对应时区
数据库时区：数据本身的时区，hive配置文件中hive.local.time.zone 设置对应时区，默认为UTC
UTC时区：一般在转换过程中转使用

select to_utc_timestamp('1970-01-01 00:00:00','Asia/Shanghai')-- 1969-12-31 16:00:00-- 给定时间戳是1970-01-01 00:00:00 Asia/Shanghai。转换成UTC时间就是减去8个小时，select from_utc_timestamp('1970-01-01 08:00:00','Asia/Shanghai')-- 1970-01-01 16:00:00-- 给定时间戳是UTC时间，转换成北京时间。加8个小时就是1970-01-01 16:00:00select  to_utc_timestamp('2023-09-08 15:25:20','Asia/Shanghai'),from_utc_timestamp(to_utc_timestamp('2023-09-08 15:25:20','Asia/Shanghai'),'Asia/Seoul');-- 2023-09-08 07:25:20    2023-09-08 16:25:20-- 2023-09-08 15:25:20 Asia/Shanghai 转换成对应 Asia/Seoul 时间.首先用to_utc_timestamp转成UTC时间，再用UTC时间转成目标时区select from_unixtime(1694156493),to_utc_timestamp(from_unixtime(1694156493),'UTC'), from_utc_timestamp(to_utc_timestamp(from_unixtime(1694156493),'UTC'),'Asia/Shanghai')-- 2023-09-08 07:01:33    2023-09-08 07:01:33    2023-09-08 15:01:33-- 1.from_unixtime(1694156493)将毫秒转换成时间戳，此时时区为hive时区-- 2.to_utc_timestamp转换成UTC时间，此时时区为UTC-- 3.from_utc_timestamp转换成目标时区 

to_utc_timestamp/from_utc_timestamp的第一个参数要求都是yyyy-MM-dd HH:mm:ss格式
from_utc_timestamp(to_utc_timestamp(‘2023-09-08 15:25:20’,‘原始时区’),‘目标时区’) 可以将原始时区时间转换成目标时区时间
from_utc_timestamp(to_utc_timestamp(from_unixtime(1694156493),‘数据库时区’),‘目标时区’) 可以将毫秒转换成目标时区时间

日期格式化

select date_format('2023-07-28 09:01:39','yyyyMMdd'),date_format('2023-07-28','yyyy-MM-dd'),date_format('2023-07-28','yyyy-MM-dd HH:mm:ss')-- 20230728    2023-07-28    2023-07-28 00:00:00

常用：
yyyy-MM-dd HH:mm:ss 2023-09-07 16:25:30
yyyy-MM-dd 2023-09-07
yyyy/MM/dd 2023/09/07
yyyyMMdd 20230907
yyyy年2022MM月09dd天07HH24小时11、20hh12小时11、08mm分钟55ss秒40
备注：YYYY表示的是Week year，就是这天所在的周所属的年（一般不会使用）。
https://baijiahao.baidu.com/s?id=1674198334146875461&wfr=spider&for=pc
详细可以参考：
https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

sparksql

sparksql全部内置函数：https://spark.apache.org/docs/latest/api/sql/

字符串与timestamp转换

SELECT cast(current_timestamp()as string),cast('2016-12-31 00:12:00'astimestamp)--2023-09-07 16:00:12    2016-12-31 00:12:00select to_timestamp('2016-12-31 00:12:00'),to_timestamp('2016-12-31','yyyy-MM-dd')-- 2016-12-31 00:12:00    2016-12-31 00:00:00

字符串与timestamp可以使用cast进行转换（与hive相同）
to_timestamp可以支持选择字符串格式转换

毫秒与字符串（timestamp）转换

SELECT unix_timestamp('2023-09-07 15:23:50'),unix_timestamp('2023-09-07 15:23:50','yyyy-MM-dd HH:mm:ss'),-- 1694071430    1694071430select to_unix_timestamp('2023-09-07 15:23:50'),to_unix_timestamp('2023-09-07 15:23:50','yyyy-MM-dd HH:mm:ss')-- 1694071430    1694071430select from_unixtime(1694071430),from_unixtime(1694071430,'yyyy-MM-dd HH:mm:ss')-- 2023-09-07 15:23:50    2023-09-07 15:23:50

unix_timestamp/from_unixtime跟hive中一样。
to_unix_timestamp等同于unix_timestamp。

时区转换

跟hive时区转换一致。

日期格式化

常用格式化跟hive一致。
特殊用法可以参考：
https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html

presto/trino

presto：https://prestodb.io/docs/current/functions.html
trino：https://trino.io/docs/current/functions.html

字符串与timestamp转换

selectSELECT cast(current_timestampasvarchar),date_format(current_timestamp(3),'%Y-%m-%d %H:%i:%s'),format_datetime(current_timestamp,'yyyy-MM-dd HH:mm:ss')-- 2023-09-07 17:47:08.524 Asia/Shanghai    2023-09-07 17:47:08SELECT cast('2023-06-09 11:13:22'astimestamp),date_parse('2023-06-09 11:13:22','%Y-%m-%d %H:%i:%s'),parse_datetime('2023-09-07 17:53:35','yyyy-MM-dd HH:mm:ss')-- 2023-06-09 11:13:22    2023-06-09 11:13:22  

可以使用cast进行时间字符串与timestamp转换。
date_format/date_parse、format_datetime/parse_datetime可以选择时间字符串格式进行转换，不同点在于格式化类型，date_format/date_parse使用的mysql标准，format_datetime/parse_datetime使用joda-time标准。

毫秒与timestamp转换

select from_unixtime(1694071430),to_unixtime(current_timestamp)-- 2023-09-07 15:23:50    1694081139.541 |

from_unixtime使用10位时间（精确到秒）

时区转换

select current_timezone()-- UTCselect with_timezone(cast('1970-01-01 08:00:00'astimestamp),'Asia/Shanghai')-- 1970-01-01 08:00:00 +08:00SELECT at_timezone(cast('1970-01-01 00:00:00'astimestamp),'Asia/Shanghai')-- 1970-01-01 08:00:00 +08:00select
cast('2022-11-01 09:08:07'astimestamp),
with_timezone(cast('2022-11-01 09:08:07'astimestamp),'UTC'),
at_timezone(with_timezone(cast('2022-11-01 09:08:07'astimestamp),'UTC'),'Asia/Shanghai'),
date_format(at_timezone(with_timezone(cast('2022-11-01 09:08:07'astimestamp),'UTC'),'Asia/Shanghai'),'%Y-%m-%d %H:%i:%s')-- 2022-11-01 09:08:07    -- 2022-11-01 09:08:07 +00:00-- 2022-11-01 17:08:07 +08:00-- 2022-11-01 17:08:07-- 1.cast('2022-11-01 09:08:07' as timestamp) 将字符串转换给timestamp，没有时区-- 2.with_timezone 添加时区信息（原始时区）-- 3.at_timezone 转换成目标时区select from_unixtime(1694156493),from_unixtime(1694156493,'Asia/Shanghai')-- 2023-09-08 07:01:33 +00:00    2023-09-08 07:01:33 +08:00-- from_unixtime不指定时区，使用默认时区-- from_unixtime指定时区可以直接将毫秒转换成对应时区时间戳select at_timezone(from_unixtime(1694156493,'UTC'),'Asia/Shanghai'),
date_format(at_timezone(from_unixtime(1694156493,'UTC'),'Asia/Shanghai'),'%Y-%m-%d %H:%i:%s')-- 2023-09-08 15:01:33 +08:00-- 2023-09-08 15:01:33-- 1.from_unixtime(1694156493,'UTC') 毫秒转换成原始时区时间戳-- 2.at_timezone转换成目标时区时间戳-- at_timezone(from_unixtime(1694156493,'原始时区'),'目标时区')

日期格式化

date_format/date_parse使用mysql格式化时间（date_parse和str_to_date）。
常用：
%Y-%m-%d %H:%i:%s 2023-09-07 16:25:30
%Y-%m-%d 2023-09-07
%Y/%m/%d 2023/09/07
%Y%m%d 20230907
详细使用可以看：https://trino.io/docs/current/functions/datetime.html#mysql-date-functions
format_datetime/parse_datetime使用joda-time格式化时间，兼容jdk中日期格式。可以参考hive日期格式化。详细使用可以看：https://github.com/JodaOrg/joda-time/blob/main/src/main/java/org/joda/time/format/DateTimeFormat.java

Doris

函数大全：https://doris.apache.org/zh-CN/docs/1.2/sql-manual/sql-functions/date-time-functions/convert_tz
doris没有timestamp类型，与之对应的是datetime类型

字符串与datetime转换

select cast('2023-09-08 07:01:33'asdatetime),cast(current_timestamp()asvarchar)-- 2023-09-08 07:01:33    2023-09-08 17:01:29select str_to_date('1970/01/01 08:00:00','%Y/%m/%d %H:%i:%s'),date_format('1970-01-01 08:00:00','%Y/%m/%d %H:%i:%s')-- 1970-01-01 08:00:001970/01/01 08:00:00

可以使用cast进行时间字符串与timestamp转换。
也可以使用str_to_date、date_format指定格式转换。

毫秒与timestamp转换

select from_unixtime(1196440219),from_unixtime(1196440219,'%Y-%m-%d %H:%i:%s');-- 2007-12-01 00:30:19    2007-12-01 00:30:19select unix_timestamp(),unix_timestamp('2007-11-30 10:30:19'),unix_timestamp('2007-11-30 10:30-19','%Y-%m-%d %H:%i-%s');-- 1694164222    1196389819    1196389819

时区转换

show variables like ‘%time_zone%’
可以查看数据库时区

select convert_tz('2019-08-01 13:21:03','UTC','Asia/Shanghai');-- 2019-08-01 21:21:03-- convert_tz('2019-08-01 13:21:03', '原始时区', '目标时区')select convert_tz(from_unixtime(1196440219),'UTC','Asia/Shanghai');-- 2007-12-01 08:30:19-- convert_tz(from_unixtime(1196440219), '数据库时区', '目标时区')

日期格式化

兼容mysql
常用：
%Y-%m-%d %H:%i:%s 2023-09-07 16:25:30
%Y-%m-%d 2023-09-07
%Y/%m/%d 2023/09/07
%Y%m%d 20230907
新增三个特殊格式（一般不用）
yyyyMMdd
yyyy-MM-dd
yyyy-MM-dd HH:mm:ss
详细可以看https://doris.apache.org/zh-CN/docs/1.2/sql-manual/sql-functions/date-time-functions/date_format