dirver时区_时区 - silyvin - 博客园

0 原则

0.1 前端(浏览器时间)-json序列化-jdbcurl(服务器内存，db时区相对于服务器及jvm时区)-db(db时间)

0.2 时间戳在地球的每一个角落都是相同的，但是在相同的时间点会有不同的表达方式，所以有了另外一个时间概念，叫时区。

结论：

时间戳代表绝对时间

mysql db datetime存日期时间字符串，timestamp存时间戳(绝对时间)

db建库前应核准时区，通过select now和show variables like '%time_zone%'

jdbc 日期时间字符串参与传输，时区及时间戳不参与传输，故应用层应在内存做好server-db的时区转换字符串

jdbc开启url时区，insert prepared statement与query getObject会自动转换时区；insert 字符串不会

建库后，应随即进行server-db时区 查询、插入更新核准

Date与Timestamp差不多，都包含了时区信息与时间戳；序列化框架不会改时间戳，但是会根据序列化框架的默认时区转显示日期时间

国际化应用应建立 浏览器-服务器-db时区链，前者通过request response 增加浏览器时区参数，反射调整Date类型；后者通过内存手动或jdbc调整时区

sequel或sql developer不会像jdbc那样帮忙转显示日期时间，显示的日期时间就是db timezone的；证据：1)该客户端连接db时无需db时区，仅有操作系统时区它也无从下手(当然不排除它自己去获取服务器时区，但可能性不大)；2)修改本地时区，sql developer上的显示时间不会变，说明即使它自己去拿了服务器时区，也没有将服务器时区与本地时区转换调整显示时间，sql dev就是按服务器时区显示时间

经oracle实践成功

1 国际化最佳实践：

1.1 db最好存储时间戳，时区无关；mysql的Datetime类型就是纯日期型，没有时区信息，你db哪个时区，它都显示一样的日期时间，一旦db时区被修改，绝对时间点信息则丢失，3.4证明

1.2 db时区最好取0时区，select now()核准当前session时区，show variables like '%time_zone%'核准全局时区

1.3 若像本文手动处理服务器Date-db的转换，应jdbc url不另外设置时区；

spring jdbctemplate/mybatis

jdbc 所有query

jdbc prepared statement insert

选择url处理，url的时区==db时区，服务器有自己的时区信息，但需要额外知道db是什么时区，将服务器时间与url中的db时区做转换

比如，服务器+8北京，db 0 UTC，插入更新时Date-8；查询时Date+8

jdbc string only insert

应用层 插入更新与查询分别自行反向处理

*url中的时区信息仅对mybatis 等框架以及jdbc prepared statement插入更新有用，jdbc字符串插入更新操作本身不处理这个参数

*mysql jdbc driver 无论是字符串、还是setTimestamp，最终转换为时区无关的日期时间字符串(1)(2)，时间戳不参与通信，一律使用date字符串tcp通信；而且这个字符串，db服务器接收到后默认为db时区的日期时间(3)

插入时，driver接收到参数，先用服务器时区与db时区比划转换date，然后传输date字符串，db接收到date，用db时区转为时间戳入库相应的timestamp类型字段，date类型字段则直接入库

查询时，db对于date类型字段直接返回，对于timestamp类型字段，通过db时区搞成date字符串传输，jvm拿到date，再用本地时区与db时区比划转换

【这是未证明的重要假定】：初步依据有：

1)从insert日期字符串可见一斑，时区及时间戳在sql中没有；而且很可能mysql jdbc文本协议，通过抓包未发现除sql文本外其它明显的涉及时区的传输https://www.oschina.net/question/1175066_235198?sort=time

2)prepeared statement insert也是业务层做时区转换，传输日期时间字符串

3)如果传输 服务器时区日期时间字符串，db没有办法锁定绝对时间即时间戳；

如果传输 UTC 0 时区字符串，那么jcbc url中就不需要指明db时区了，何必多此一举

因此，传输的是db时区的日期时间字符串，db再用自己的时区搞成绝对时间

4)7.1.2，7.2.1，黄色背景蓝色字，这是本文最重要的结论

具体流程，见附录图

1.4 应用层依据db时区(z)，做服务器时间(x)核准【时区核准方法论】

1.5 序列化配合浏览器做个性化json序列化，假设request里包含了一个时区，那么server接收到request，反序列化后，扫描request所有Date类型参数，调整时区；response反射扫描所有Date类型，以这个时区参数序列化

*序列化springboot默认有时候用UTC，比如7.1.3

1.6 前端依据服务器时间(x)，做浏览器端时间(y)核准

1.7【时区核准方法论】：

1.7.1 插入，服务器时间 new Date.getTime 与 db的timestamp (select UNIX_TIMESTAMP(date_field) from my_orm)两个时间戳和谐

1.7.2 查询，db的timestimp(select UNIX_TIMESTAMP(date_field) from my_orm)与内存中查出来的Date.getTime时间戳和谐

2 实践准备

2.1 show variables like "%time_zone%";

system_time_zone UTC

time_zoneUTC

docker 的mysql镜像

2.2 select now();

2020-04-14 08:16:03

实际北京时间下午16点16分

3 预先数据

3.1

显示

2019-08-08 21:33:44

2019-08-08 21:33:44

时间戳

1565300024

1565300024

实际北京时间

2019-08-09 05:33:44

2019-08-09 05:33:44

*select unix_timestamp(f_date), unix_timestamp(f_timestamp) from my_timezone

因此，sequel pro显示的是db所在UTC时区时间，不是北京时间，sequel显示的时间你要自己转换，它不会像jdbc那样帮你转换，不要相信自己的眼睛

3.2 修改时区

> set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间，即我们所在的东8区

3.3 修改后显示时间为

3.3.1

显示

2019-08-08 21:33:44

2019-08-09 05:33:44

时间戳

1565271224

1565300024

实际北京时间

2019-08-08 21:33:44

2019-08-09 05:33:44

3.3.2 select now()

2020-04-14 16:29:56

3.4 可以看到

