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

一、日期格式化

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

1、时间戳格式化

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

• Mysql

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

• Hive SQL

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

• Athena

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

1
2
3
4
5

2、日期字符串格式化

1、字符串日期格式化

• Mysql

// 字符串转换成日期：str_to_date(str,format)
// 日期转换成字符串：date_format(date,format)
// 时间转换成字符串：time_format(time,format)
select date_format(str_to_date('09-13-2024','%m-%d-%Y'),'%Y%m%d')

// 八位转10位 + 时分秒
select date_format('20240501','%Y-%m-%d %H:%i:%s')

// 八位转10位
select date_format('20240501','%Y-%m-%d')
1
2
3
4
5
6
7
8
9
10

• Hive SQL

// 方式1：使用 data_format 
// 8 位日期格式，maxcomputer 可用 to_date 转换
select date_format(to_date('20240501','yyyyMMdd'),'yyyy-MM-dd');

// 10位日期格式 hive 可以使用 to_date 后进行format格式化
select date_format(to_date('2024-05-01'),'yyyyMMdd');

// 方式2：使用 from_unixtime
select from_unixtime(unix_timestamp('20240501','yyyyMMdd'),'yyyy-MM-dd');
1
2
3
4
5
6
7
8
9

• Athena

select date_parse('2024/05/01/05', '%Y/%m/%d/%H')
-- 2024-05-01 05:00:00.000

select date_format(date_parse('20240501','%Y%m%d'),'%Y-%m-%d')
-- 2024-05-01
1
2
3
4
5

3、时区切换

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

• Mysql

// CONVERT_TZ(dt,from_tz,to_tz)

select 
    from_unixtime(1617187200000/1000,'%Y-%m-%d %H:%i:%s') as ori_time 
    ,convert_tz(FROM_UNIXTIME(1617187200000/1000,'%Y-%m-%d %H:%i:%s'),"+00:00","+8:00") as to_time

// 结果
"""
+---------------------+---------------------+
|ori_time             |to_time              |
+---------------------+---------------------+
|2021-03-31 10:40:00  |2021-03-31 18:40:00  |
+---------------------+---------------------+
"""

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• Hive SQL

from_utc_timestamp(expr, timeZone)
// expr：一个 TIMESTAMP 表达式，带有 UTC 时间戳。
// timeZone：一个为有效时区的 STRING 表达式。

// 转中国时区
select 
     from_unixtime(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss') as ori_time
    ,from_utc_timestamp(from_unixtime(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss'), 'PRC') as to_time
"""
+---------------------+---------------------+
|ori_time             |to_time              |
+---------------------+---------------------+
|2021-03-31 10:40:00  |2021-03-31 18:40:00  |
+---------------------+---------------------+
"""

// 方式2： 根据当前时区与目标时间差，对目标时间戳 + 对应时间差值

select 
     from_unixtime(cast(1617187200000/1000 as bigint),'yyyy-MM-dd HH:mm:ss') as ori_time
    ,from_unixtime(cast(1617187200000/1000 as bigint) + 8*60*60,'yyyy-MM-dd HH:mm:ss') as to_time
"""
+---------------------+---------------------+
|ori_time             |to_time              |
+---------------------+---------------------+
|2021-03-31 10:40:00  |2021-03-31 18:40:00  |
+---------------------+---------------------+
"""
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Hive官网说明

阿里云文档FROM_UTC_TIMESTAMP 函数说明

• Athena

// 方式1 from_unixtime(unixtime, zone) → timestamp(3) 
// 方式2 timestamp AT TIME ZONE 'zone'
// 方式3 当前时间戳 + 时区差
select 
     from_unixtime(1617187200000/1000) as ori_time
    ,from_unixtime(1617187200000/1000, 'PRC') as to_time1
    ,from_unixtime(1617187200000/1000) AT TIME ZONE 'PRC' as to_time2
    ,from_unixtime(1617187200000/1000 + 8*60*60) as to_time3
    ,date_format(from_unixtime(1617187200000/1000, 'PRC'),'%Y-%m-%d %H:%i:%s') as to_time_format

"""
+-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
|ori_time                 |to_time1                     |to_time2                     |to_time3                 |to_time_format           |
+-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
|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  |
+-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
"""
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Athena Date and time functions and operators 官网

4、时区列表

1：阿里云文档时区列表

2：timeanddate 时区列表

3：Athena 支持的时区列表