PostgreSQL TIMESTAMP類型 時間戳操作

PostgreSQL 提供兩種存儲時間戳的數(shù)據(jù)類型： 不帶時區(qū)的 TIMESTAMP 和帶時區(qū)的 TIMESTAMPTZ。

TIMESTAMP 數(shù)據(jù)類型可以同時存儲日期和時間，但它不存儲時區(qū)。這意味著，當修改了數(shù)據(jù)庫服務(wù)器所在的時區(qū)時，它里面存儲的值不會改變。

TIMESTAMPTZ 數(shù)據(jù)類型在存儲日期和時間的同時還能正確處理時區(qū)。PostgreSQL 使用 UTC 值來存儲 TIMESTAMPTZ 數(shù)據(jù)。在向 TIMESTAMPTZ 字段插入值的時候，PostgreSQL 會自動將值轉(zhuǎn)換成 UTC 值，并保存到表里。當從一個 TIMESTAMPTZ 字段查詢數(shù)據(jù)的時候，PostgreSQL 會把存儲在其中的 UTC 值轉(zhuǎn)換成數(shù)據(jù)庫服務(wù)器、用戶或當前連接所在的時區(qū)。

TIMESTAMP 和 TIMESTAMPTZ 都使用8字節(jié)存儲空間。如下所示：

SELECT
   typname,
   typlen
FROM
   pg_type
WHERE
   typname ~ '^timestamp';
  typname  | typlen
-------------+--------
 timestamp  |   8
 timestamptz |   8
(2 rows)

重要提示，TIMESTAMPTZ 并不會存儲時區(qū)，它只是了 UTC 值，然后會和當前時區(qū)進行轉(zhuǎn)換。

時間戳示例

首先，創(chuàng)建一個含有 TIMESTAMP 和 TIMESTAMPTZ 的表：

CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ);

接下來，將數(shù)據(jù)庫服務(wù)器的時區(qū)設(shè)置為 America/Los_Angeles：

SET timezone = 'America/Los_Angeles';

順便說一句，可以使用 SHOW TIMEZONE 語句來顯示當前時區(qū)：

SHOW TIMEZONE;

然后，插入一些示例數(shù)據(jù)：

INSERT INTO timestamp_demo (ts, tstz)
VALUES
 (
 '2016-06-22 19:10:25-07',
 '2016-06-22 19:10:25-07'
 );

最后，查詢：

SELECT
 ts
FROM
 timestampz_demo;
     ts     |     tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)

再把時區(qū)設(shè)置為 America/New_York：

SET timezone = 'America/New_York';

再查詢一次，結(jié)果如下：

     ts     |     tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)

TIMESTAMP 類型字段的值不變，而 TIMESTAMPTZ 類型字段的值變成了當前時區(qū)下的時間。

補充：postgresql timestamp timestamptz 使用注意事項

postgresql日期時間類型分為timestamp [ (p) ] [ without time zone ]和timestamp [ (p) ] with time zone,它們的區(qū)別在于一個無時區(qū),另一有時區(qū),存儲大小均為8字節(jié).

同時也要注意time [ (p) ] [ without time zone ]和time [ (p) ] with time zone類型,無時區(qū)存儲大小為8字節(jié),有時區(qū)存儲大小為12字節(jié).

postgresql中日期時間類型縮寫:

timestamp

timestamptz

date (僅年月日部份,存儲大小為4字節(jié))

time

timetz

interval(表示間隔,存儲大小為16字節(jié))

interval可以通過函數(shù)make_interval來創(chuàng)建,支持年\月\星期\天\小時\分鐘\秒的間隔.如now() + make_interval(days => 10)表示10天后,now() - make_interval(secs => 30)表示30秒之前

為保證數(shù)據(jù)的準確性在保存\使用\計算過程中應(yīng)盡量使用timestamptz和timetz,盡量避免使用timestamp和time

UTC+8(EAT - 東亞標準時間/中國標準時間(BJT))

注意整個中國使用相同的時區(qū)，這就使得這個時區(qū)特別的大。在中國最西部的地區(qū)，太陽最高的時候是下午3點，在最東部是上午11點。單純從地理規(guī)劃來看，整個中國橫跨了從東五區(qū)（UTC+5）到東九區(qū)（UTC+9）共計五個時區(qū)。

在postgresql中查詢時區(qū)的定義

select * from pg_timezone_names

或查詢東亞/中國時區(qū)定義

select * from pg_timezone_names where utc_offset = '+08:00:00';

查詢結(jié)果中的"PRC","Asia/Shanghai","Asia/Chongqing"均表示中國

生成一個日期時間示例

select make_timestamptz(1970,1,1,0,0,0.0,'Asia/Shanghai')

注意避免使用timestamp類型相關(guān)函數(shù),如:make_timestamp

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。