datetime类型，显示没变，时间戳变了

timestamp类型，显示变了，时间戳没变

证明datetime存储可想为一个字符串，该字符串的显示不会随db时区变化而变化，但它表达的时间点会随db时区变化而变化

而timestimp即是一个时区无关的long，它的显示会随着db时区变化而变化，但它表达的时间点不会

4 jdbc 查询核准

set global time_zone = 'UTC';

set time_zone = 'UTC';

private static final String URL_NO_TIMEZONE="jdbc:mysql://127.0.0.1:53306/mytest?useUnicode=true&characterEncoding=utf-8&useSSL=false";

private static final String URL="jdbc:mysql://127.0.0.1:53306/mytest?useTimezone=true&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";

4.1 内存date转换后时间戳

{f_date=2019-08-08 21:33:44.0, f_timestamp=2019-08-08 21:33:44.0, id=1}  未转换，jdbc直接出来Timestamp类型，根据debug，显示+0800时区(可以想象，jdbc用一个Timestamp对象接收，初始化Timestamp对象时继承了jvm的时区+0800，再把db的日期时间字符串塞入)

transferred timestamp : 转换后datetime字段时间戳：1565271224000

transferred timestamp : 转换后timestamp字段时间戳：1565271224000

{f_date=2019-08-09 05:33:44.0, f_timestamp=2019-08-09 05:33:44.0, id=1} jdbc url转换，同样Timestamp类型，根据debug，显示+0800时区

transferred timestamp : 转换后datetime字段时间戳：1565300024000

transferred timestamp : 转换后timestamp字段时间戳：1565300024000

设只有字符串参与传输

对于datetime，直接传输

对于timestamp，db根据时区搞成日期时间字符串后回传

前者将sequel显示的UTC时间传过来了，这个数据还要本地jvm时区(上海)化，21点的北京时间与db UTC 21点差异

后者帮忙转成北京时间了

4.2

select unix_timestamp(f_date), unix_timestamp(f_timestamp) from my_timezone

15653000241565300024

4.3 根据1.7.2，

后者与db一致，前者错误

5 insert 字符串日期时间

String tt = "2020-04-14 14:47:19";

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date date = sdf.parse(tt);

System.out.println(date);

System.out.println(date.getTime());

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO `my_timezone` (`f_date`, `f_timestamp`)\n" +

"VALUES\n" +

"\t('" + sdf.format(date) + "', '"+ sdf.format(date) +"')");

pstmt.executeUpdate();

注意，jdbc url带时区信息

useTimezone=true&serverTimezone=UTC&

我们本意插入服务器所在时区 4.14 下午2:47分的时间数据

设只有字符串参与传输

对于datetime，直接入库

对于timestamp，db根据时区搞成日期时间字符串后入库

5.1

origin jvm date : Tue Apr 14 14:47:19 CST 2020 Date类型，根据debug，显示+0800时区

origin timestamp : 1586846839000

5.2 db

102020-04-14 14:47:192020-04-14 14:47:19，当我从sequel看到这个值，意味着它表示UTC时间 4.14 下午2:47分，而不是服务器所在时区的4.14 下午2:47分

select unix_timestamp(f_date), unix_timestamp(f_timestamp) from my_timezone

15868756391586875639错，北京时间 2020-04-14 22:47:19

5.3 根据1.7.1，该方法不可直接用，除非db与server时区相同

6 prepared statement 插入

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO `my_timezone` (`f_date`, `f_timestamp`)\n" +

"VALUES\n" +

"\t(?, ?)");

String tt = "2020-04-14 14:47:19";

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date date = sdf.parse(tt);

System.out.println(date);

System.out.println(date.getTime());

pstmt.setTimestamp(1, new Timestamp(date.getTime()));

pstmt.setTimestamp(2, new Timestamp(date.getTime()));

pstmt.executeUpdate();

6.1

origin jvm date : Tue Apr 14 14:47:19 CST 2020 Date类型，根据debug，显示+0800时区

origin timestamp : 1586846839000

6.2 db

132020-04-14 06:47:192020-04-14 06:47:19UTC6点，北京14点，ok

select unix_timestamp(f_date), unix_timestamp(f_timestamp) from my_timezone

15868468391586846839

6.3 根据1.7.1，该方法可用

7 myrom

7.1 查询

7.1.1 db：

12019-08-08 21:33:442019-08-08 21:33:44

select unix_timestamp(f_date), unix_timestamp(f_timestamp) from my_timezone

15653000241565300024

7.1.2 内存：

origin db date : 2019-08-08 21:33:44.0 未转换，jdbc直接出来Timestamp类型，根据debug，显示+0800时区，这一步看出已经有问题了，库里是UTC的21:33，出来变成+8时区的21:33，说明库里db的时区UTC没有参与db-》server的传输

2020-04-14 22:31:10.347 [https-jsse-nio-8080-exec-8] INFO com.example.demo.testcase.timezone.TimezoneManager - server zone offset : 28800000 +8

2020-04-14 22:31:10.358 [https-jsse-nio-8080-exec-8] INFO com.example.demo.testcase.timezone.TimezoneManager - db zone offset : 0

2020-04-14 22:31:10.359 [https-jsse-nio-8080-exec-8] INFO com.example.demo.testcase.timezone.TimezoneManager - timezone transfer value : 8

transferred timestamp : 1565300024000

class java.util.Date:class java.sql.Timestamp

origin db date : 2019-08-08 21:33:44.0 未转换，jdbc直接出来Timestamp类型，根据debug，显示+0800时区，这一步看出已经有问题了，库里是UTC的21:33，出来变成+8时区的21:33

transferred timestamp : 1565300024000

查询 : Fri Aug 09 05:33:44 CST 2019 转换后，Date类型，根据debug，显示+0800时区

查询 : Fri Aug 09 05:33:44 CST 2019 转换后，Date类型，根据debug，显示+0800时区

查询 : 1565300024000

查询 : 1565300024000

7.1.3，默认springboot使用UTC时区序列化到客户端

