时区
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 mysql 协议抓包
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
https://blog.csdn.net/kongxx/article/details/38356607 说说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 附录图
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<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作为该日期的时区错误
浏览器不可调和的问题，除非在前端从浏览器获取系统时区，计算该输入日期应处于哪个时区，直接在前端转换
