基于PostgreSQL的时区问题解决

目录

 

●实际业务场景中遇到的问题

●时间戳(timestamp)定义

●PostgreSQL时间日期类型

●PostgreSQL时间戳使用的问题

●解决方案一：变更字段类型

●解决方案二：SQL层面

●解决方案三：Java层面

●附.Java timezone时区列表

●附.PostgreSQL时区列表

---

●实际业务场景中遇到的问题

最近在工作中遇到这么一个需求，用户要求存入的时间和取出的时间相同，业务场景是国际不同地区、夏令时、标准时等。注意：此处的相同指的是用户看到的时间要一样，即在有时区差异的A地存入“2018-12-01 12:12:12.123”，在B地也显示“2018-12-01 12:12:12.123”。这样做的业务场景是用户在B地取出数据库中的时间戳字段，想知道当时的A地时间，而不是B地时间，因此要保持显示为A地时间，方便沟通。

 

●时间戳(timestamp)定义

时间戳指的是从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。严格来说，不管你处在地球上的哪个地方，任意时间点的时间戳都是相同的。这点有利于线上和客户端分布式应用统一追踪时间信息。
 

●PostgreSQL时间日期类型

名称	描述	精度	数据库显示 示例
timestamp [ (p) ] [ without time zone ]	日期+时间，不带时区	毫秒	2018-02-01 10:33:20.125
timestamp [ (p) ] with time zone	日期+时间，带时区	毫秒	2018-02-01 10:33:20.125+08
date	日期	天	2018-02-01
time [ (p) ] [ without time zone ]	时间，不带时区	毫秒	10:33:20.125
time [ (p) ] with time zone	时间，带时区	毫秒	10:33:20.125+08

根据业务需求，我们存入的数据，例如记录创建时间是包括日期、时间、时区的ISO8601标准时间，因此，能够选择的时间日期类型是timestamp with time zone。

 

●PostgreSQL时间戳使用的问题

虽然时间戳实际上是一个数值，但PostgreSQL在设计的时候，就屏蔽了其底层的真实值，而是以标准ISO8601的规范展示给用户，并且默认是根据系统所在时区自动显示到当前的时区。当然，可以通过数据库的配置文件更改时区，以更换显示的值。但根据时间戳的定义，其真实的、底层的秒数数值是不变的。
可以做以下实验，插入+8时区的数据如下：

变换时区，查看timestamp_with_zone的值如下：

可以发现，相同的时间戳，在不同的时区下对用户表现的形式也不一样。为了验证确实是同一个时间戳，我们继续查看其秒数：

 

●解决方案一：变更字段类型

最简单方式，例如为了存入一个诸如“2018-02-01T10:33:20.125+08:00”的时间数据，直接将时间日期字段用varchar进行存储。这样，用户在A地存入什么，在B地、C地、……取出还是这个值，不可能变化（相同字符集编码的前提下）。但其排序性能没有timestamp好，因此，如果要对该字段加上索引并有先后次序的话，该解决方案并不适用。

 

●解决方案二：SQL层面

PostgreSQL提供时区的操作，我们可以通过设置本地时区（系统级或数据库级）来更换显示的时间日期，诸如“PostgreSQL时间戳使用的问题”中实验所示。还可以使用“at time zone”关键字在SQL查询语句中直接进行转化，如下所示：

--原时间(带时区)
SELECT timestamp_with_zone  FROM test WHERE id = 1;

--直接写时区偏移(基于格林威治时区，是反的，即提前-滞后+，例如正常东八区，需要写-8:00，因此建议直接用时区缩写)
SELECT timestamp_with_zone at time zone '-8:00' FROM test WHERE id = 1;

--中国沿海时间 +8:00
SELECT timestamp_with_zone at time zone 'CCT' FROM test WHERE id = 1;

--格林威治时间 +0:00
SELECT timestamp_with_zone at time zone 'GMT' FROM test WHERE id = 1;

