PostgreSQL 提供兩種存儲(chǔ)時(shí)間戳的數(shù)據(jù)類型： 不帶時(shí)區(qū)的 TIMESTAMP 和帶時(shí)區(qū)的 TIMESTAMPTZ。
TIMESTAMP 數(shù)據(jù)類型可以同時(shí)存儲(chǔ)日期和時(shí)間，但它不存儲(chǔ)時(shí)區(qū)。這意味著，當(dāng)修改了數(shù)據(jù)庫(kù)服務(wù)器所在的時(shí)區(qū)時(shí)，它里面存儲(chǔ)的值不會(huì)改變。
TIMESTAMPTZ 數(shù)據(jù)類型在存儲(chǔ)日期和時(shí)間的同時(shí)還能正確處理時(shí)區(qū)。PostgreSQL 使用 UTC 值來存儲(chǔ) TIMESTAMPTZ 數(shù)據(jù)。在向 TIMESTAMPTZ 字段插入值的時(shí)候，PostgreSQL 會(huì)自動(dòng)將值轉(zhuǎn)換成 UTC 值，并保存到表里。當(dāng)從一個(gè) TIMESTAMPTZ 字段查詢數(shù)據(jù)的時(shí)候，PostgreSQL 會(huì)把存儲(chǔ)在其中的 UTC 值轉(zhuǎn)換成數(shù)據(jù)庫(kù)服務(wù)器、用戶或當(dāng)前連接所在的時(shí)區(qū)。
TIMESTAMP 和 TIMESTAMPTZ 都使用8字節(jié)存儲(chǔ)空間。如下所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT typname, typlenFROM pg_typeWHERE typname ~ '^timestamp'; typname | typlen-------------+-------- timestamp | 8 timestamptz | 8(2 rows) |
重要提示，TIMESTAMPTZ 并不會(huì)存儲(chǔ)時(shí)區(qū)，它只是了 UTC 值，然后會(huì)和當(dāng)前時(shí)區(qū)進(jìn)行轉(zhuǎn)換。
時(shí)間戳示例
首先，創(chuàng)建一個(gè)含有 TIMESTAMP 和 TIMESTAMPTZ 的表：
CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ);
接下來，將數(shù)據(jù)庫(kù)服務(wù)器的時(shí)區(qū)設(shè)置為 America/Los_Angeles：
SET timezone = 'America/Los_Angeles';
順便說一句，可以使用 SHOW TIMEZONE 語句來顯示當(dāng)前時(shí)區(qū)：
SHOW TIMEZONE;
然后，插入一些示例數(shù)據(jù)：
|
1
2
3
4
5
6
|
INSERT INTO timestamp_demo (ts, tstz)VALUES ( '2016-06-22 19:10:25-07', '2016-06-22 19:10:25-07' ); |
最后，查詢：
|
1
2
3
4
5
6
7
8
|
SELECT tsFROM timestampz_demo; ts | tstz---------------------+------------------------ 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07(1 row) |
再把時(shí)區(qū)設(shè)置為 America/New_York：
SET timezone = 'America/New_York';
再查詢一次，結(jié)果如下：
|
1
2
3
4
|
ts | tstz---------------------+------------------------ 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04(1 row) |
TIMESTAMP 類型字段的值不變，而 TIMESTAMPTZ 類型字段的值變成了當(dāng)前時(shí)區(qū)下的時(shí)間。
補(bǔ)充：postgresql timestamp timestamptz 使用注意事項(xiàng)
postgresql日期時(shí)間類型分為timestamp [ (p) ] [ without time zone ]和timestamp [ (p) ] with time zone,它們的區(qū)別在于一個(gè)無時(shí)區(qū),另一有時(shí)區(qū),存儲(chǔ)大小均為8字節(jié).
同時(shí)也要注意time [ (p) ] [ without time zone ]和time [ (p) ] with time zone類型,無時(shí)區(qū)存儲(chǔ)大小為8字節(jié),有時(shí)區(qū)存儲(chǔ)大小為12字節(jié).
postgresql中日期時(shí)間類型縮寫:
timestamp
timestamptz
date (僅年月日部份,存儲(chǔ)大小為4字節(jié))
time
timetz
interval(表示間隔,存儲(chǔ)大小為16字節(jié))
interval可以通過函數(shù)make_interval來創(chuàng)建,支持年月星期天小時(shí)分鐘秒的間隔.如now() + make_interval(days => 10)表示10天后,now() - make_interval(secs => 30)表示30秒之前
為保證數(shù)據(jù)的準(zhǔn)確性在保存使用計(jì)算過程中應(yīng)盡量使用timestamptz和timetz,盡量避免使用timestamp和time
UTC+8(EAT - 東亞標(biāo)準(zhǔn)時(shí)間/中國(guó)標(biāo)準(zhǔn)時(shí)間(BJT))
注意整個(gè)中國(guó)使用相同的時(shí)區(qū)，這就使得這個(gè)時(shí)區(qū)特別的大。在中國(guó)最西部的地區(qū)，太陽最高的時(shí)候是下午3點(diǎn)，在最東部是上午11點(diǎn)。單純從地理規(guī)劃來看，整個(gè)中國(guó)橫跨了從東五區(qū)（UTC+5）到東九區(qū)（UTC+9）共計(jì)五個(gè)時(shí)區(qū)。
在postgresql中查詢時(shí)區(qū)的定義
select * from pg_timezone_names
或查詢東亞/中國(guó)時(shí)區(qū)定義
select * from pg_timezone_names where utc_offset = '+08:00:00';
查詢結(jié)果中的"PRC","Asia/Shanghai","Asia/Chongqing"均表示中國(guó)
生成一個(gè)日期時(shí)間示例
select make_timestamptz(1970,1,1,0,0,0.0,'Asia/Shanghai')
注意避免使用timestamp類型相關(guān)函數(shù),如:make_timestamp
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持服務(wù)器之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
原文鏈接：https://pg.sjk66.com/
