介绍下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时间戳 165231000 转换为北京时间 -05-12 15:33:40
ClickHouse：select formatDateTime(toDateTime(165231000/1000),'%F %T', 'Asia/Shanghai')MySQL：select date_format(CONVERT_TZ(from_unixtime(165231000 / 1000, '%Y-%m-%d %H:%i:%s'),'+00:00','+08:00'),'%Y-%m-%d %T')SQL server：select CONVERT(varchar(19),DATEADD(SS,165231000 / 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(165231000 /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 + 时间戳的方式
格式化时希望增加汉字或其他字符，比如想要得到 “/5月”这样的
ClickHouse：select formatDateTime(toDateTime(165231000/1000),'%Y/%m月', 'Asia/Shanghai')MySQL：select date_format(CONVERT_TZ(from_unixtime(165231000 / 1000, '%Y-%m-%d %H:%i:%s'),'+00:00','+08:00'),'%Y/%m月')SQL server：select CONVERT(varchar(7),DATEADD(SS,165231000 / 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(165231000 /1000 + 8 * 3600 ,'second')) AT time zone tz_offset('Asia/Shanghai'),'YYYY/MM') || '月’ from dual
若是要格式化为“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
