【Postgres】日期欄位預設值

Postgres time zone 值:
http://www.postgresql.org/docs/7.1/static/datetime-appendix.html

Time Zone Offset from UTC Description
NZDT +13:00 New Zealand Daylight Time
IDLE +12:00 International Date Line, East
NZST +12:00 New Zealand Std Time
NZT +12:00 New Zealand Time
AESST +11:00 Australia Eastern Summer Std Time
ACSST +10:30 Central Australia Summer Std Time
CADT +10:30 Central Australia Daylight Savings Time
SADT +10:30 South Australian Daylight Time
AEST +10:00 Australia Eastern Std Time
EAST +10:00 East Australian Std Time
GST +10:00 Guam Std Time, USSR Zone 9
LIGT +10:00 Melbourne, Australia
ACST +09:30 Central Australia Std Time
SAST +09:30 South Australia Std Time
CAST +09:30 Central Australia Std Time
AWSST +9:00 Australia Western Summer Std Time
JST +9:00 Japan Std Time,USSR Zone 8
KST +9:00 Korea Standard Time
WDT +9:00 West Australian Daylight Time
MT +8:30 Moluccas Time
AWST +8:00 Australia Western Std Time
CCT +8:00 China Coastal Time
WADT +8:00 West Australian Daylight Time
WST +8:00 West Australian Std Time
JT +7:30 Java Time
WAST +7:00 West Australian Std Time
IT +3:30 Iran Time
BT +3:00 Baghdad Time
EETDST +3:00 Eastern Europe Daylight Savings Time
CETDST +2:00 Central European Daylight Savings Time
EET +2:00 Eastern Europe, USSR Zone 1
FWT +2:00 French Winter Time
IST +2:00 Israel Std Time
MEST +2:00 Middle Europe Summer Time
METDST +2:00 Middle Europe Daylight Time
SST +2:00 Swedish Summer Time
BST +1:00 British Summer Time
CET +1:00 Central European Time
DNT +1:00 Dansk Normal Tid
FST +1:00 French Summer Time
MET +1:00 Middle Europe Time
MEWT +1:00 Middle Europe Winter Time
MEZ +1:00 Middle Europe Zone
NOR +1:00 Norway Standard Time
SET +1:00 Seychelles Time
SWT +1:00 Swedish Winter Time
WETDST +1:00 Western Europe Daylight Savings Time
GMT 0:00 Greenwich Mean Time
WET 0:00 Western Europe
WAT -1:00 West Africa Time
NDT -2:30 Newfoundland Daylight Time
ADT -03:00 Atlantic Daylight Time
NFT -3:30 Newfoundland Standard Time
NST -3:30 Newfoundland Standard Time
AST -4:00 Atlantic Std Time (Canada)
EDT -4:00 Eastern Daylight Time
CDT -5:00 Central Daylight Time
EST -5:00 Eastern Standard Time
CST -6:00 Central Std Time
MDT -6:00 Mountain Daylight Time
MST -7:00 Mountain Standard Time
PDT -7:00 Pacific Daylight Time
PST -8:00 Pacific Std Time
YDT -8:00 Yukon Daylight Time
HDT -9:00 Hawaii/Alaska Daylight Time
YST -9:00 Yukon Standard Time
AHST -10:00 Alaska-Hawaii Std Time
CAT -10:00 Central Alaska Time
NT -11:00 Nome Time
IDLW -12:00 International Date Line, West


以台灣為例, 台灣時間為 +8:00, 故 time zone 值為 CCT, (其實也等於 AWST, WADT, WST)

要取得目前台灣時間, 語法為:

SELECT NOW() AT TIME ZONE 'CCT'
若要在 DATE 欄位給予新增記錄時的欄位預設值

在 pgAdmin 建立/修改 table 時, 可在預設值輸入

timezone('CCT'::text, now())
建立的 SQL 語法則為 ----
CREATE TABLE test
(
  id serial NOT NULL,
  newsdate date DEFAULT timezone('CCT'::text, now()),  CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE test OWNER TO 資料庫擁有者;