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