常见数据库时间戳格式化函数及用法（按时区格式化时间戳），java计算时区值

本文链接：https://blog.youkuaiyun.com/as350144/article/details/124731297

介绍下clickhouse、mysql、SQL server、Oracle数据库中将时间戳按时区进行格式化的函数及用法。

前提

默认传入的时间戳是UTC时间

函数

ClickHouse

formatDateTime

Functions for Working with Dates and Times | ClickHouse Docs

MySQL

from_unixtime

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

convert_tz

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

date_format

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

SQL server

DATEADD

DATEPART

日期和时间数据类型及函数 - SQL Server (Transact-SQL) | Microsoft Docs

CONVERT

CAST 和 CONVERT (Transact-SQL) - SQL Server | Microsoft Docs

Oracle

TZ_OFFSET

NUMTODSINTERVAL

TO_CHAR (datetime)

实践

将 UTC时间戳 1652312020000 转换为北京时间 2022-05-12 15:33:40

ClickHouse：select formatDateTime(toDateTime(1652312020000/1000),'%F %T', 'Asia/Shanghai')
MySQL：select date_format(CONVERT_TZ(from_unixtime(1652312020000 / 1000, '%Y-%m-%d %H:%i:%s'),'+00:00','+08:00'),'%Y-%m-%d %T')
SQL server：select CONVERT(varchar(19),DATEADD(SS,1652312020000 / 1000 + 8 * 3600,'1970-01-01 00:00:00'),121)
Oracle：select TO_CHAR((TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + numtodsinterval(1652312020000 /1000 + 8 * 3600 ,'second')) AT time zone tz_offset('Asia/Shanghai'),'YYYY-MM-DD HH24:mi:ss') from dual

SQL server和Oracle对于时间戳的计算需要通过 1970-01-01 00:00:00 + 时间戳的方式

格式化时希望增加汉字或其他字符，比如想要得到 “2022/5月”这样的

ClickHouse：select formatDateTime(toDateTime(1652312020000/1000),'%Y/%m月', 'Asia/Shanghai')
MySQL：select date_format(CONVERT_TZ(from_unixtime(1652312020000 / 1000, '%Y-%m-%d %H:%i:%s'),'+00:00','+08:00'),'%Y/%m月')
SQL server：select CONVERT(varchar(7),DATEADD(SS,1652312020000 / 1000 + 8 * 3600,'1970-01-01 00:00:00'),111) + ‘月’ （截取前7个字符）
Oracle：select TO_CHAR((TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + numtodsinterval(1652312020000 /1000 + 8 * 3600 ,'second')) AT time zone tz_offset('Asia/Shanghai'),'YYYY/MM') || '月’ from dual

若是要格式化为“2022年5月”，那么对于SQL server和Oracle的支持就不是很好

java中时区值计算

//时区处理
TimeZone timeZone = TimeZone.getTimeZone(cqb.getTimeZone());
//时区值
long rawOffset = timeZone.getRawOffset();
int hour =(int) rawOffset  / 1000 / 60 / 60;
//可为负数
System.out.println(hour);

Note：

java中日期格式化字符串需要转义，例如：%Y-%m-%d 需要写成 %%Y-%%m-%%d

参考资料

https://www.it1352.com/2441543.html
https://www.runoob.com/sql/func-datepart.html
https://blog.youkuaiyun.com/educast/article/details/6883231
https://www.w3school.com.cn/sql/func_date_format.asp