--(美国)太平洋标准时间PDT -7:00
SELECT timestamp_with_zone at time zone 'PDT' FROM test WHERE id = 1;

--(美国)太平洋夏令时PST -8:00
SELECT timestamp_with_zone at time zone 'PST' FROM test WHERE id = 1;

查询结果如下：

这种方案的优势在于时区转换在SQL层面完成，因为直接查和使用“at time zone”都是将时间戳秒数转化为用户友好型的ISO8601标准时间格式，几乎没有额外的资源消耗。劣势在于JAVA开发后端需要进行dao层重构，添加自定义的查询函数执行原生SQL。值得注意的是，我们可能会需要用额外的一个字段存储A地的时区，这样在B地查询的时候，才能从数据库知道A地时区，也才能拼接出如上的查询SQL。
“附.PostgreSQL支持时区”中提供了PostgreSQL中能使用的时区，当然，也可以直接通过加减数字的方式使用自定义的时区偏移。
 

●解决方案三：Java层面

利用Java提供的timezone，我们可以在后端进行时间的转化，定义如下的工具类：

public class DateTools {
  public static String timestampToStr(java.sql.Timestamp time, String strFormat) {
    DateFormat df = new SimpleDateFormat(strFormat);
    String str = df.format(time);
    return str;
  }

  public static java.util.Date timestampToDate(java.sql.Timestamp time) {
    return time;
  }

  public static String getZonedDateTime(Timestamp timestamp, String zone, String formatStr){
    TimeZone timeZone = TimeZone.getTimeZone(zone);
    Date date = timestampToDate(timestamp);
    DateFormat df = new SimpleDateFormat(formatStr);
    df.setTimeZone(timeZone);
    return df.format(date);
  }
}

进行测试，其中时区字符串见“附.Java timezone时区列表”：

public void fun1() {
    System.out.println("时间戳测试开始————————");

    Timestamp timestampWithZone = timeTestRepository.getTimestampWithZoneById(1L);
    String local = DateTools.timestampToStr(timestampWithZone, "yyyy-MM-dd HH:mm:ss +z");
    String pst = DateTools.getZonedDateTime(timestampWithZone,"PST","yyyy-MM-dd HH:mm:ss +z");
    String hst = DateTools.getZonedDateTime(timestampWithZone,"HST","yyyy-MM-dd HH:mm:ss +z");

    System.out.println("本地时区时间——");
    System.out.println(local);
    System.out.println("pst时区时间——");
    System.out.println(pst);
    System.out.println("hst时区时间——");
    System.out.println(hst);
  }

结果如下：

其转化结果和方案二是一直的，都能实现用户在不同时区查看的时间是当地时间的业务需求。这种方案的优势在于不需要修改dao层代码，例如Spring Data JPA的Repository接口。劣势在于将计算资源移至了Java服务器后端，性能上不如直接SQL层面的高。同样地，我们也需要在数据库用一个字段保存用户存储的时区。

 

●附.Java timezone时区列表

Etc/GMT+12

Etc/GMT+11
MIT
Pacific/Apia
Pacific/Midway
Pacific/Niue
Pacific/Pago_Pago
Pacific/Samoa
US/Samoa
America/Adak
America/Atka

Etc/GMT+10
HST
Pacific/Fakaofo
Pacific/Honolulu
Pacific/Johnston
Pacific/Rarotonga
Pacific/Tahiti
SystemV/HST10
US/Aleutian
US/Hawaii
Pacific/Marquesas
AST
America/Anchorage
America/Juneau
America/Nome
America/Yakutat

Etc/GMT+9
Pacific/Gambier
SystemV/YST9
SystemV/YST9YDT
US/Alaska
America/Dawson
America/Ensenada
America/Los_Angeles
America/Tijuana
America/Vancouver
America/Whitehorse
Canada/Pacific
Canada/Yukon

