PostgreSQL时间时区类型 postgresql 时区设置-爱华网

PostgreSQL 时间时区类型有 "timestamp with time zone" 和"timestamp without time zone" ,
"time zone"即为时区，这节简单描述下时区的概念，最后将给出一个全世界时区参照图，供参考。

一了解时区
--1.1创建一张测试表
skytf=>create table test_42(idinteger,
time_withtimestamp with timezone,--带时区
time_without timestamp withouttimezone--不带时区
);
CREATE TABLE

skytf=> insert into test_42 values (1,now(),now());
INSERT 0 1

--1.2 查询表数据
skytf=> select * fromtest_42;
id|time_with|time_without
----+-------------------------------+----------------------------
1 | 2011-06-30 13:28:12.340764+08 | 2011-06-3013:28:12.340764
(1 row)

备注：可以看出，带时区的字段"time_with"的内容显示了时区信息"+08"。

二查询其它时区时间举例
--2.1查询当前时间
skytf=>select now();
now
-------------------------------
2011-06-30 13:17:01.867474+08
PostgreSQL时间时区类型 postgresql 时区设置
(1 row)

--2.2 查询china 时间
skytf=> select now() attime zone 'CCT';
timezone
----------------------------
2011-06-30 13:17:03.493546
(1 row)

--2.3 查询香港时间
skytf=> select now() attime zone 'HKT';
timezone
----------------------------
2011-06-30 13:17:11.174238
(1 row)

--2.4 查询法国时间
skytf=> select now() attime zone 'GFT';
timezone
----------------------------
2011-06-30 02:36:19.137075

备注：这里用到了函数“ at time zone ”, 可以将时间转换成其它时区的时间。

三附： PostgreSQL Time ZoneNames