{"id":1,"f_date":"2019-08-08T21:33:44.000+0000","f_timestamp":"2019-08-08T21:33:44.000+0000"}

从内存的+8时区5:33，不转换(因为序列化传输本身包含了时区信息，不同于jdbc通信，没有时区信息，见【这是未证明的重要假定】)，只是改变显示，到浏览器成UTC的21:33 (-1天)，与3.1呼应

7.2 插入

7.2.1 内存：

插入 : Tue Apr 14 14:47:19 CST 2020 Date类型，根据debug，显示+0800时区

插入 : 1586846839000

origin timestamp : 1586846839000

transferred db date : Tue Apr 14 06:47:19 CST 2020 表示+8的6:47，可以看到，与7.2.2相比(UTC的6:47)，server jvm的时区CST不参与server-》db的传输

origin timestamp : 1586846839000

transferred db date : Tue Apr 14 06:47:19 CST 2020

7.2.2 db：

182020-04-14 06:47:192020-04-14 06:47:19变成了UTC的6:47

15868468391586846839

8 oracle实践

8.1 背景

select dbtimezone from dual;

DBTIMEZONE:-04:00

select sessiontimezone from dual;

SESSIONTIMEZONE:Asia/Shanghai

8.2 方式：

8.2.1 jvm内存-db核准，根据1.7.1 、1.7.2

oracle没有mysql unix_timestamp的函数，无法让我从原位获取原始long型时间戳

8.2.2

老插新查 ok

新插老查 ok

9 2020.5.12 补充

#url设置为UTC(db)会导致查询时orm TimezoneController返回错误，因为经过2次转换，相当于+16时区；insert由于是手动转换字符串不受影响

#url应设置为+8，与服务器一致，免去jdbc自行根据url的自动时区处理

#jdbc.sybase.url=jdbc:mysql://127.0.0.1:53306/mytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&useTimezone=true&serverTimezone=UTC

jdbc.sybase.url=jdbc:mysql://127.0.0.1:53306/mytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&useTimezone=true&serverTimezone=Asia/Shanghai

#jdbc.sybase.url=jdbc:mysql://127.0.0.1:53306/mytest?useUnicode=true&characterEncoding=utf-8&useSSL=false

10 2020.5.28 出现问题

引出：

北京时间2.05pm在服务器(-5)新后台插入，刷新后显示2.05pm，但本地(-8)新后台显示3.05pm

发现插入的new Date，入db后为3.05am(-4时区)，理论上北京的2.05pm在库(-4)中应为2.05am，此处为3.05am，所以入库的时间错了，判断insert时时区转换没做好

排查：

server zone offset : -18000000　　-5

db zone offset : -4

timezone transfer value : -1

可以看到程序按服务器为-5时区来处理，然而，new Date显示为：

origin timestamp : 1590645901950  2020/5/28 14:5:1(北京)

transferred db date : Thu May 28 03:05:01 EDT2020　　可以看到jvm以EDT(美国东部夏令时-4)在处理Date类型

linux：date显示与北京12小时时差

锁定：

种种迹象表明-new Date给我-4的时间，然而，TimeZone.getDefault给了我-5，尼玛

在北京3：54 pm再次补全日志操作确认一次：

Date date = new Date();

int hourMod = date.getTimezoneOffset();

log.info("origin new Date zone {}", hourMod);

插入前后

// logger.info("origin timestamp : {} {}", date, date.getTime());

date = TimezoneManager.timezoneJvmToDbWhenInsertAndUpdate(date);

// logger.info("transferred db date : {} {}", date, date.getTime());

origin new Date zone 240

server zone offset : -18000000

db zone offset : -4

timezone transfer value : -1

尼玛，new Date的时区和TimeZone.getDault的时区不一样

2020-05-28 03:54:08.707 [ GUI-Thread-11, TaskID:257, Start Time:05-28 03:54:08 ] - [ INFO ] [ : -1 ] - origin timestamp : Thu May 28 03:54:08 EDT 2020 1590652448500　　与北京相差12小时，服务器确实以-4在处理Date

2020-05-28 03:54:08.708 [ GUI-Thread-11, TaskID:257, Start Time:05-28 03:54:08 ] - [ INFO ] [ : -1 ] - transferred db date : Thu May 28 04:54:08 EDT 2020 1590656048500

修复：

static {

TimeZone timeZoneCurrent = TimeZone.getDefault();

int offset = timeZoneCurrent.getRawOffset();

log.info("server zone offset : {}", offset);

log.info("db zone offset : {}", db);

int hour = offset/1000/60/60;

{

Date date = new Date();

int hourMod = -date.getTimezoneOffset() / 60;

log.info("origin new Date zone {}", hourMod);

if(hourMod != hour) {

log.info("[warn] - hourMod not eq hour");

hour = hourMod;

}

}

timezonePlus = hour - db;

log.info("timezone transfer value : {}", timezonePlus);

}

北京时间4.23pm操作，服务器(-4)新后台显示4.23pm插入——本地(+8)新后台check刚才那条时间为4.23pm——老后台check 4.23pm，db(-4)显示4.23am，done

2020-05-28 04:16:31.492 [ GUI-Thread-6, TaskID:95, Start Time:05-28 04:16:31 ] - [ INFO ] [: -2 ] - server zone offset : -18000000

2020-05-28 04:16:31.497 [ GUI-Thread-6, TaskID:95, Start Time:05-28 04:16:31 ] - [ INFO ] [: -2 ] - db zone offset : -4

2020-05-28 04:16:31.497 [ GUI-Thread-6, TaskID:95, Start Time:05-28 04:16:31 ] - [ INFO ] [: -2 ] - origin new Date zone -4

2020-05-28 04:16:31.497 [ GUI-Thread-6, TaskID:95, Start Time:05-28 04:16:31 ] - [ INFO ] [: -2 ] -[warn] - hourMod not eq hour

2020-05-28 04:16:31.498 [ GUI-Thread-6, TaskID:95, Start Time:05-28 04:16:31 ] - [ INFO ] [: -2 ] - timezone transfer value : 0