Etc/GMT+8
Mexico/BajaNorte
PST
PST8PDT
Pacific/Pitcairn
SystemV/PST8
SystemV/PST8PDT
US/Pacific
US/Pacific-New
America/Boise
America/Cambridge_Bay
America/Chihuahua
America/Dawson_Creek
America/Denver
America/Edmonton
America/Hermosillo
America/Inuvik
America/Mazatlan
America/Phoenix
America/Shiprock
America/Yellowknife
Canada/Mountain

Etc/GMT+7
MST
MST7MDT
Mexico/BajaSur
Navajo
PNT
SystemV/MST7
SystemV/MST7MDT
US/Arizona
US/Mountain
America/Belize
America/Cancun
America/Chicago
America/Costa_Rica
America/El_Salvador
America/Guatemala
America/Indiana/Knox
America/Indiana/Tell_City
America/Knox_IN
America/Managua
America/Menominee
America/Merida
America/Mexico_City
America/Monterrey
America/North_Dakota/Center
America/North_Dakota/New_Salem
America/Rainy_River
America/Rankin_Inlet
America/Regina
America/Swift_Current
America/Tegucigalpa
America/Winnipeg
CST
CST6CDT
Canada/Central
Canada/East-Saskatchewan
Canada/Saskatchewan
Chile/EasterIsland

Etc/GMT+6
Mexico/General
Pacific/Easter
Pacific/Galapagos
SystemV/CST6
SystemV/CST6CDT
US/Central
US/Indiana-Starke
America/Atikokan
America/Bogota
America/Cayman
America/Coral_Harbour
America/Detroit
America/Eirunepe
America/Fort_Wayne
America/Grand_Turk
America/Guayaquil
America/Havana
America/Indiana/Indianapolis
America/Indiana/Marengo
America/Indiana/Petersburg
America/Indiana/Vevay
America/Indiana/Vincennes
America/Indiana/Winamac
America/Indianapolis
America/Iqaluit
America/Jamaica
America/Kentucky/Louisville
America/Kentucky/Monticello
America/Lima
America/Louisville
America/Montreal
America/Nassau
America/New_York
America/Nipigon
America/Panama
America/Pangnirtung
America/Port-au-Prince
America/Porto_Acre
America/Resolute
America/Rio_Branco
America/Thunder_Bay
America/Toronto
Brazil/Acre
Canada/Eastern
Cuba
EST
EST5EDT

Etc/GMT+5
IET
Jamaica
SystemV/EST5
SystemV/EST5EDT
US/East-Indiana
US/Eastern
US/Michigan
America/Caracas
America/Anguilla
America/Antigua
America/Aruba
America/Asuncion
America/Barbados
America/Blanc-Sablon
America/Boa_Vista
America/Campo_Grande
America/Cuiaba
America/Curacao
America/Dominica
America/Glace_Bay
America/Goose_Bay
America/Grenada
America/Guadeloupe
America/Guyana
America/Halifax
America/La_Paz
America/Manaus
America/Marigot
America/Martinique
America/Moncton
America/Montserrat
America/Port_of_Spain
America/Porto_Velho
America/Puerto_Rico
America/Santiago
America/Santo_Domingo
America/St_Barthelemy
America/St_Kitts
America/St_Lucia
America/St_Thomas
America/St_Vincent
America/Thule
America/Tortola
America/Virgin
Antarctica/Palmer
Atlantic/Bermuda
Atlantic/Stanley
Brazil/West
Canada/Atlantic
Chile/Continental

Etc/GMT+4
PRT
SystemV/AST4
SystemV/AST4ADT
America/St_Johns
CNT
Canada/Newfoundland
AGT
America/Araguaina
America/Argentina/Buenos_Aires
America/Argentina/Catamarca
America/Argentina/ComodRivadavia
America/Argentina/Cordoba
America/Argentina/Jujuy
America/Argentina/La_Rioja
America/Argentina/Mendoza
America/Argentina/Rio_Gallegos
America/Argentina/San_Juan
America/Argentina/San_Luis
America/Argentina/Tucuman
America/Argentina/Ushuaia
America/Bahia
America/Belem
America/Buenos_Aires
America/Catamarca
America/Cayenne
America/Cordoba
America/Fortaleza
America/Godthab
America/Jujuy
America/Maceio
America/Mendoza
America/Miquelon
America/Montevideo
America/Paramaribo
America/Recife
America/Rosario
America/Sao_Paulo
Antarctica/Rothera
BET
Brazil/East

