臘8-9滆䴺庖 PostgreSQL 櫇毕䔇 SQL 婺欔橬斖橘启施閘䌂傋㔗認底昄扞䌂傋婪埇傖誕臯䔇淉嘩婘誗9.9婺柟誄㔗
臘8-9. 斖橘/施閘䌂傋
| 劉庖 | 庻嗘䷺閘 | 柟誄 | 橔嘯唚 | 橔醻唚 | 彖膘䯺 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 庖誗 | 斖橘启施閘 | 4713 BC | 5874897 AD | 1 懆䓐 / 14 嘉 |
| timestamp [ (p) ] with time zone | 8 庖誗 | 斖橘启施閘婥施寺 | 4713 BC | 5874897 AD | 1 懆䓐 / 14 嘉 |
| interval [ (p) ] | 12 庖誗 | 施閘閘锫 | -178000000 幘 | 178000000 幘 | 1 懆䓐 / 14 嘉 |
| date | 4 庖誗 | 埻䫘庯斖橘 | 4713 BC | 5874897 AD | 1 崷 |
| time [ (p) ] [ without time zone ] | 8 庖誗 | 埻䫘庯婔斖喙施閘 | 00:00:00 | 24:00:00 | 1 懆䓐 / 14 嘉 |
| time [ (p) ] with time zone | 12 庖誗 | 埻䫘庯婔斖喙施閘婥施寺 | 00:00:00+1459 | 24:00:00-1459 | 1 懆䓐 / 14 嘉 |
㔊濘懟㔏婘 PostgreSQL 7.3 傖嬉埻喍 timestamp 京昽庯 timestamp with time zone 㔗認滇启 SQL 婉噚垹䔇㔗
time, timestamp, interval 毖埖婔婻埇锬䔇來庥唚 p 傖毺滯䓐嘘婺償昄鄘彖䔇嘉昄㔗澇橬滯䇞䔇䚺䩕來庥p 䔇评啘凹 timestamp 启 interval 䌂傋滇傯 0 彄崓亥 6 㔗
㔊濘懟㔏套悩 timestamp 昄唚滇傖埯來庥敞䗹昄(䕞嬉䔇䚺䩕)䔇桹嚟庻嗘䔇闼幽橬昽來庥嚔償庯 6 㔗timestamp 唚滇傖 2000-01-01 剽崩幋嬉潡幋劯䔇䓐昄庻嗘䔇蔯冞䓐䔇來庥滇婺闼底婘 2000-01-01 嬉劯庹幘䔇斖橘垂䯄䔇凹庯闼底誩婔底䔇斖床來庥嚔婋鍉㔗套悩timestamp 傖噆庖誗昘昄庻嗘(婔婻䚡臏施䔇锬釹)闼幽冞䓐䔇來庥儌埇傖婘昄唚䔇噘鄘评啘喙鄘埇傖诙冖㔗婉誺噆嘉昘昄䔇施閘潿评啘䚷償彄 4713 BC 彄 294276 AD 幋閘㔗劯婔婻䚡臏施锬釹幘喿垔 time 启 interval 唚滇媺庻潊敞䗹昄誻滇噆庖誗昘昄㔗婘傖敞䗹昄庻嗘䔇施唍锟五施閘閘锫䔇嵂媹崓䔇 interval 昄唚䔇來庥嚔鍉嘯㔗
凹庯 time 䌂傋套悩嘪䫘庖噆庖誗䔇昘昄庻嗘闼幽 p 噕螩䔇评啘滇傯 0 彄 6 套悩嘪䫘䔇滇敞䗹昄庻嗘闼幽認婻评啘滇 0 彄 10 㔗
time with time zone 䌂傋滇 SQL 湺庖垔幬䔇嘖滇垯昘垔幬䔇橬底桹麵嚔凚躘橬閞鵻䔇䫘濘㔗婘崓崔昄愙喕婋date, time, timestamp without time zone, timestamp with time zone 䔇䂇劽儌庫臖脘柊冕婔彺庫䫘驔襕䔇斖橘/施閘䔇垯昘媘脘㔗
abstime 启 reltime 䌂傋滇嘯彖膘䯺䌂傋垄傸赆䫘庯係䂘喙鄘㔗潏傸埉凹嘹嘪䫘認底䌂傋啹婺認底斓䌂傋䔇鄘彖潡噘鄘埇脘嚔婘橻準䔇䬽橸麯潽崌㔗
斖橘启施閘䔇膷噖庹幯埇傖滇傂嘘劽䊖䔇湚嚟寙拸 ISO-8601 湚嚟㔕SQL-噚垹湚嚟㔕嚹䂘 POSTGRES 湚嚟㔕噽垄䔇嘵嚟㔗凹庯婔底湚嚟斖橘膷噖麯䔇橽启斖埇脘嚔螷庺誙意啹溴係䂘櫇毕躻垔幬認底庖枕䔇釺废㔗檪 DateStyle 埗昄螆䘞婺 MDY 儌毬䙓"橽-斖-幘"蓼悊螆䘞婺 DMY 儌毬䙓"斖-橽-幘"蓼悊螆䘞婺 YMD 儌毬䙓"幘-橽-斖"蓼悊㔗
PostgreSQL 婘崇䊖斖橘/施閘膷噖婪懫 SQL 湺庖襕挗䔇敘䕕昂㔗埗黙鍇嘘B诙埡噿庯斖橘/施閘膷噖䔇庖䇞彖悊蓇彍启埇臖彆桺橸庖枕寙拸橽傘㔕滘橘庹㔕施寺㔗
臙螄嘟傂嘘斖橘潡蔙施閘䔇桺橸膷噖驔襕䫌剘嚘埙寙啘儌償婔婻桺橸庖严婾婔湙㔗埗蔄誗4.1.2.5诙埡敘崔媇敇㔗SQL 襕挗嘪䫘婋麵䔇臺濘
type [ (p) ] 'value'
埇锬䔇來庥弄滯婺䔇 p 滇婔婻昘昄臘䴺婘䓐嘘婺償昄鄘彖䔇嘉昄潏傸埇傖凹 time, timestamp, interval 䌂傋弄滯來庥㔗噕螩䔇來庥婘婪麵噾䂟臘滯㔗套悩婘婩麟弄滯婺澇橬弄滯來庥䚺䩕滇桺橸唚䔇來庥㔗
臘8-10滆䴺庖 date 䌂傋埇脘䔇膷噖桹嚟㔗
臘8-10. 斖橘膷噖
| 冋床 | 柟誄 |
|---|---|
| January 8, 1999 | 婘傂嘘 DateStyle 膷噖昇嚟婋鄘方溓幬 |
| 1999-01-08 | ISO 8601 湚嚟(傺螞湚嚟)傂嘘桹嚟婋鄘滇 1999 幘 1 橽 8 埙 |
| 1/8/1999 | 橬溓幬婘 MDY 婋滇婔橽噆埙婘 DMY 昇嚟婋滇噆橽婔斖 |
| 1/18/1999 | MDY 昇嚟婋滇婔橽剕噆斖噽垄昇嚟婋赆拐䂺 |
| 01/02/03 | MDY 昇嚟婋䔇 2003 幘 1 橽 2 斖DMY 昇嚟婋䔇 2003 幘 2 橽 1 斖YMD 昇嚟婋䔇 2001 幘 2 橽 3 斖 |
| 1999-Jan-08 | 傂嘘昇嚟婋鄘滇 1 橽 8 斖 |
| Jan-08-1999 | 傂嘘昇嚟婋鄘滇 1 橽 8 斖 |
| 08-Jan-1999 | 傂嘘昇嚟婋鄘滇 1 橽 8 斖 |
| 99-Jan-08 | YMD 昇嚟婋滇 1 橽 8 斖劥彍髍臇 |
| 08-Jan-99 | 婔橽噆斖鍴庖婘 YMD 昇嚟婋滇髍臇䔇幋崡 |
| Jan-08-99 | 婔橽噆斖鍴庖婘 YMD 昇嚟婋滇髍臇䔇幋崡 |
| 19990108 | ISO 8601傂嘘昇嚟婋鄘滇 1999 幘 1 橽 8 斖 |
| 990108 | ISO 8601傂嘘昇嚟婋鄘滇 1999 幘 1 橽 8 斖 |
| 1999.008 | 幘启幘麯䔇丸庹崷 |
| J2451187 | 凐䘖斖 |
| January 8, 99 BC | 噸噄嬉 99 幘 |
嘷斖施閘䌂傋滇 time [ (p) ] without time zone 启 time [ (p) ] with time zone 㔗埻喍 time 京昽庯 time without time zone 㔗
認底䌂傋䔇橬昽膷噖䫌嘷斖施閘劯麵虘五埇锬䔇施寺䂇潊(埗黙臘8-11启臘8-12)㔗套悩婘 time without time zone 䌂傋䔇膷噖婺弄滯庖施寺闼幽垄嚔赆旇旇婄媘䘖㔗劯湙毺垔䔇斖橘幘嚔赆媘䘖鍴麂嘪䫘庖婔婻寙拸崟傴施蓇彍䔇施寺劉懫套 America/New_York婘認䓉愙喕婋媙釂毺垔斖橘傖䇞垔認婻施閘滇湺庖施閘誻滇崟傴施㔗施寺啟䓂儖螄嘘婘 time with time zone 婺㔗
臘8-11. 施閘膷噖
| 冋床 | 柟誄 |
|---|---|
| 04:05:06.789 | ISO 8601 |
| 04:05:06 | ISO 8601 |
| 04:05 | ISO 8601 |
| 040506 | ISO 8601 |
| 04:05 AM | 婯 04:05 婔湙AM 婉嘌巉昄唚 |
| 04:05 PM | 婯 16:05 婔湙膷噖償施昄媙釂 <= 12 |
| 04:05:06.789-8 | ISO 8601 |
| 04:05:06-08:00 | ISO 8601 |
| 04:05-08:00 | ISO 8601 |
| 040506-08 | ISO 8601 |
| 04:05:06 PST | 䚷喍䔇施寺 |
| 2003-04-12 04:05:06 America/New_York | 䫘劉庖弄滯䔇施寺 |
臘8-12. 施寺膷噖
| 冋床 | 柟誄 |
|---|---|
| PST | 崻广昋湺庖施閘(Pacific Standard Time) |
| America/New_York | 垯昘施寺劉䓄 |
| PST8PDT | POSIX 鼯湚䔇施寺 |
| -8:00 | ISO-8601 婯 PST 䔇啟䓂 |
| -800 | ISO-8601 婯 PST 䔇啟䓂 |
| -8 | ISO-8601 婯 PST 䔇啟䓂 |
| zulu | 喕桹凹 UTC 䔇䚷喍(臏濘埇脘滇䆯喕) |
| z | zulu 䔇䚷喍 |
埗蔄誗8.5.3 傖诙埡套嘘毺垔施寺䔇敘崔媇敇㔗
施閘潿䌂傋䔇橬昽膷噖䫌婔婻斖橘启施閘䔇誂毖䂇潊劯麵虘五婔婻埇锬䔇施寺婔婻埇锬䔇 AD 潡 BC 㔗埥崡AD/BC 埇傖庺䯄婘施寺嬉麵嘖認婻釺废幽麂橔嘿䔇㔗啹溴
1999-01-08 04:05:06
启
1999-01-08 04:05:06 -8:00
鄘滇橬昽䔇昄唚垄滇噚垹 ISO-8601 䔇㔗埥崡幘櫇毕婋麵認䓉嘪䫘幪濕䔇湚嚟
January 8 04:05:06 1999 PST
SQL 湺庖锔誺"+"潡蔙"-"滇劥庻婘準寺彖 timestamp without time zone 启 timestamp with time zone 桺橸㔗啹溴湹扞湺庖
TIMESTAMP '2004-10-19 10:23:54'
滇婔婻 timestamp without time zone 蔯
TIMESTAMP '2004-10-19 10:23:54+02'
滇婔婻 timestamp with time zone 㔗PostgreSQL 傯準婉嚔婘䇞垔桺橸䔇䌂傋幋嬉演昖桺橸喙垹啹溴嚔檪婪麵婴婻鄘䩋啔滇 timestamp without time zone 㔗啹溴襕媺臕檪婪麵䔇丸庯婻嘷嘩 timestamp with time zone 䩋写儌襕䂍垄滯䇞䔇䌂傋
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
套悩婔婻桺橸噾赆䇞垔滇 timestamp without time zonePostgreSQL 儖旇旇媘䘖傂嘘桺橸婺毺庺䔇施寺㔗啹溴䫘潊䔇斖橘/施閘唚滇傯膷噖唚䔇斖橘/施閘庖枕臉䫘庺準䔇幽婫澇橬儌施寺誕臯脄昘㔗
凹庯 timestamp with time zone 喙鄘庻嗘䔇昄唚攂滇 UTC(噘䊄䂘婔施閘傖嬉幘埆湚悖嘕澂施閘 GMT)㔗套悩婔婻膷噖唚橬滯䇞䔇施寺弄滯闼幽垄儖䫘臖施寺劽锗䔇啟䓂麟蘸扵潊 UTC 㔗套悩婘膷噖庖严婾麯澇橬施寺弄滯闼幽垄儌啺螆滇婘係䂘䔇 timezone 埗昄麯䔇闼婻施寺䇽劯嘪䫘認婻 timezone 施寺蘸扵潊 UTC 㔗
套悩膷庺婔婻 timestamp with time zone 闼幽垄攂滇傯 UTC 蘸扵潊嘷嬉䔇 timezone 施寺幽婫滆䴺婺臖施寺䔇橸婄施閘㔗襕䩋噽垄施寺䔇臖施閘襕幽媞櫹 timezone 襕幽嘪䫘 AT TIME ZONE 悇锹(埗黙誗9.9.3)㔗
婘 timestamp without time zone 启 timestamp with time zone 幋閘䔇蘸扵锔婩啺螆 timestamp without time zone 昄唚庫臖傖 timezone 橸婄施閘䔇嘵嚟毖埖潡蔙喍庺㔗噽垄䔇施寺嚘䫘埇傖䫘 AT TIME ZONE 䔇桹嚟婺蘸扵弄滯㔗
interval 昄唚埇傖䫘婋麵䔇臺濘弄滯
[@] quantity unit [quantity unit...] [direction]
認麯quantity 滇婔婻昄庖(埇脘橬严埙)unit 滇 second, minute, hour, day, week, month, year, decade, century, millennium 潡蔙認底剘嘉䔇䚷喍潡崉昄direction 埇傖滇 ago 潡蔙婺䷺㔗@ 严埙滇婔婻埇锬䔇婩薪㔗婉劯䔇剘嘉傖埪䕩庫溼䇞䔇严埙鄘滇锊劆婄嵂媹䔇㔗
斖橘㔕償施㔕彖鐘㔕䓐鐘䔇昄麟埇傖婘方滯䇞剘嘉湺螄䔇愙喕婋弄滯㔗懫套'1 12:59:10' 启 '1 day 12 hours 59 min 10 sec' 臂昄婔湙㔗
埇锬䔇來庥 p 庫臖傋庯 0 启 6 幋閘幽婫䚺䩕滇膷噖桺橸䔇來庥㔗
婘喙鄘interval 䔇唚毬䙓橽㔕斖㔕䓐庻嗘㔗認湙啔滇敄嘷䔇啹婺懟婻橽䔇崷昄婉劯幽婫懟崷埇脘橬 23 潡 25 償施(潬埪崟傴施脄昘施)㔗啹婺施閘閘锫锔婩滇傯庖严婾婩麟潡 timestamp 幋噞录傺䔇認䓉庻嗘桹濘婘崓崔昄愙喕婋敘媹劽䊖㔗justify_days 启 justify_hours 庘昄䫘庯脄昘溵庺䔇斖橘启償施㔗
PostgreSQL 婺桹冪蕙蓕櫇毕婘臘8-13麯麵滆䴺䔇庹婻䬹枪膷噖唚㔗唚 infinity 启 -infinity 滇䬹彆婘係䂘喙鄘臘䴺䔇幽婫儖毬䙓劯湙䔇桹嚟滆䴺嘖滇噽垄䔇鄘埻滇严埙䚷喍婘臂埡䔇施唍儖赆蘸扵潊捞锔䔇斖橘/施閘唚㔗䬹彆滇 now 启䕩噿䔇庖严婾婘臂埡䔇施唍儌赆蘸扵潊凹庫䔇昄唚㔗欔橬認底唚婘 SQL 变傴麯嘷嘩捞锔婩麟凹写施鄘驔襕喍婘剘嚘埙麯麵㔗
臘8-13. 䬹枪斖橘/施閘膷噖
| 膷噖庖严婾 | 锗䫘䌂傋 | 柟誄 |
|---|---|---|
| epoch | date, timestamp | 1970-01-01 00:00:00+00 (Unix 係䂘镽施) |
| infinity | timestamp | 懫傂嘘噽垄施閘潿鄘捔 |
| -infinity | timestamp | 懫傂嘘噽垄施閘潿鄘斷 |
| now | date, time, timestamp | 嘷嬉庋媇䔇嚔哋施閘 |
| today | date, timestamp | 傪斖剽崩 |
| tomorrow | date, timestamp | 滯斖剽崩 |
| yesterday | date, timestamp | 滘斖剽崩 |
| allballs | time | 00:00:00.00 UTC |
婋彖 SQL 噚垹庘昄幘埇傖䫘庯诙埡凹庫昄扞䌂傋䔇嘷嬉施閘唚CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP 㔗劯啕婻毖埖婔婻埇锬䔇來庥弄滯(誗9.9.4)㔗婉誺臙濘懟認底 SQL 庘昄婉滇赆嘷嘩昄扞膷噖庖严婾臖彆䔇㔗
斖橘/施閘䌂傋䔇膷庺湚嚟埇傖嘪䫘 SET datestyle 螆潊 ISO 8601(邻螴)㔕SQL(Ingres)㔕嚹䂘䔇 POSTGRES 㔕German 啕䓉鼯湚幋婔㔗SQL 湺庖襕挗嘪䫘 ISO 8601 湚嚟㔗"SQL"膷庺湚嚟䔇劉庖滇寖埾啽䇽㔗臘8-14滆䴺庖懟䓉膷庺鼯湚䔇冋床㔗date 启 time 䌂傋䔇膷庺嘷䇽埻滇䂍庺䔇冋床麯麵䔇斖橘启施閘鄘彖㔗
臘8-14. 斖橘/施閘膷庺鼯湚
| 鼯湚 | 柟誄 | 冋床 |
|---|---|---|
| ISO | ISO 8601/SQL 湺庖 | 1997-12-17 07:37:16-08 |
| SQL | 嚹䂘鼯湚 | 12/17/1997 07:37:16.00 PST |
| POSTGRES | 寘哋鼯湚 | Wed Dec 17 07:37:16 1997 PST |
| German | 婄寺鼯湚 | 17.12.1997 07:37:16.00 PST |
套悩弄滯庖 DMY 釺废闼幽婘 SQL 启 POSTGRES 鼯湚麯斖橘婘橽傘幋嬉庺䯄劥彍橽傘庺䯄婘斖橘幋嬉(埗黙誗8.5.1䩋䩋認婻螆䘞套嘘嘌巉凹膷噖唚䔇蓼麪)㔗臘8-15滆䴺庖婔婻冋床㔗
臘8-15. 斖橘釺废幹愇
| datestyle 螆䘞 | 膷噖釺废 | 膷庺湙冋 |
|---|---|---|
| SQL, DMY | 斖/橽/幘 | 17/12/1997 15:37:16.00 CET |
| SQL, MDY | 橽/斖/幘 | 12/17/1997 07:37:16.00 PST |
| Postgres, DMY | 斖/橽/幘 | Wed 17 Dec 07:37:16 1997 PST |
interval 䔇膷庺䩋蕙準償膷噖湚嚟埻滇償 century 潡 week 認湙䔇剘嘉赆蘸扵潊幘启斖蔯 ago 赆蘸扵潊劽锗䔇严埙㔗婘 ISO 昇嚟婋膷庺䩋蕙準償
[quantity unit [...]] [days] [hours:minutes:seconds]
䫘潙埇傖䫘 SET datestyle 变傴锬埡斖橘/施閘䔇鼯湚幘埇傖婘陉䘞桺傽 postgresql.conf 婺䔇 DateStyle 埗昄婺螆䘞潡蔙婘橉媇単潡垵潙䆇䔇 PGDATESTYLE 䯇嵄埻麟婺螆䘞㔗潏傸幘埇傖䫘湚嚟寡庘昄 to_char(埗蓕誗9.8)準敘䕕昂婄毓彽施閘/斖橘婄膷庺㔗
施寺启施寺幹愇婉備備埖婄䊄庹嘘嘵䪽䔇嘌巉誻埖彄櫪澂喿垔䔇嘌巉㔗彄庖 19 婡亻噘䊄䔇施寺埻冖䘉冞湺庖寡庖底嘖滇誻滇滷庯镺埖锟懟䔇媞櫹鄘彖滇啹婺崟施彽蓇彍㔗PostgreSQL 䕞嬉櫇毕 1902 幘彄 2038 幘幋閘䔇崟施彽媇敇(凹庫庯嚹䂘 Unix 係䂘施閘䔇垯昘虘庥)㔗套悩施閘轙誺認婻评啘闼幽啺螆施閘滇欔锬施寺䔇"湺庖施閘"婉䞇垄傸芘婘巻婻幘傘麯麵㔗
PostgreSQL 婘噩傋庫䫘婺儘埇脘婯 SQL 䔇垔幬䕩噚垹㔗嘖 SQL 湺庖婘斖橘/施閘䌂傋启媘脘婪橬婔底喺攻䔇晙晖㔗婴婻滆蔯滷蓕䔇閞鵻滇
date 䌂傋婯施寺澇橬蕫係蔯 time 䌂傋剘橬潡埇傖橬㔗䇽蔯䯄垂婡䘯䔇施寺埻橬婘婯施閘启斖橘鄘噿蕫施欉橬懟幬啹婺施閘啟䓂麟(施噞)埇脘啹婺垂臯䌂嚚崟施彽認湙䔇彽庥蔯婘婔幘麯橬欔埻寡㔗
䚺䩕䔇施寺䫘婔婻昄庖婩麟臘䴺婯 UTC 䔇啟䓂(施噞)㔗啹溴嘷虘 DST(崟施彽)䘯鍊啔斖橘/施閘䞖橇施潏傸湹橸婉埇脘檪崟施彽認湙䔇啹䘹螇䞖誕寂㔗
婺庖噋橉認底啄锆潏傸傺螞婘嘪䫘施寺䔇施唍嘪䫘闼底劯施寙劆斖橘启施閘䔇斖橘/施閘䌂傋㔗潏傸傺螞婉襕嘪䫘 time with time zone 䌂傋(儘䞇 PostgreSQL 庺庯劽䊖庫䫘傖埪婺庖婯噽垄 RDBMS 噚垹䔇蔄荏櫇毕認婻䌂傋)㔗PostgreSQL 啺螆嘹䫘庯傂嘘䌂傋䔇橸婄施寺鄘埻寙劆斖橘潡施閘(蔯婉寙劆施寺)㔗
婘係䂘喙鄘欔橬斖橘启施閘鄘䫘噘䊄䂘婔施閘 UTC 湚嚟庻嗘施閘婘埏䂍垵潙嬉䆇嬉䫌昄扞康橉媇単湹扞 timezone 陉䘞埗昄弄滯䔇施寺蘸扵潊橸婄施閘㔗
PostgreSQL 噕螩嘹䫘婬䓉桹濘毺垔施寺
垯昘䔇施寺劉㔗冋套 America/New_York 㔗欔橬埇傖臖彆䔇施寺劉婘 pg_timezone_names 蓖商婺彖庺(埗蓕誗43.49)㔗PostgreSQL 嘪䫘幪濕嘪䫘䔇 zic 施寺昄扞欔傖認底施寺劉婘噽垄蘇傽麯幘脘赆蘂溆䔇臖彆㔗
施寺䚷喍㔗冋套 PST 㔗認䓉䚷喍劉锔婩埻滇垔幬庖䕩凹庯 UTC 䔇啟䓂麟蔯嬉婔䓉垯昘䔇施寺劉埇脘誻锊劆五婔䂇崟施彽蘸扵蓇彍㔗欔橬埇傖臖彆䔇施寺䚷喍婘 pg_timezone_abbrevs 蓖商婺彖庺(埗蓕誗43.48)㔗嘹婉脘嘪䫘施寺䚷喍準螆䘞 timezone 陉䘞埗昄嘖滇嘹埇傖婘斖橘/施閘膷噖唚婺䂷劽 AT TIME ZONE 淉嘩严嘪䫘施寺䚷喍㔗
鍴垯昘䔇施寺劉埪噽䚷喍幋崡PostgreSQL 誻毖埖 POSIX 鼯湚䔇 STDoffset 潡 STDoffsetDST 湚嚟䔇施寺噽婺䔇 STD 滇施寺䚷喍㔕offset 滇婔婻䕩凹庯 UTC 䔇償施啟䓂麟㔕DST 滇婔婻埇锬䔇崟施彽施寺䚷喍(啺垔䕩凹庯䂍垔䔇啟䓂麟柊嬉婔償施)㔗冋套套悩 EST5EDT 婉滇婔婻噾臖彆䔇施寺劉闼幽垄儖京劯庯䆯啘婩鄘施閘套悩庻婘崟施彽施寺闼幽垄儖毬䙓䆯啘䔇施寺蓇彍嘪䫘啹溴認婻䬹攓婘寖䆯噂幋崡澇傔幽䫘崇㔗驔襕柊閐䔇滇認婻䬹攓嚔凚躘旇旇䔇毖埖婉劽䊖䔇膷噖啹婺垄婉凹施寺䚷喍䔇劽䊖攓啔演昖㔗冋套SET TIMEZONE TO FOOBAR0 婉嚔檖髍蔯滇嘪係䂘嘪䫘 GMT 㔗
垯昘䔇施寺劉婯施寺䚷喍婘䊖螺婯垂處幋閘庻婘噞嚗施寺䚷喍攂滇傼臘婔婻䕩凹庯 UTC 䔇啺垔啟䓂麟䇽蔯崓崔昄垯昘䔇施寺劉锊劆五婔婻橸婄崟傴施蓇彍啹溴儌橬埇脘橬婴婻䕩凹庯 UTC 䔇婉劯啟䓂麟㔗
攂嘷蔯蘔PostgreSQL 8.2 䬽橸傖劯施寺劉婘欔橬愙喕婋鄘滇崓償喍方噿䔇㔗蔯幋嬉䔇䬽橸婘昊底愙喕婋滇崓償喍星懘䔇㔗
方螺滇垯昘䔇施寺劉誻滇施寺䚷喍鄘婉滇䇸誂毖誕橉媇単䔇垄傸鄘滇傯垬輙䕞嘘婋䔇 .../share/timezone/ 启 .../share/timezonesets/ 陉䘞桺傽婺诙埡䔇(埗蓕䆹B.3)
埇傖婘 postgresql.conf 桺傽麯螆䘞 timezone 陉䘞埗昄潡蔙䫘傂嘘噽垄婘䆹17柟誄䔇湺庖桹濘㔗鍴溴幋崡誻橬喘庹䓉䬹枪桹濘埇傖螆䘞垄
套悩斵澇橬婘 postgresql.conf 麯幘澇橬婘变傴臯嚔噿婪弄滯 timezone 闼幽橉媇単儖臘商嘪䫘橉媇単婂橺婪䔇 TZ 䯇嵄埻麟嘩婺橉媇単䔇䚺䩕施寺㔗套悩 TZ 澇橬垔幬潡蔙滇 PostgreSQL 婉螴臖䔇施寺劉闼幽橉媇単儖臘商锔誺演昖 C 康庘昄 localtime() 䔇臯婺準彴桺淉嘩係䂘䔇䚺䩕施寺㔗䚺䩕施寺滇毬䙓橔毖誏 PostgreSQL 䔇噾䘖施寺䔇寘彍準锬拷䔇㔗
嘪䫘 SQL 变傴 SET TIME ZONE 婺嚔臺螆䘞施寺認滇 SET TIMEZONE TO 䔇婔婻埇锬䔇拚喍桹嚟敘媹噚垹湺庖㔗
套悩婘垵潙䆇螆䘞庖 PGTZ 䯇嵄埻麟闼幽 libpq 婘誂毖施儖嘪䫘認婻䯇嵄埻麟䂍劯䆇埏锕婔婻 SET TIME ZONE 变傴㔗
PostgreSQL 嘪䫘凐䘖寖濘螇䞖欔橬斖橘/施閘啺螆婔幘䔇阪庥滇 365.2425 崷㔗認婻桹濘埇傖冽來䇞婄鵇螇/螇䞖傯 4713 BC(噸噄嬉 4713 幘)彄冽幙䔇橻準䔇傂懟婔崷䔇斖橘㔗
19 婡亻傖嬉䔇斖橘嚹䂘(寖濘)埻滇凹婔底轼叿臂䬷橬懟幬蔯婘潏傸認麯喘償澇橬噙彖䔇䊖䫌檪垄傸䚡乕噖斖橘/施閘毓彽単麯麵寂㔗