Names	Offset	Description
`FJST`	-13:00	Fiji Summer Time
`FJT`	-12:00	Fiji Time
`IDLW`	-12:00	International Date Line, West
`BST`	-11:00	Bering Summer Time
`NT`	-11:00	Nome Time
`NUT`	-11:00	Niue Time
`AHST`	-10:00	Alaska-Hawaii Std Time
`CAT`	-10:00	Central Alaska Time
`HST`	-10:00	Hawaii Std Time
`THAT`	-10:00	Tahiti Time
`TKT`	-10:00	Tokelau Time
`MART`	-09:30	Marquesas Time
`AKST`	-09:00	Alaska Standard Time
`GAMT`	-09:00	Gambier Time
`HDT`	-09:00	Hawaii/Alaska Daylight Time
`YST`	-09:00	Yukon Standard Time
`AKDT`	-08:00	Alaska Daylight Time
`PST`	-08:00	Pacific Standard Time
`YDT`	-08:00	Yukon Daylight Time
`MST`	-07:00	Mountain Standard Time
`PDT`	-07:00	Pacific Daylight Time
`CST`	-06:00	Central Standard Time
`EAST`	-06:00	Easter Island Time
`GALT`	-06:00	Galapagos Time
`MDT`	-06:00	Mountain Daylight Time
`ZP6`	-06:00	UTC +6 hours
`ACT`	-05:00	Atlantic/Porto Acre Time
`CDT`	-05:00	Central Daylight Time
`COT`	-05:00	Columbia Time
`EASST`	-05:00	Easter Island Summer Time
`ECT`	-05:00	Ecuador Time
`EST`	-05:00	Eastern Standard Time
`PET`	-05:00	Peru Time
`ZP5`	-05:00	UTC +5 hours
`ACST`	-04:00	Atlantic/Porto Acre Summer Time
`AMT`	-04:00	Amazon Time (Porto Velho)
`AST`	-04:00	Atlantic Std Time (Canada)
`BOT`	-04:00	Bolivia Time
`CLT`	-04:00	Chile Time
`ECT`	-04:00	Eastern Caribbean Time
`EDT`	-04:00	Eastern Daylight Time
`GYT`	-04:00	Guyana Time
`PYT`	-04:00	Paraguay Time
`VET`	-04:00	Venezuela Time
`ZP4`	-04:00	UTC +4 hours
`NFT`	-03:30	Newfoundland Standard Time
`NST`	-03:30	Newfoundland Standard Time
`ADT`	-03:00	Atlantic Daylight Time
`AMST`	-03:00	Amazon Summer Time (Porto Velho)
`ART`	-03:00	Argentina Time
`AWT`	-03:00	Brazil Time
`BRT`	-03:00	Brasilia Time
`BST`	-03:00	Brazil Standard Time
`CLST`	-03:00	Chile Summer Time
`FKST`	-03:00	Falkland Islands Summer Time
`GFT`	-03:00	French Guiana Time
`PYST`	-03:00	Paraguay Summer Time
`UYT`	-03:00	Uruguay Time
`WGT`	-03:00	West Greenland Time
`NDT`	-02:30	Newfoundland Daylight Time
`BRST`	-02:00	Brasilia Summer Time
`FKT`	-02:00	Falkland Islands Time
`FNT`	-02:00	Fernando de Noronha Time
`PMDT`	-02:00	Pierre & Miquelon Daylight Time
`UYST`	-02:00	Uruguay Summer Time
`WGST`	-02:00	West Greenland Summer Time
`AZOT`	-01:00	Azores Time
`EGT`	-01:00	East Greenland Time
`FNST`	-01:00	Fernando de Noronha Summer Time
`SET`	-01:00	Seychelles Time
`WAT`	-01:00	West Africa Time
`AZOST`	+00:00	Azores Summer Time
`EGST`	+00:00	East Greenland Summer Time
`GMT`	+00:00	Greenwich Mean Time
`UTC`	+00:00	Universal Coordinated Time
`UT`	+00:00	Universal Time
`WET`	+00:00	Western Europe
`ZULU`	+00:00	Universal Time
`Z`	+00:00	ISO-8601 Universal Time
`BST`	+01:00	British Summer Time
`CET`	+01:00	Central European Time
`DNT`	+01:00	Dansk Normal Time
`FST`	+01:00	French Summer Time
`MET`	+01:00	Middle Europe Time
`MEWT`	+01:00	Middle Europe Winter Time
`MEZ`	+01:00	Middle Europe Zone
`NOR`	+01:00	Norway Standard Time
`SWT`	+01:00	Swedish Winter Time
`WEST`	+01:00	Western Europe Summer Time
`WEtdST`	+01:00	Western Europe Daylight Savings Time
`BDST`	+02:00	British Double Summer Time
`CEST`	+02:00	Central European Dayl.Time
`CETDST`	+02:00	Central European Dayl.Time
`EET`	+02:00	Eastern Europe, USSR Zone 1
`FWT`	+02:00	French Winter Time
`IST`	+02:00	Israel Time
`MEST`	+02:00	Middle Europe Summer Time
`MEtdST`	+02:00	Middle Europe Daylight Time
`SST`	+02:00	Swedish Summer Time
`BT`	+03:00	Baghdad Time
`EAT`	+03:00	East Africa Time
`EAT`	+03:00	Indian Antananarivo Time
`EEST`	+03:00	Eastern Europe Summer Time
`EEtdST`	+03:00	Eastern Europe Daylight Time
`HMT`	+03:00	Hellas Mediterranean Time
`MSK`	+03:00	Moscow Time
`IRT`	+03:30	Iran Time
`IT`	+03:30	Iran Time
`AMT`	+04:00	Armenia Time (Yerevan)
`AZT`	+04:00	Azerbaijan Time
`EAST`	+04:00	Indian Antananarivo Savings Time
`GET`	+04:00	Georgia Time
`MSD`	+04:00	Moscow Summer Time
`MUT`	+04:00	Mauritius Island Time
`RET`	+04:00	Reunion Island Time
`SCT`	+04:00	Mahe Island Time
`AFT`	+04:30	Kabul Time
`AMST`	+05:00	Armenia Summer Time (Yerevan)
`AZST`	+05:00	Azerbaijan Summer Time
`GEST`	+05:00	Georgia Summer Time
`IOT`	+05:00	Indian Chagos Time
`KGT`	+05:00	Kyrgyzstan Time
`MVT`	+05:00	Maldives Island Time
`PKT`	+05:00	Pakistan Time
`TFT`	+05:00	Kerguelen Time
`TJT`	+05:00	Tajikistan Time
`TMT`	+05:00	Turkmenistan Time
`UZT`	+05:00	Uzbekistan Time
`YEKT`	+05:00	Yekaterinburg Time
`NPT`	+05:45	Nepal Standard Time
`ALMT`	+06:00	Almaty Time
`BDT`	+06:00	Dacca Time
`BTT`	+06:00	Bhutan Time
`DUSST`	+06:00	Dushanbe Summer Time
`KGST`	+06:00	Kyrgyzstan Summer Time
`LKT`	+06:00	Lanka Time
`MAWT`	+06:00	Mawson, Antarctica
`NOVT`	+06:00	Novosibirsk Standard Time
`OMST`	+06:00	Omsk Time
`UZST`	+06:00	Uzbekistan Summer Time
`YEKST`	+06:00	Yekaterinburg Summer Time
`CCT`	+06:30	Indian Cocos (Island) Time
`MMT`	+06:30	Myanmar Time
`ALMST`	+07:00	Almaty Savings Time
`CVT`	+07:00	Christmas Island Time (Indian Ocean)
`CXT`	+07:00	Christmas Island Time (Indian Ocean)
`DAVT`	+07:00	Davis Time (Antarctica)
`ICT`	+07:00	Indochina Time
`JAVT`	+07:00	Java Time
`KRAST`	+07:00	Krasnoyarsk Summer Time
`NOVST`	+07:00	Novosibirsk Summer Time
`OMSST`	+07:00	Omsk Summer Time
`WAST`	+07:00	West Australian Std Time
`JT`	+07:30	Java Time
`AWST`	+08:00	Western Australia
`BNT`	+08:00	Brunei Darussalam Time
`BORT`	+08:00	Borneo Time (Indonesia)
`CCT`	+08:00	China Coast Time
`HKT`	+08:00	Hong Kong Time
`IRKT`	+08:00	Irkutsk Time
`KRAT`	+08:00	Krasnoyarsk Standard Time
`MYT`	+08:00	Malaysia Time
`PHT`	+08:00	Phillipine Time
`ULAT`	+08:00	Ulan Bator Time
`WADT`	+08:00	West Australian DST
`WST`	+08:00	West Australian StandardTime
`MT`	+08:30	Moluccas Time
`AWSST`	+09:00	Western Australia Time
`IRKST`	+09:00	Irkutsk Summer Time
`JAYT`	+09:00	Jayapura Time (Indonesia)
`JST`	+09:00	Japan Std Time, USSR Zone 8
`KST`	+09:00	Korea Standard Time
`PWT`	+09:00	Palau Time
`ULAST`	+09:00	Ulan Bator Summer Time
`WDT`	+09:00	West Australian DST
`YAKT`	+09:00	Yakutsk Time
`ACST`	+09:30	Central Australia
`CAST`	+09:30	Central Australian ST
`SAST`	+09:30	South Australian Std Time
`SAT`	+09:30	South Australian Std Time
`AEST`	+10:00	Australia Eastern Std Time
`DDUT`	+10:00	Dumont-d'Urville Time (Antarctica)
`EAST`	+10:00	East Australian Std Time
`EST`	+10:00	Australia Eastern Std Time
`GST`	+10:00	Guam Std Time, USSR Zone 9
`KDT`	+10:00	Korea Daylight Time
`LIGT`	+10:00	From Melbourne, Australia
`MPT`	+10:00	North Mariana Islands Time
`PGT`	+10:00	Papua New Guinea Time
`trUK`	+10:00	Truk Time
`VLAT`	+10:00	Vladivostok Time
`YAKST`	+10:00	Yakutsk Summer Time
`YAPT`	+10:00	Yap Time (Micronesia)
`ACSST`	+10:30	Central Australia Time
`CADT`	+10:30	Central Australian DST
`CST`	+10:30	Australia Central Std Time
`LHST`	+10:30	Lord Howe Standard Time, Australia
`SADT`	+10:30	South Australian Daylight Time
`AESST`	+11:00	Eastern Australia
`LHDT`	+11:00	Lord Howe Daylight Time, Australia
`MAGT`	+11:00	Magadan Time
`NCT`	+11:00	New Caledonia Time
`PONT`	+11:00	Ponape Time (Micronesia)
`VLAST`	+11:00	Vladivostok Summer Time
`VUT`	+11:00	Vanuata Time
`ANAT`	+12:00	Anadyr Time (Russia)
`CKT`	+12:00	Cook Islands Time
`GILT`	+12:00	Gilbert Islands Time
`IDLE`	+12:00	International Date Line, East
`KOST`	+12:00	Kosrae Time
`MAGST`	+12:00	Magadan Summer Time
`MHT`	+12:00	Kwajalein Time
`NZST`	+12:00	New Zealand Standard Time
`NZT`	+12:00	New Zealand Time
`PETT`	+12:00	Petropavlovsk-Kamchatski Time
`TVT`	+12:00	Tuvalu Time
`WAKT`	+12:00	Wake Time
`WFT`	+12:00	Wallis and Futuna Time
`CHAST`	+12:45	Chatham Island Time
`ANAST`	+13:00	Anadyr Summer Time (Russia)
`NZDT`	+13:00	New Zealand Daylight Time
`PETST`	+13:00	Petropavlovsk-Kamchatski Summer Time
`PHOT`	+13:00	Phoenix Islands (Kiribati) Time
`TOT`	+13:00	Tonga Time
`CHADT`	+13:45	Chatham Island Daylight Time
`LINT`	+14:00	Line Islands Time (Kiribati)

I mentioned earlier in this section thatanINTERVALvaluerepresents a time span. I also mentioned

postgresql 时间类型

爱华网本文地址 » http://www.413yy.cn/a/25101012/117527.html

PostgreSQL时间时区类型 postgresql 时区设置

更多阅读

如何设置手机的首选网络类型？电信设置首选网络类型

让网络时间同步电脑时间方法电脑如何同步网络时间

怎样让电脑定时开机精电脑怎样定时开关机

电脑装双系统后如何快速切换和增加选择系统时间双系统如何快速切换

深圳蛇口港→珠海时刻表深圳蛇口港时刻表

声明:《PostgreSQL时间时区类型 postgresql 时区设置》为网友梦过后分享！如侵犯到您的合法权益请联系我们删除

更多阅读

如何设置手机的首选网络类型？ 电信设置首选网络类型

让网络时间同步电脑时间方法 电脑如何同步网络时间

怎样让电脑定时开机 精 电脑怎样定时开关机