Etc/GMT+3
America/Noronha
Atlantic/South_Georgia
Brazil/DeNoronha

Etc/GMT+2
America/Scoresbysund
Atlantic/Azores
Atlantic/Cape_Verde

Etc/GMT+1
Africa/Abidjan
Africa/Accra
Africa/Bamako
Africa/Banjul
Africa/Bissau
Africa/Casablanca
Africa/Conakry
Africa/Dakar
Africa/El_Aaiun
Africa/Freetown
Africa/Lome
Africa/Monrovia
Africa/Nouakchott
Africa/Ouagadougou
Africa/Sao_Tome
Africa/Timbuktu
America/Danmarkshavn
Atlantic/Canary
Atlantic/Faeroe
Atlantic/Faroe
Atlantic/Madeira
Atlantic/Reykjavik
Atlantic/St_Helena
Eire

Etc/GMT

Etc/GMT+0

Etc/GMT-0

Etc/GMT0
Etc/Greenwich
Etc/UCT
Etc/UTC
Etc/Universal
Etc/Zulu
Europe/Belfast
Europe/Dublin
Europe/Guernsey
Europe/Isle_of_Man
Europe/Jersey
Europe/Lisbon
Europe/London
GB
GB-Eire
GMT
GMT0
Greenwich
Iceland
Portugal
UCT
UTC
Universal
WET
Zulu
Africa/Algiers
Africa/Bangui
Africa/Brazzaville
Africa/Ceuta
Africa/Douala
Africa/Kinshasa
Africa/Lagos
Africa/Libreville
Africa/Luanda
Africa/Malabo
Africa/Ndjamena
Africa/Niamey
Africa/Porto-Novo
Africa/Tunis
Africa/Windhoek
Arctic/Longyearbyen
Atlantic/Jan_Mayen
CET
ECT

Etc/GMT-1
Europe/Amsterdam
Europe/Andorra
Europe/Belgrade
Europe/Berlin
Europe/Bratislava
Europe/Brussels
Europe/Budapest
Europe/Copenhagen
Europe/Gibraltar
Europe/Ljubljana
Europe/Luxembourg
Europe/Madrid
Europe/Malta
Europe/Monaco
Europe/Oslo
Europe/Paris
Europe/Podgorica
Europe/Prague
Europe/Rome
Europe/San_Marino
Europe/Sarajevo
Europe/Skopje
Europe/Stockholm
Europe/Tirane
Europe/Vaduz
Europe/Vatican
Europe/Vienna
Europe/Warsaw
Europe/Zagreb
Europe/Zurich
MET
Poland
ART
Africa/Blantyre
Africa/Bujumbura
Africa/Cairo
Africa/Gaborone
Africa/Harare
Africa/Johannesburg
Africa/Kigali
Africa/Lubumbashi
Africa/Lusaka
Africa/Maputo
Africa/Maseru
Africa/Mbabane
Africa/Tripoli
Asia/Amman
Asia/Beirut
Asia/Damascus
Asia/Gaza
Asia/Istanbul
Asia/Jerusalem
Asia/Nicosia
Asia/Tel_Aviv
CAT
EET
Egypt

