【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

• Hive SQL

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

• Athena

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

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')

• 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_unixtimeselect from_unixtime(unix_timestamp('20240501','yyyyMMdd'),'yyyy-MM-dd');

• Athena

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

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  |
+---------------------+---------------------+
"""

• Hive SQL

from_utc_timestamp(expr, timeZone)// expr：一个 TIMESTAMP 表达式，带有 UTC 时间戳。// timeZone：一个为有效时区的 STRING 表达式。// 转中国时区select 
     from_unixtime(cast(1617187200000/1000asbigint),'yyyy-MM-dd HH:mm:ss')as ori_time
    ,from_utc_timestamp(from_unixtime(cast(1617187200000/1000asbigint),'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/1000asbigint),'yyyy-MM-dd HH:mm:ss')as ori_time
    ,from_unixtime(cast(1617187200000/1000asbigint)+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  |
+---------------------+---------------------+
"""

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  |
+-------------------------+-----------------------------+-----------------------------+-------------------------+-------------------------+
"""

Athena Date and time functions and operators 官网

4、时区列表

1：阿里云文档时区列表

2：timeanddate 时区列表

3：Athena 支持的时区列表