11 突然反应过来：目前的双数据源代码不支持各自时区，时区校正作为全局变量存在，若要达到各自时区校正，要与各数据源的ormsession绑定

12 双数据源时区

mysql 5.7　　UTC　　12019-08-08 21:33:442019-08-08 21:33:44　　15653000241565300024

mysql   8　　　-4　　12019-08-08 17:33:442019-08-08 17:33:44　　15653000241565300024

*mysql的jdbc：useTimezone=true&serverTimezone=Asia/Shanghai

12.1 查询

mysql 5.7　　{"id":1,"f_date":"2019-08-08T21:33:44.000+0000","f_timestamp":"2019-08-08T21:33:44.000+0000"}

查询 : 1565300024000

查询 : 1565300024000

mysql 8.　　  {"id":1,"f_date":"2019-08-08T21:33:44.000+0000","f_timestamp":"2019-08-08T21:33:44.000+0000"}

查询 : 1565300024000

查询 : 1565300024000

12.2 插入　　　　　　　　　　2020-04-14 14:47:19 +8 　　　　　　　　1586846839000

mysql 5.7　　UTC　　52020-04-14 06:47:192020-04-14 06:47:19　　15868468391586846839

mysql 8　　   -4　　  62020-04-14 02:47:192020-04-14 02:47:19　　15868468391586846839

13 附录图

package com.example.demo.testcase.timezone;

import com.example.demo.testcase.ScefLogbackFactory;

import java.util.Calendar;

import java.util.Date;

import java.util.TimeZone;

/**

* https://www.cnblogs.com/silyvin/p/12696872.html

* Created by joyce on 2020/4/14.

*/

public class TimezoneManager {

private static final ScefLogbackFactory.ScefLogger log = ScefLogbackFactory.getLogger(TimezoneManager.class);

private static int timezonePlus;

private static int db = 0;

static {

TimeZone timeZoneCurrent = TimeZone.getDefault();

int offset = timeZoneCurrent.getRawOffset();

log.info("server zone offset : {}", offset);

log.info("db zone offset : {}", db);

int hour = offset/1000/60/60;

timezonePlus = hour - db;

log.info("timezone transfer value : {}", timezonePlus);

}

public static int getTimezonePlus() {

return timezonePlus;

}

private static Date dealTimeZone(Date date, int offset) {

Calendar cal = Calendar.getInstance();

cal.setTime(date);

cal.add(Calendar.HOUR, offset);

return cal.getTime();

}

public static Date timezoneJvmToDbWhenInsertAndUpdate(Date date) {

return dealTimeZone(date, -TimezoneManager.getTimezonePlus());

}

public static Date timezoneDbToJvmWhenQuery(Date date) {

return dealTimeZone(date, TimezoneManager.getTimezonePlus());

}

}

query: 会将java.sql.Timestamp 转为java.util.Date

// https://www.cnblogs.com/silyvin/p/12696872.html

if(val instanceof java.util.Date) {

Date date = (Date)val;

System.out.println("origin db date : " + date);

date = TimezoneManager.timezoneDbToJvmWhenQuery(date);

System.out.println("transferred timestamp : " + date.getTime());

val = date;

}

field.set(domain, val);

}

listDomain.add(domain);

} catch (Exception e) {

throw new DBException(e);

}

}

return listDomain;

insert:

// https://www.cnblogs.com/silyvin/p/12696872.html

if(o instanceof java.util.Date) {

Date date = (Date)o;

System.out.println("origin timestamp : " + date.getTime());

date = TimezoneManager.timezoneJvmToDbWhenInsertAndUpdate(date);

System.out.println("transferred db date : " + date);

domain.setFieldValue(date);

}

String domainFieldName = domain.getFieldName();

domain.setFieldName(domainFieldName.replaceAll("[A-Z]", "_$0").toLowerCase());

if(dealWithJoinFieldUpdate(domain, field, o))

list.add(domain);

}

ormUnit.setListDomain(list);

return ormUnit;

}

14

2020.7.29补充

oracle

mysql

说明

timestamp

datetime

只有时间字符串，无论哪个时区，返回的就是字符串

timestamp local timezone

timestamp

时间字符串+oracle时区；

绝对时间戳(但jdbc协议只传输时间字符串，所以这个时间戳按timezone折合，客户端依靠url timezone再次折合为客户端本地时间)

timestamp timezone

时间字符串+oracle客户端session时区

2020.9.18补充

https://m.newsmth.net/article/Database/61083 这篇文章经典，证实了这种说法

Oracle 9i 开始向timestamp引进time zone概念。

10g提供了

timestamp

timestamp with timezone

timestamp with local timezone

几种数据类型。

这里比较一下这些数据类型的差异。

一个统一的时间应由 时刻+时区来指定，

比如2005-4-6 14:00:00.000并不能说清楚到底这是

日本的下午2点还是中国的下午两点。

2005-4-6 14:00:00.000 +8:00 才是北京时间

2005-4-6 14:00:00.000 +9:00 则是东京时间

假设有一个online meeting system

DB服务器在英国 dbtimezone (+0:00) [select dbtimezone from dual;]

一个客户端c-cn在中国 session timezone (+8:00) [select sessiontimezone from

dual;]

一个客户端c-jp在日本 session timezone (+9:00) [select sessiontimezone from

dual;]

管理客户端c-en在英国 session timezone (+0:00) [select sessiontimezone from

dual;]

===================================================

Timestamp 不能包含任何时区信息

===================================================

DB有TABLE定义如下：

create table meeting_table( id number(10) primary key, ctime timestamp );

中国用户插入一个会议，早上8点开会

insert into meeting_table values (1, '05-06-29 8:00:00,000');

commit;

日本用户也插入一个会议，早上8点开会

insert into meeting_table values (2, '05-06-29 8:00:00,000');

commit;

英国的管理员查询一下这张表，发现两个会议是同时的，

ID CTIME

---------- ------------------------------

1 05-06-29 08:00:00,000000

2 05-06-29 08:00:00,000000

