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文本外其它明显的涉及时区的传输​​​

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_zone UTC

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区​


​> set time_zone = '+8:00'; ##修改当前会话时区​


​> flush privileges; #立即生效​


 


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

1565300024 1565300024

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

10 2020-04-14 14:47:19 2020-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

1586875639 1586875639 错,北京时间 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

13 2020-04-14 06:47:19 2020-04-14 06:47:19 UTC6点,北京14点,ok

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

1586846839 1586846839 

 

6.3 根据1.7.1,该方法可用

 

7 myrom

7.1 查询

7.1.1 db:

1 2019-08-08 21:33:44 2019-08-08 21:33:44

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

1565300024 1565300024

 

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:

18 2020-04-14 06:47:19 2020-04-14 06:47:19 变成了UTC的6:47

1586846839 1586846839

 

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 EDT 2020  可以看到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

​javascript:void(0)​​ 说说Java中的TimeZone夏令时问题

 

 

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

 

12 双数据源时区

mysql 5.7  UTC  1 2019-08-08 21:33:44 2019-08-08 21:33:44  1565300024 1565300024

mysql   8   -4  1 2019-08-08 17:33:44 2019-08-08 17:33:44  1565300024 1565300024

*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  5 2020-04-14 06:47:19 2020-04-14 06:47:19  1586846839 1586846839

mysql 8     -4    6 2020-04-14 02:47:19 2020-04-14 02:47:19  1586846839 1586846839

 

 

 

 

13 附录图

时区_mysql

 



package com.example.demo.testcase.timezone;

import com.example.demo.testcase.ScefLogbackFactory;

import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

/**

* 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



//
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:




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时区

 

时区_mysql_02

 

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 timezonehandler​​mybatis 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

* @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<Date> {
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<Date> {

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作为该日期的时区错误

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