Etc/GMT-2
Europe/Athens
Europe/Bucharest
Europe/Chisinau
Europe/Helsinki
Europe/Istanbul
Europe/Kaliningrad
Europe/Kiev
Europe/Mariehamn
Europe/Minsk
Europe/Nicosia
Europe/Riga
Europe/Simferopol
Europe/Sofia
Europe/Tallinn
Europe/Tiraspol
Europe/Uzhgorod
Europe/Vilnius
Europe/Zaporozhye
Israel
Libya
Turkey
Africa/Addis_Ababa
Africa/Asmara
Africa/Asmera
Africa/Dar_es_Salaam
Africa/Djibouti
Africa/Kampala
Africa/Khartoum
Africa/Mogadishu
Africa/Nairobi
Antarctica/Syowa
Asia/Aden
Asia/Baghdad
Asia/Bahrain
Asia/Kuwait
Asia/Qatar
Asia/Riyadh
EAT

Etc/GMT-3
Europe/Moscow
Europe/Volgograd
Indian/Antananarivo
Indian/Comoro
Indian/Mayotte
W-SU
Asia/Riyadh87
Asia/Riyadh88
Asia/Riyadh89
Mideast/Riyadh87
Mideast/Riyadh88
Mideast/Riyadh89
Asia/Tehran
Iran
Asia/Baku
Asia/Dubai
Asia/Muscat
Asia/Tbilisi
Asia/Yerevan

Etc/GMT-4
Europe/Samara
Indian/Mahe
Indian/Mauritius
Indian/Reunion
NET
Asia/Kabul
Asia/Aqtau
Asia/Aqtobe
Asia/Ashgabat
Asia/Ashkhabad
Asia/Dushanbe
Asia/Karachi
Asia/Oral
Asia/Samarkand
Asia/Tashkent
Asia/Yekaterinburg

Etc/GMT-5
Indian/Kerguelen
Indian/Maldives
PLT
Asia/Calcutta
Asia/Colombo
Asia/Kolkata
IST
Asia/Katmandu
Antarctica/Mawson
Antarctica/Vostok
Asia/Almaty
Asia/Bishkek
Asia/Dacca
Asia/Dhaka
Asia/Novosibirsk
Asia/Omsk
Asia/Qyzylorda
Asia/Thimbu
Asia/Thimphu
BST

Etc/GMT-6
Indian/Chagos
Asia/Rangoon
Indian/Cocos
Antarctica/Davis
Asia/Bangkok
Asia/Ho_Chi_Minh
Asia/Hovd
Asia/Jakarta
Asia/Krasnoyarsk
Asia/Phnom_Penh
Asia/Pontianak
Asia/Saigon
Asia/Vientiane

Etc/GMT-7
Indian/Christmas
VST
Antarctica/Casey
Asia/Brunei
Asia/Choibalsan
Asia/Chongqing
Asia/Chungking
Asia/Harbin
Asia/Hong_Kong
Asia/Irkutsk
Asia/Kashgar
Asia/Kuala_Lumpur
Asia/Kuching
Asia/Macao
Asia/Macau
Asia/Makassar
Asia/Manila
Asia/Shanghai
Asia/Singapore
Asia/Taipei
Asia/Ujung_Pandang
Asia/Ulaanbaatar
Asia/Ulan_Bator
Asia/Urumqi
Australia/Perth
Australia/West
CTT

Etc/GMT-8
Hongkong
PRC
Singapore
Australia/Eucla
Asia/Dili
Asia/Jayapura
Asia/Pyongyang
Asia/Seoul
Asia/Tokyo
Asia/Yakutsk

Etc/GMT-9
JST
Japan
Pacific/Palau
ROK
ACT
Australia/Adelaide
Australia/Broken_Hill
Australia/Darwin
Australia/North
Australia/South
Australia/Yancowinna
AET
Antarctica/DumontDUrville
Asia/Sakhalin
Asia/Vladivostok
Australia/ACT
Australia/Brisbane
Australia/Canberra
Australia/Currie
Australia/Hobart
Australia/Lindeman
Australia/Melbourne
Australia/NSW
Australia/Queensland
Australia/Sydney
Australia/Tasmania
Australia/Victoria

