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

已于 2023-11-09 18:27:08 修改
阅读量1.3k 收藏 2
点赞数 1
文章标签： hive spark
于 2023-11-09 16:35:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43839095/article/details/134315153
版权

时间相关函数

hive

字符串与timestamp转换

select cast(current_timestamp() as string),cast('2023-07-28 09:01:39' as timestamp);
-- 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:50

select 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:00

select  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年2022
MM月09
dd天07
HH24小时11、20
hh12小时11、08
mm分钟55
ss秒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' as timestamp)
--2023-09-07 16:00:12    2016-12-31 00:12:00

select 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    1694071430

select 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    1694071430

select 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转换

select SELECT cast(current_timestamp as varchar),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:08

SELECT cast('2023-06-09 11:13:22' as timestamp),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()
-- UTC

select with_timezone(cast('1970-01-01 08:00:00' as timestamp),'Asia/Shanghai')
-- 1970-01-01 08:00:00 +08:00

SELECT at_timezone(cast('1970-01-01 00:00:00' as timestamp), 'Asia/Shanghai')
-- 1970-01-01 08:00:00 +08:00

select
cast('2022-11-01 09:08:07' as timestamp),
with_timezone(cast('2022-11-01 09:08:07' as timestamp),'UTC'),
at_timezone(with_timezone(cast('2022-11-01 09:08:07' as timestamp),'UTC'),'Asia/Shanghai'),
date_format(at_timezone(with_timezone(cast('2022-11-01 09:08:07' as timestamp),'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' as datetime),cast(current_timestamp() as varchar)
-- 2023-09-08 07:01:33    2023-09-08 17:01:29

select 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:19

select 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

申尧强
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
【大数据入门核心技术-Hive】（八）Hive 时间转换
12-18 328
UNIX时间戳概念：因为UNIX时间戳只是一个秒数，一个UNIX时间戳在不同时区看来，时间是不同的。如UNIX时间戳0，在0时区看来是1970-01-01 00:00:00，在东八区看来是1970-01-01 08:00:00。以前需要很长时间处理才能获得结果的问题 ，到现在变得顷刻之间就迎刃而解 ，同时还可以引发新的问题和新的见解。Hadoop 是 Apache Lucene 创始人Doug Cutting 创建的 ，Lucene 是一个应用 广 泛 的文本搜索系统库Hadoop起源于开 源的网络搜索引擎
presto时间转换、时间加减、时间差
热门推荐
别说话写代码的博客
04-13 1万+
Hive中对应的日期操作见：https://blog.csdn.net/qq_21997625/article/details/111473520 问题1：时间格式转换 例子: 当前时间20200110 转化为2020-01-10 --presto select (format_datetime(date_parse('20200110','%Y%m%d'),'yyyy-MM-dd') 问题2： 时间的加减 例子: 原时间为20200110 需先转化为标准日期形式再加减 --presto .
hive sql 日期格式转换
紫蝶侠的博客
12-29 6686
TO_CHAR 将日期类型date按照format指定的格式转成字符串。 https://helpcdn.aliyun.com/document_detail/48974.html#section-a2d-rfm-vdb string to_char(datetime <date>, string <format>) 命令说明 将日期类型date按照format指定的格式转成字符串。 参数说明 date：必填。DATETIME类型日期值，格式为yyyy-mm-dd hh:mi:s
hive sql日期转换
ViaNnAljj的博客
04-19 420
regexp_replace(cast(date_sub(from_unixtime(unix_timestamp('${date}','yyyymmdd'),'yyyy-mm-dd'),10) as string),'-','')
Hive、Presto、PostgreSQL 时区、日期、时间转换（昨天、周）
weixin_42385450的博客
10-23 7692
Hive、Presto、PostgroSQL 时区、日期、时间转换（昨天、周）
Hive时间函数时区问题（坑）
weixin_38251332的博客
12-23 6589
hive的时间函数 取得当前日期 select current_date(); 取得当前日期时间 select current_timestamp(); 取得当前时间 select from_unixtime(unix_timestamp(),‘yyyy-MM-dd HH:mm:ss’); 获取的是HiveService所在操作系统RTC（时钟芯片）时间，如下 实例1： 实例2： ...
hive 和 presto sql的对比
04-21
总结来说，Hive和Presto在SQL语法上有所不同，Presto更注重强类型和明确的类型转换，而Hive的语法可能更加灵活但有时可能导致隐式类型转换的问题。在处理复杂数据类型、时间日期以及聚合函数时，两者都有各自的特性...
从Hive迁移到SparkSQL，有赞的大数据实践
02-24
本文介绍由SparkSQL替换Hive过程中碰到的问题以及处理经验和优化建议，包括以下方面的内容：有赞数据平台的整体架构。SparkSQL在有赞的技术演进。从Hive到SparkSQL的迁移之路。首先介绍一下有赞大数据平台总体架构：...
cloudera-hive/impala-odbc
04-22
标题中的"cloudera-hive/impala-odbc"指的是Cloudera提供的Hive和Impala的ODBC（Open Database Connectivity）驱动程序。ODBC是用于在不同数据库系统之间建立连接的标准接口，使得应用程序可以访问多种类型的数据库...
Spark Sql中时间字段少8个小时问题解决
最新发布
02-28
Spark Sql中时间字段少8个小时问题解决
spring-boot集成mybtis+druid实现hive/mysql多数据源切换
09-28
本文将详细讨论如何使用Spring Boot、MyBatis和Druid来实现MySQL与Hive之间的多数据源切换，以满足不同业务场景的需求。 首先，Spring Boot是Spring框架的一种轻量级实现，它简化了配置并提供了快速开发新应用的...
hive sql 自动根据时区转换
u011250186的博客
04-08 867
select date_format(from_utc_timestamp(1567640142000,"UTC"),'yyyy-MM-dd') as local_time 2019-09-04 selectdate_format(from_utc_timestamp(1567640142000,"GMT+8"),'yyyy-MM-dd')aslocal_time 2019-09-05
PrestoSQL(trino)和Hive SQL的日期格式转换对比
一缕阳光的博客
11-08 886
【代码】PrestoSQL(trino)和Hive SQL的日期格式转换对比。
hive 10位、13位、16位时间戳转换（考虑时区因素）
jetspide的博客
06-12 910
date_format(from_utc_timestamp(create_time*1000,'PRC'),'yyyy-MM-dd HH:mm:ss')default.format_utc_time(create_time ,'yyyy-MM-dd HH:mm:ss.SSS')date_format(from_utc_timestamp(cast(substr(create_time,0,13) as bigint),'PRC'),'yyyy-MM-dd HH:mm:ss')
【SQL】prestoSQL 日期函数实战常用方法（更新中）
weixin_41765578的博客
09-05 1125
本文用于整理企业实战中常见的日期函数使用需求，主要基于presto sql语法进行实现。内容将会持续更新。
flink写入hive的时区问题
微电子学与固体电子学-俞驰
01-13 1346
调研 [2]中只是解决了显示问题 [3]中解决了无法触发水印计算的问题. ##################################################################################################################### 修改hive配置 案例讲解 引入相关的pom 构造hive catalog 创建hive表 将流数据插入hive，
Hive中时间格式的相关转换
iilegend的博客
05-18 1953
1、获取当前的时间戳 select unix_timestamp() from dim_kpi; 2、时间戳转时间 备注：转化UNIX时间戳（从1970-01-0100:00:00 UTC到指定时间的秒数）到当前时区的时间格式 select from_unixtime(1558166130,'yyyy-MM-dd HH:mm:ss') from dim_kpi; 3、日期...
2019.07.02 - SQL 日期时间换算 与 UTC时区转换，即dateadd(), date_add(), date()使用
jaylixueji的博客
07-02 9980
文章目录使用场景DateTime数据格式SQL Server的日期换算MySQLTimeStamp数据格式 使用场景 日期换算往往用于UTC时间+时区 UTC时间 DateTime数据格式 SQL Server的日期换算 DATEADD(hour,2, creat_time)：增加两小时 DATE(creat_time)：获取日期"2019-06-28" Amazon RedShi...
hive / sparksql：如何将unix时间戳转换为时间戳（不是字符串）？
03-16
在Hive和SparkSQL中，可以使用from_unixtime()函数将Unix时间戳转换为时间戳。该函数的语法如下： from_unixtime(unix_timestamp[, format]) 其中，unix_timestamp是Unix时间戳，format是可选参数，用于指定输出时间戳的格式。如果不指定format，则默认输出为yyyy-MM-dd HH:mm:ss格式的时间戳。 例如，将Unix时间戳转换为时间戳的示例代码如下： SELECT from_unixtime(1612345678); 输出结果为： 2021-02-03 12:01:18 注意：在Hive中，Unix时间戳的单位为秒；而在SparkSQL中，Unix时间戳的单位为毫秒。因此，在使用from_unixtime()函数时，需要根据具体情况进行单位转换。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值