而实际上应该日本的会议比中国的早一个小时。

英国的管理员如果想参加2号会议的话，他到底该几点去呢？

八点？0点？前一天的晚上11点？

数据库完全不能给他一个明确的答复。

c-cn, c-jp来查询也都得到相同的模糊结果：

ID CTIME

---------- ------------------------------

1 05-06-29 08:00:00,000000

2 05-06-29 08:00:00,000000

===================================================

Timestamp with time zone 显式包含时区信息

===================================================

DB有TABLE定义如下：

create table meeting_table2( id number(10) primary key, ctime timestamp with

time zone);

中国、日本用户同样插入上例中的两个会议

英国的管理员查询表时，返回的结果就清晰多了：

select * from meeting_table2;

ID CTIME

---------- ----------------------------------------

1 05-06-29 08:00:00,000000 +08:00

2 05-06-29 08:00:00,000000 +09:00

他可以知道 meeting 2是在东九区的早上八点开始的，去参加的话，

前一天晚上11：00他就要接进web meeting了。

c-cn, c-jp来查询也都与c-en结果相同

结果都包含时区信息，所以是精确的，不可能被混淆。

===================================================

Timestamp with local time zone 隐式式包含时区信息

===================================================

用Timestamp with local time zone插入或显示的时间信息会根据

客户session里面时区的不同自动转换：

* 插入时，从客户端的时区 转到 数据库时区

* 显示时，从数据库时区 转到 客户端的时区

DB有TABLE定义如下：

create table meeting_table3( id number(10) primary key, ctime timestamp with

local time zone);

c-cn, c-jp同样插入上例中的两个会议

c-en 的查询结果：

ID CTIME

---------- ----------------------------------------

1 05-06-29 00:00:00,000000

2 05-06-28 23:00:00,000000

c-cn 的查询结果：

ID CTIME

---------- ----------------------------------------

1 05-06-29 08:00:00,000000

2 05-06-29 07:00:00,000000

c-jp 的查询结果：

ID CTIME

---------- ----------------------------------------

1 05-06-29 09:00:00,000000

2 05-06-29 08:00:00,000000

这样连换算都不需要了，每个客户查出来的时间

直接就是客户端所在的区域的当地时间。

不过需要注意的是：timestamp with local time zone

应用在c-s结构中没有问题，但在三层结构中，由于app server

是db server的客户端，所以转换发生在 app server - db server

之间，而非 db - browser之间。所以应用timestamp with local time zone

可能引发潜在的问题。

===================================================

常用命令：

===================================================

更改 session time zone:

alter session set time_zone='+9:00';

取得 server 当地时间：

select systimestamp from dual;

取得以客户session时区表示的 server 时间

select current_timestamp from dual;

数据库的时区是创建数据库时设置的，

用 alter database set time_zone='0:00'

可以更改。但是如果数据库已经有 timestamp with local time zone

类型的数据时，不能更改数据库时区。

15

2020.8.8 补充

mysql的timezone表示，我mysql建立连接时将以什么时区率先解析一次时间戳，你客户端再解析一次

16

2020.8.17补充

第10点中的问题：

尼玛，new Date的时区和TimeZone.getDault的时区不一样

原来是有夏令时到冬令时的区别

1)oracle

在整个我们ormsession开发过程中，2020.4.13开始涉及到时区，db在纽约，oracle实践时，

select dbtimezone from dual;

DBTIMEZONE:-04:00

显示在-4区，而纽约在西五区，这里面就是oracle启动了夏令时

如果在冬天执行这条语句，显示的很有可能是-5

2)linux

到第10点出现的时刻，2020.5.28，也就是首次部署到服务器(也在纽约)，出现Timezone.getDault.getRawOffset与new Date所展现的所在时区不一致，这是linux夏令时的表现

https://zhuanlan.zhihu.com/p/98424435

TimeZone itemTimeZone = TimeZone.getTimeZone(时区名);

itemTimeZone.getOffset(long data);//显示当前时区和0时区的偏移量,和令时制相关

itemTimeZone.getRawOffset();//显示当前时区和0时区的偏移量,和令时制无关

另一个linux处理夏令时的证据为，linux date显示时间换算后，时区是在-4区，与北京相差12小时

3)纽约

西五区

当地时间 2020年03月08日，02:00:00 时钟向前调整 1 小时 变为 2020年03月08日，03:00:00，开始夏令时，此时为-4，与北京差12

纽约在当地时间 2020年11月01日，02:00:00 时钟向后调整 1 小时 变为 2020年11月01日，01:00:00，结束夏令时，此时为-5，与北京差13

4)项目

项目oracle使用timestamp，近乎相当于字符串

原项目不对时区做任何处理，依靠服务器

new Date-> date string ->jdbc ->oracle

那么在本项目中，我们在sqldeveloper等工具看到的日期时间(字符串)不同季节代表了不同时区的时间

2020.3.7 2:00:00，代表-5区的2点

2020.4.28 2:00:00，代表-4区的2点

5)解决方案

此前无论是服务器还是数据库，时区都是写死的(包括mybatis timezonehandlermybatis orm解决方案)，服务器也仅是加载时自动按new Date计算，但一旦到冬天，需要重新启动jvm

第4点的项目背景决定了，要运行期动态判断夏令时还是冬令时

开1个后台线程，每天2:01执行，判断是否已经到更改令时的日期了，如果是，将内存中已计算的服务器时区和写死在代码中的db时区一起更改

6)如果不改——纽约

我们处理时区，是处理时区的相对值，只要能保证db和服务两边相对时区差一致即可：

比如

夏天服务器-4，db-4

冬天服务器-5，db-5

冬天服务器还以-4，db也还以-4，并不产生差别

再比如

冬天，原来为我们修正时区的new Date时区显示为-5，db还用写死的-4，那就产生1小时的差距，这就要求运行期修改写死的db时区-4更改为-5

所以，timezone继续使用Timezone.getDefault.getRawOffset，让其返回纽约-5，然后oracle db的时区也在代码中定为-5，则可，这样对于夏令时，两边仍取-5，但相对差不变

