注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL Time Zone Names  

2011-06-30 13:42:58|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

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


一 了解时区
--1.1 创建一张测试表
skytf=> create table test_42 (id             integer,
                              time_with      timestamp with time zone,       --带时区
                              time_without   timestamp without time zone     --不带时区
                              );
CREATE TABLE

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

--1.2 查询表数据
skytf=> select * from test_42;
 id |           time_with           |        time_without       
----+-------------------------------+----------------------------
  1 | 2011-06-30 13:28:12.340764+08 | 2011-06-30 13:28:12.340764
(1 row)
     
   备注:可以看出,带时区的字段"time_with" 的内容显示了时区信息 "+08"。  
     
   
二 查询其它时区时间举例
--2.1 查询当前时间
skytf=> select now();
              now             
-------------------------------
 2011-06-30 13:17:01.867474+08
(1 row)

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

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

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

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


三 附: PostgreSQL Time Zone Names

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 Standard   Time

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 that an INTERVAL value represents a time span. I also mentioned


  评论这张
 
阅读(31409)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016