Etc/GMT-10
Pacific/Guam
Pacific/Port_Moresby
Pacific/Saipan
Pacific/Truk
Pacific/Yap
Australia/LHI
Australia/Lord_Howe
Asia/Magadan

Etc/GMT-11
Pacific/Efate
Pacific/Guadalcanal
Pacific/Kosrae
Pacific/Noumea
Pacific/Ponape
SST
Pacific/Norfolk
Antarctica/McMurdo
Antarctica/South_Pole
Asia/Anadyr
Asia/Kamchatka

Etc/GMT-12
Kwajalein
NST
NZ
Pacific/Auckland
Pacific/Fiji
Pacific/Funafuti
Pacific/Kwajalein
Pacific/Majuro
Pacific/Nauru
Pacific/Tarawa
Pacific/Wake
Pacific/Wallis
NZ-CHAT
Pacific/Chatham

Etc/GMT-13
Pacific/Enderbury
Pacific/Tongatapu

Etc/GMT-14
Pacific/Kiritimati
 

●附.PostgreSQL时区列表

Africa/Abidjan	GMT	0:00:00
Africa/Accra	GMT	0:00:00
Africa/Addis_Ababa	EAT	3:00:00
Africa/Algiers	CET	1:00:00
Africa/Asmara	EAT	3:00:00
Africa/Asmera	EAT	3:00:00
Africa/Bamako	GMT	0:00:00
Africa/Bangui	WAT	1:00:00
Africa/Banjul	GMT	0:00:00
Africa/Bissau	GMT	0:00:00
Africa/Blantyre	CAT	2:00:00
Africa/Brazzaville	WAT	1:00:00
Africa/Bujumbura	CAT	2:00:00
Africa/Cairo	EET	2:00:00
Africa/Casablanca	WET	0:00:00
Africa/Ceuta	CET	1:00:00
Africa/Conakry	GMT	0:00:00
Africa/Dakar	GMT	0:00:00
Africa/Dar_es_Salaam	EAT	3:00:00
Africa/Djibouti	EAT	3:00:00
Africa/Douala	WAT	1:00:00
Africa/El_Aaiun	WET	0:00:00
Africa/Freetown	GMT	0:00:00
Africa/Gaborone	CAT	2:00:00
Africa/Harare	CAT	2:00:00
Africa/Johannesburg	SAST	2:00:00
Africa/Juba	EAT	3:00:00
Africa/Kampala	EAT	3:00:00
Africa/Khartoum	EAT	3:00:00
Africa/Kigali	CAT	2:00:00
Africa/Kinshasa	WAT	1:00:00
Africa/Lagos	WAT	1:00:00
Africa/Libreville	WAT	1:00:00
Africa/Lome	GMT	0:00:00
Africa/Luanda	WAT	1:00:00
Africa/Lubumbashi	CAT	2:00:00
Africa/Lusaka	CAT	2:00:00
Africa/Malabo	WAT	1:00:00
Africa/Maputo	CAT	2:00:00
Africa/Maseru	SAST	2:00:00
Africa/Mbabane	SAST	2:00:00
Africa/Mogadishu	EAT	3:00:00
Africa/Monrovia	GMT	0:00:00
Africa/Nairobi	EAT	3:00:00
Africa/Ndjamena	WAT	1:00:00
Africa/Niamey	WAT	1:00:00
Africa/Nouakchott	GMT	0:00:00
Africa/Ouagadougou	GMT	0:00:00
Africa/Porto-Novo	WAT	1:00:00
Africa/Sao_Tome	GMT	0:00:00
Africa/Timbuktu	GMT	0:00:00
Africa/Tripoli	EET	2:00:00
Africa/Tunis	CET	1:00:00
Africa/Windhoek	WAST	2:00:00
America/Adak	HAST	-10:00:00
America/Anchorage	AKST	-09:00:00
America/Anguilla	AST	-04:00:00
America/Antigua	AST	-04:00:00
America/Araguaina	BRT