7)如果不改——北京

第6)点所述，要求服务器和db同时开启夏令时或冬令时，冬天和夏天的相对时区差不变

而北京没有夏令时，北京时间14:00pm在夏天和冬天对纽约有不同的时间概念，入库应不同(除非就约定，库里的时间就是-5或-4区的，相应的，服务器做额外的令时转换再给用户)

夏天服务器+8，db-4

冬天服务器+8，db-5

不像服务器和db都在纽约，或服务器在其他与纽约有同样令时规则的地区，并开启linux令时功能，夏天和冬天的14:00pm入库都是14:00

夏天服务器洛杉矶 -7，db-4

冬天服务器洛杉矶-8，db-5

都相差3小时

8)本质

本来，服务器所在时区非实时动态，db时区静态

现在，服务器所在时区要实时动态，db时区实时动态

纽约的时区会自己变，而北京时区固定

17

2020.9.18 补充

要解决16的问题，需要开1个后台线程，每天2:01执行，判断是否已经到更改令时的日期了，如果是，将内存中已计算的服务器时区和写死在代码中的db时区一起更改实时更新服务器和db的时区(潜在夏令时)

1) jvm当前时区

相对好处理，Date，包含了daylight

需要确认jvm不重启情况下，跨冬夏时前后jvm自动处理daylight

2) db时区

已经知道db 纽约(-5)，需要如果从任何地区服务器(假定服务器不在纽约)得知某Date时间戳对应于纽约的daylight(是否冬令时+0还是夏令时+1)

public static void main(String [] f) throws Exception {

TimeZone thisTimeZone = TimeZone.getDefault();

TimeZone newYorkTimeZone = TimeZone.getTimeZone("America/New_York");

System.out.println(thisTimeZone);

System.out.println(newYorkTimeZone);

String [] ddsBJ = {

"2020-03-08 14:59:59",

"2020-03-08 15:00:01",

"2020-11-01 13:59:59",

"2020-11-01 14:00:01"

};

// 系统切换到美东时区

String [] ddsEST = {

"2020-03-08 01:59:59",

"2020-03-08 02:00:01", // 实际不存在这个美东时间

"2020-10-31 12:59:59",

"2020-11-01 01:00:01" // 代表2个时间，回拨前后都有1：00：01

};

String strDateFormat = "yyyy-MM-dd HH:mm:ss";

SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);

for(String dd : ddsBJ) {

//for(String dd : ddsBJ) {

Date date = sdf.parse(dd);

System.out.println(date);

Date convert = convertTimezone(date, thisTimeZone, newYorkTimeZone);

System.out.println(convert);

boolean isSummer = isSummer(date, newYorkTimeZone);

System.out.println(isSummer);

}

}

/**

* http://www.timeofdate.com/city/United%20States/New%20York%20City/timezone/change

* https://www.cnblogs.com/timfruit/p/11788366.html

* @param sourceDate

* @param sourceTimezone

* @param targetTimezone

* @return

*/

public static Date convertTimezone(Date sourceDate, TimeZone sourceTimezone, TimeZone targetTimezone){

Calendar calendar = Calendar.getInstance();

long sourceTime = sourceDate.getTime();

calendar.setTimeInMillis(sourceTime);

System.out.println(sourceTime);

calendar.setTimeZone(sourceTimezone);

int sourceZoneOffset = calendar.get(Calendar.ZONE_OFFSET);

int sourceDaylightOffset = calendar.get(Calendar.DST_OFFSET);

calendar.setTimeZone(targetTimezone);

int targetZoneOffset = calendar.get(Calendar.ZONE_OFFSET);

int targetDaylightOffset = calendar.get(Calendar.DST_OFFSET);

long targetTime = sourceTime + (targetZoneOffset + targetDaylightOffset) - (sourceZoneOffset + sourceDaylightOffset);

return new Date(targetTime);

}

public static boolean isSummer(Date sourceDate, TimeZone targetTimezone){

Calendar calendar = Calendar.getInstance();

long sourceTime = sourceDate.getTime();

calendar.setTimeInMillis(sourceTime);

// calendar.setTimeZone(sourceTimezone);

// int sourceZoneOffset = calendar.get(Calendar.ZONE_OFFSET);

// int sourceDaylightOffset = calendar.get(Calendar.DST_OFFSET);

calendar.setTimeZone(targetTimezone);

int targetZoneOffset = calendar.get(Calendar.ZONE_OFFSET);

int targetDaylightOffset = calendar.get(Calendar.DST_OFFSET);

return targetDaylightOffset != 0;

}

String [] ddsBJ = {

"2020-03-08 14:59:59",

"2020-03-08 15:00:01",

"2020-11-01 13:59:59",

"2020-11-01 14:00:01"

};

// 系统切换到美东时区

String [] ddsEST = {

"2020-03-08 01:59:59",

"2020-03-08 02:00:01", // 实际不存在这个美东时间

"2020-11-01 00:59:59",

"2020-11-01 01:00:01" // 代表2个时间，回拨前后都有1：00：01

};

输出

sun.util.calendar.ZoneInfo[id="Asia/Shanghai",offset=28800000,dstSavings=0,useDaylight=false,transitions=29,lastRule=null]

sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]]

Sun Mar 08 14:59:59 CST 2020

1583650799000

Sun Mar 08 01:59:59 CST 2020

false

Sun Mar 08 15:00:01 CST 2020

1583650801000

Sun Mar 08 03:00:01 CST 2020

true

Sun Nov 01 13:59:59 CST 2020

1604210399000

Sun Nov 01 01:59:59 CST 2020

true

Sun Nov 01 14:00:01 CST 2020

1604210401000

Sun Nov 01 01:00:01 CST 2020

false

sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]]

sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]]

Sun Mar 08 01:59:59 EST 2020

1583650799000

Sun Mar 08 01:59:59 EST 2020

false

Sun Mar 08 03:00:01 EDT 2020

1583650801000

Sun Mar 08 03:00:01 EDT 2020

