【Hive SQL】时间戳格式化、时间字符串转换格式化、时区切换（Mysql\Hive S ...

一、日期格式化

本文主要记录 [Mysql\ Hive SQL\ Athena] 时间戳转换、日期格式化、时区转换各种数据数据操作
1、时间戳格式化

1、毫秒值转 yyyy-MM-dd HH:mm:ss

• Mysql
  

1. select FROM_UNIXTIME(1617187200000/1000,'%Y-%m-%d %H:%i:%s') as ts_format

复制代码

• Hive SQL
  

1. select FROM_UNIXTIME(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss') as ts_format

复制代码

• Athena
  

1. // 方式1 返回值 -> timestamp
2. select FROM_UNIXTIME(1617187200000/1000) as ts_format
3. // 方式2 返回值 -> varchar
4. select date_format(FROM_UNIXTIME(1617187200000/1000),'%Y-%m-%d %H:%i:%s') as ts_format

复制代码

2、日期字符串格式化

1、字符串日期格式化

• Mysql
  

1. // 字符串转换成日期：str_to_date(str,format)
2. // 日期转换成字符串：date_format(date,format)
3. // 时间转换成字符串：time_format(time,format)
4. select date_format(str_to_date('09-13-2024','%m-%d-%Y'),'%Y%m%d')
6. // 八位转10位 + 时分秒
7. select date_format('20240501','%Y-%m-%d %H:%i:%s')
9. // 八位转10位
10. select date_format('20240501','%Y-%m-%d')

复制代码

• Hive SQL
  

1. // 方式1：使用 data_format
2. // 8 位日期格式，maxcomputer 可用 to_date 转换
3. select date_format(to_date('20240501','yyyyMMdd'),'yyyy-MM-dd');
5. // 10位日期格式 hive 可以使用 to_date 后进行format格式化
6. select date_format(to_date('2024-05-01'),'yyyyMMdd');
8. // 方式2：使用 from_unixtime
9. select from_unixtime(unix_timestamp('20240501','yyyyMMdd'),'yyyy-MM-dd');

复制代码

• Athena
  

1. select date_parse('2024/05/01/05', '%Y/%m/%d/%H')
2. -- 2024-05-01 05:00:00.000
4. select date_format(date_parse('20240501','%Y%m%d'),'%Y-%m-%d')
5. -- 2024-05-01

复制代码

3、时区切换

将一个UTC时区的时间戳转换成一个指定时区的时间戳，即将一个UTC时区的时间戳按照指定的时区表现

• Mysql
  

1. // CONVERT_TZ(dt,from_tz,to_tz)
3. select
4.     from_unixtime(1617187200000/1000,'%Y-%m-%d %H:%i:%s') as ori_time
5.     ,convert_tz(FROM_UNIXTIME(1617187200000/1000,'%Y-%m-%d %H:%i:%s'),"+00:00","+8:00") as to_time
7. // 结果
8. """
9. +---------------------+---------------------+
10. |ori_time             |to_time              |
11. +---------------------+---------------------+
12. |2021-03-31 10:40:00  |2021-03-31 18:40:00  |
13. +---------------------+---------------------+
14. """

复制代码

• Hive SQL
  

1. from_utc_timestamp(expr, timeZone)
2. // expr：一个 TIMESTAMP 表达式，带有 UTC 时间戳。
3. // timeZone：一个为有效时区的 STRING 表达式。
5. // 转中国时区
6. select
7.      from_unixtime(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss') as ori_time
8.     ,from_utc_timestamp(from_unixtime(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss'), 'PRC') as to_time
9. """
10. +---------------------+---------------------+
11. |ori_time             |to_time              |
12. +---------------------+---------------------+
13. |2021-03-31 10:40:00  |2021-03-31 18:40:00  |
14. +---------------------+---------------------+
15. """
17. // 方式2： 根据当前时区与目标时间差，对目标时间戳 + 对应时间差值
19. select
20.      from_unixtime(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss') as ori_time
21.     ,from_unixtime(cast(1617187200000/1000 as bigint) + 8*60*60,'yyyy-MM-dd HH:mm:ss') as to_time
22. """
23. +---------------------+---------------------+
24. |ori_time             |to_time              |
25. +---------------------+---------------------+
26. |2021-03-31 10:40:00  |2021-03-31 18:40:00  |
27. +---------------------+---------------------+
28. """

复制代码

Hive官网说明
阿里云文档FROM_UTC_TIMESTAMP 函数说明

• Athena
  

2. // 方式1 from_unixtime(unixtime, zone) → timestamp(3)
3. // 方式2 timestamp AT TIME ZONE 'zone'
4. // 方式3 当前时间戳 + 时区差
5. select
6.      from_unixtime(1617187200000/1000) as ori_time
7.     ,from_unixtime(1617187200000/1000, 'PRC') as to_time1
8.     ,from_unixtime(1617187200000/1000) AT TIME ZONE 'PRC' as to_time2
9.     ,from_unixtime(1617187200000/1000 + 8*60*60) as to_time3
10.     ,date_format(from_unixtime(1617187200000/1000, 'PRC'),'%Y-%m-%d %H:%i:%s') as to_time_format
12. """
13. +-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
14. |ori_time                 |to_time1                     |to_time2                     |to_time3                 |to_time_format           |
15. +-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
16. |2021-03-31 10:40:00.000  |2021-03-31 18:40:00.000 PRC  |2021-03-31 18:40:00.000 PRC  |2021-03-31 18:40:00.000  |2021-03-31 18:40:00.000  |
17. +-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
18. """

复制代码

Athena Date and time functions and operators 官网
4、时区列表

1：阿里云文档时区列表
2：timeanddate 时区列表
3：Athena 支持的时区列表

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。