hive和mysql中把中國時間轉為美國時間,utc
hive
1.把中国时间转为美国时间
SELECT date_format(from_utc_timestamp(to_utc_timestamp(current_timestamp(),'Asia/Shanghai'),'America/Los_Angeles'),'yyyy-MM-dd HH:mm:ss');
2.把美国时间转为中国时间
SELECT date_format(from_utc_timestamp(to_utc_timestamp('2023-08-29 17:50:16','America/Los_Angeles'),'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss');
3.把utc时间转为美国时间
SELECT from_utc_timestamp('2023-08-30 00:46:57.000000000', 'PST');
其中’2023-08-30 00:46:57.000000000’是utc时间
第二种格式:
SELECT from_utc_timestamp(from_unixtime(unix_timestamp('2023-10-30T16:00:00.000Z', 'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\'')),'America/Los_Angeles') AS pst_time;
其中:2023-10-30T16:00:00.000Z是utc时间
4.把utc时间转为中国时间
SELECT from_utc_timestamp('2023-08-30 00:46:57.000000000', 'Asia/Shanghai');
5.把中国时间转为utc
SELECT date_format(from_utc_timestamp(to_utc_timestamp('2023-08-30 08:48:58.715000000','Asia/Shanghai'),'UTC'),'yyyy-MM-dd HH:mm:ss');
6.把美国时间转为utc时间
SELECT date_format(from_utc_timestamp(to_utc_timestamp('2023-08-29 17:50:16 ','America/Los_Angeles'),'UTC'),'yyyy-MM-dd HH:mm:ss');
7.把時間戳转为pst和cst
date_format(from_utc_timestamp(approver_time * 1000, 'America/Los_Angeles'),'yyyy-MM-dd HH:mm:ss') approver_time_pst,
date_format(from_utc_timestamp(approver_time * 1000, 'Asia/Shanghai'), 'yyyy-MM-dd') approver_time_cst,
MySQL
if(date_adjust <= date_sub(now(), interval 15 hour), 1, 0) FILTER