true

Sat Oct 31 12:59:59 EDT 2020

1604206799000　　　　比北京的2020-11-01 13:59:59少3600s，一个小时

Sat Oct 31 12:59:59 EDT 2020

true

Sun Nov 01 01:00:01 EST 2020

1604210401000

Sun Nov 01 01:00:01 EST 2020

false　　优先以冬令时处理

3)对原系统的验证

北京时间

2020-11-01 13:50

2020-11-01 14:10-2020-11-01 13:10

在原系统插入2条，看时间，第2条会不会在第1条前order

纽约linux系统查看是否改变时区

纽约db oracle查看是否改变时区

4)此前使用服务器当前时间判断时区差，这只能解决服务器-》db的问题，通过获取服务器某Date对应于db 纽约的夏/冬

但不能解决db-》服务器的查询，比如db有数据：

服务器-在北京

对于sqldeveloper中28-FEB-18 02.05.59.285000000 AM (逻辑上代表，因为oracle timestamp无时区信息，该字符串体现了原项目-部署在纽约的服务器时间意志，即自己调整夏/冬的linux当前时间)

代表纽约冬令时上午2点——对应北京时间当天下午3点

然而我们的查询检测时，28-Feb-2018, 02:05 PM CST，检测当前纽约为夏天，与北京12小时时差，以此转换，出错，【第一个错误】原因为错误的使用了当前时间戳判定纽约所处daylight，对于db中既有数据则出错

老后台db-服务器无任何时区处理，不错，服务器到前端处理成了03：05 pm

部署在纽约的新后台，服务器与db由于都在纽约，无时差，处理为了03:05pm，说明纽约的后台-北京的前端处理正确

5)为了解决db-》server的查询，使用两阶段转换

按标准时转换

查看该时间在纽约是否是夏天

会有边界点

abstract public class TimezoneTypeHandler extends BaseTypeHandler {

protected int dbZone;

public TimezoneTypeHandler(int dbZone) {

this.dbZone = dbZone;

}

abstract int dayLight(Date sourceDateJvm);

@Override

public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date dateJvm, JdbcType jdbcType) throws SQLException {

if(dateJvm != null) {

Date dateDb = TimezoneManager.timezoneJvmToDbWhenInsertAndUpdate(dateJvm, this.dbZone + dayLight(dateJvm));

preparedStatement.setTimestamp(i, new java.sql.Timestamp(dateDb.getTime()));

}

}

。。。

private Date getJvmDateByDbDate(java.sql.Timestamp dateDb) {

if(dateDb == null) return null;

Date firstTurn = TimezoneManager.timezoneDbToJvmWhenQuery(new Date(dateDb.getTime()), this.dbZone);

return TimezoneManager.timezoneDbToJvmWhenQuery(new Date(dateDb.getTime()), this.dbZone + dayLight(firstTurn));

}

/**

* whether the jvm datetime in targetTimezone is in summer according to the timestamp

* @param targetTimezone

* @return

*/

protected boolean isSummer(Date sourceDate, TimeZone targetTimezone){

Calendar calendar = Calendar.getInstance();

long sourceTime = sourceDate.getTime();

calendar.setTimeInMillis(sourceTime);

// calendar.setTimeZone(sourceTimezone);

// int sourceZoneOffset = calendar.get(Calendar.ZONE_OFFSET);

// int sourceDaylightOffset = calendar.get(Calendar.DST_OFFSET);

calendar.setTimeZone(targetTimezone);

int targetZoneOffset = calendar.get(Calendar.ZONE_OFFSET);

int targetDaylightOffset = calendar.get(Calendar.DST_OFFSET);

return targetDaylightOffset != 0;

}

}

public class TimezoneNewYorkTypeHandler extends NullableTimezoneTypeHandler {

private static final int DB_ZONE_NEW_YORK = -5;

private static final TimeZone newYorkTimeZone = TimeZone.getTimeZone("America/New_York");

public TimezoneNewYorkTypeHandler() {

super(DB_ZONE_NEW_YORK);

}

@Override

int dayLight(Date sourceDateJvm) {

return isSummer(sourceDateJvm, newYorkTimeZone) ? 1 : 0;

}

}

2020-03-08 01:59:59 按标准13转 2020-03-08 14:59:59，isSummer false，按13第二次转 2020-03-08 14:59:59

2020-03-08 02:00:01 不应出现 2020-03-08 15:00:01，isSummer true， 按12第二次转 2020-03-08 14:00:01，实际相当于纽约2020-03-08 01:00:01，一个冬令时时间被用当前夏令时错误的处理了

2020-03-08 03:00:01 按标准13转 2020-03-08 16:00:01，isSummer true， 按12第二次转 2020-03-08 15:00:01

2020-11-01 00:59:59 按标准13转 2020-11-01 13:59:59，isSummer true， 按12第二次转 2020-11-01 12:59:59

夏令的01：00：00-01：59：59，无法表示

2020-11-01 01:00:01 按标准13转 2020-11-01 14:00:01，isSummer false，按13第二次转 2020-11-01 14:00:01

(2020.10.19)

6)对于server本身又自带夏令时冬令时(手动将server由北京调整为纽约)的情况，由于代码出错，5)的结果出现问题(2020.10.19)

此前代码：

public class TimezoneManager {

private static int getJVMTimezone() {

TimeZone timeZoneCurrent = TimeZone.getDefault();

int offset = timeZoneCurrent.getRawOffset();

int hour = offset / 1000 / 60 / 60;

Date date = new Date();

int hourMod = -date.getTimezoneOffset() / 60;

if (hourMod != hour) {

hour = hourMod;

}

return hour;

}

private static Date dealTimeZone(Date date, int offset) {

Calendar cal = Calendar.getInstance();

cal.setTime(date);

cal.add(Calendar.HOUR, offset);

return cal.getTime();

}

public static Date timezoneJvmToDbWhenInsertAndUpdate(Date date, int dbZone) {

int offset = dbZone - getJVMTimezone();

return dealTimeZone(date, offset);

}

public static Date timezoneDbToJvmWhenQuery(Date date, int dbZone) {

int offset = dbZone - getJVMTimezone();

return dealTimeZone(date, -offset);

}

可以看到getJvmTimezone由于是纽约夏，返回-4，db由于走了输入db时间，该时间是冬令时，算出来是-5，导致一个小时误差

而插入comments没问题，是因为comments测试时使用当前时间new Date，server北京当前+8，db对应该new Date夏令时-4；server纽约当前夏令时-4，db对应该new Date夏令时-4

【第二个错误】错误的原因为，取服务器时区所有操作均使用new Date当前时间，而没有使用db实际查出来的字段时间，而又因为北京本身没有daylight隐藏了问题；当时怎么没有在纽约的dev试一下？或者只是简单看了最近夏季的数据

如果server纽约情况插入comments使用一个冬令时时间也会出问题。插入24-JAN-20 06.00.04.625000000，server -4，db -5，进库则出现1小时误差，逻辑上代表的时间就错了；当插入当前的10.20，server -4，db -4，进库无问题

例子：服务器调整为纽约，db时间24-JAN-20 06.00.04.625000000 AM，代表纽约冬令时，服务器当前为纽约夏令时，导致我们程序显示24-Jan-2020, 07:00 AM EST

之所以之前没暴露出来，是因为服务器使用了北京时区，服务器始终在+8，把db对应于db某个冬令时时间的时区算准后，就可以了，而服务器如果本身自带夏令时冬令时则不行

代码调整为：

public class TimezoneManager {

【重点】由于冬令时夏令时，jvm时区与db时区与具体日期挂钩，增加一个输入日期字段，时间戳使代码可读性更强

private static int getJVMTimezone(long timestamp) {

int hour = getJVMTimezoneWithoutDaylight();

return getTimezone(hour, timestamp, TimeZone.getDefault());

}

private static int getTimezone(int timezoneWithoutDaylight, long timestamp, TimeZone dbTimezone) {

int hour = timezoneWithoutDaylight;

if(isSummer(timestamp, dbTimezone))

hour ++;

return hour;

}

private static int getJVMTimezoneWithoutDaylight() {

TimeZone timeZoneCurrent = TimeZone.getDefault();

int offset = timeZoneCurrent.getRawOffset();

int hour = offset / 1000 / 60 / 60;

return hour;

}

public static boolean isSummer(long timestamp, TimeZone targetTimezone){

Calendar calendar = Calendar.getInstance();

calendar.setTimeInMillis(timestamp);

calendar.setTimeZone(targetTimezone);

int targetDaylightOffset = calendar.get(Calendar.DST_OFFSET);

return targetDaylightOffset != 0;

}

public static Date timezoneJvmToDbWhenInsertAndUpdate(Date date, int dbZoneWithoutDaylight, TimeZone dbTimezone) {

int offset = getTimezone(dbZoneWithoutDaylight, date.getTime(), dbTimezone) - getJVMTimezone(date.getTime());

return dealTimeZone(date, offset);

}

public static Date timezoneDbToJvmWhenQuery(Date date, int dbZone, TimeZone dbTimezone) {

int offsetWithoutDaylight = dbZone - getJVMTimezoneWithoutDaylight();

Date dateWithoutDaylight = dealTimeZone(date, -offsetWithoutDaylight);

int offsetDaylight = getTimezone(dbZone, dateWithoutDaylight.getTime(), dbTimezone) - getJVMTimezone(dateWithoutDaylight.getTime());

return dealTimeZone(date, -offsetDaylight);

}

private static Date dealTimeZone(Date date, int offset) {

Calendar cal = Calendar.getInstance();

cal.setTime(date);

cal.add(Calendar.HOUR, offset);

return cal.getTime();

}

public class TimezoneNewYorkTypeHandler extends NullableTimezoneTypeHandler {

private static final int DB_ZONE_NEW_YORK = -5;

private static final TimeZone newYorkTimeZone = TimeZone.getTimeZone("America/New_York");

public TimezoneNewYorkTypeHandler() {

super(DB_ZONE_NEW_YORK, newYorkTimeZone);

}

}

abstract public class TimezoneTypeHandler extends BaseTypeHandler {

private static final Logger logger = LoggerFactory.getLogger(TimezoneTypeHandler.class);

protected int dbZoneWithoutDaylight;

protected TimeZone dbTimezone;

public TimezoneTypeHandler(int dbZoneWithoutDaylight, TimeZone dbTimezone) {

this.dbZoneWithoutDaylight = dbZoneWithoutDaylight;

this.dbTimezone = dbTimezone;

}

@Override

public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date dateJvm, JdbcType jdbcType) throws SQLException {

if(dateJvm != null) {

if(!dealWithTimezone()) {

preparedStatement.setTimestamp(i, new java.sql.Timestamp(dateJvm.getTime()));

return;

}

Date dateDb = TimezoneManager.timezoneJvmToDbWhenInsertAndUpdate(dateJvm, this.dbZoneWithoutDaylight, this.dbTimezone);

preparedStatement.setTimestamp(i, new java.sql.Timestamp(dateDb.getTime()));

}

}

private Date getJvmDateByDbDate(java.sql.Timestamp dateDb) {

if(dateDb == null) return null;

if(!dealWithTimezone()) {

return new Date(dateDb.getTime());

}

return TimezoneManager.timezoneDbToJvmWhenQuery(new Date(dateDb.getTime()), this.dbZoneWithoutDaylight, this.dbTimezone);

}

完美，顺便提一下，TimeZone.getDefault，运行期修改操作系统时区，对其不生效，但chrome浏览器立即生效

2020.10.30

7)前端

前端处于伦敦，当前处于伦敦冬令时，时区0，输入2020.8.1 00：00：00，浏览器传过来的时区为0，然后8.1这个日期处于夏令时，应处于-1，此时用0作为该日期的时区错误

浏览器不可调和的问题，除非在前端从浏览器获取系统时区，计算该输入日期应处于哪个时区，直接在前端转换