天下苦postgres時間轉換久已！

最近在操作數據庫時，遇到頻繁的時間操作，每次弄完了就忘了，今天痛定思痛，下定決心（終于自己也受不了自己的lazy了）對postgres的時間操作進行一下總結。本文竟可能詳盡的記錄postgres中涉及到date、timestamp以及和字符串之間的轉換進行說明。

1.utc對timestamp的轉換

在postgres中沒有utc這個概念，為什么？這個問題沒有深入的調研，沒有什么我們不關心，但是與之對應的有一個概念——epoch，其意思為新紀元，新時代，怎么定義的新呢，計算機上面就是指1970-01-01 00:00:00之后的時間，有人會說這不就是utc嗎，好吧，我承認買了個關子，但是有助于記憶。

postgres中的時間可以精確到小數點后五位，也就是10微秒的單位，已經很小了，我們先來感受一下在pg中獲取epoch：

--把時間戳轉成epoch值 postgres=# select extract(epoch from now()); date_part --- 1533803404.49598 (1 row)

上面是將當前時間戳轉化為epoch，轉換的過程中主要用到extract函數，

--將具體時間戳轉換為utc select extract(epoch from timestamp without time zone '1970-01-01 01:00:00');date_part -----------3600 (1 row) --根據具體時間進行計算，一個小時的utc select extract(epoch from interval '+1 hours');date_part -----------3600 (1 row) --同上 select extract(epoch from interval '-1 hours');date_part ------------3600 (1 row)

下面是將epoch值也就是utc值，轉換為時間戳，如下：

--把epoch 值轉換回時間戳 postgres=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1447898857.74524 * INTERVAL '1 second'; ?column? --- 2015-11-19 10:07:37.74524+08

其他都很好理解，這里有一點需要注意，就是with time zone，字面意思很好理解，就是帶時區，如果不帶時區怎么表示？對了就是without time zone,我們將上面語句進行不帶時區的運行，如下：

SELECT TIMESTAMP WITHout TIME ZONE 'epoch' + 1447898857 * INTERVAL '1 second'; --- 2015-11-19 02:07:37

整整相差了8個小時，不賣關子，其主要區別如下：

沒有時區代表的是絕對時間，absolute timestamp，即 UTC (UTC+0) 時間。

帶著時區的代表相對時間，relative timestamp，即當地時間，如北京的當地時間是 UTC+8 的時間。

使用的一個最佳實踐是時間類型都設為 timestamp with time zone 類型，只有在根據 timestamp 進行 partition 時才使用 timestamp without time zone 類型，因為 partition 必須使用 immutable 數據 (即在任何情況下數據取出來都一樣)，而 timestamp with time zone 的數據值與 postgres 配置的 timezone 有關。

這兩種數據類型的區別是:

• 以當地時間存儲數據到 timestamp with time zone 類型的字段時，postgres 底層會以 UTC 時間存儲，展示數據時會根據 postgres 設置的 timezone 顯示為當時時間。
• 以當地時間存儲數據到 timestamp without time zone 類型的字段時，postgres 底層以輸入的數據進行存儲，展示時會原樣展示，與 postgres 設置的時區無關。

查看當前時間戳

下面這是查看當前時間戳的幾種方式，如下：

--查看當前的時間戳，還有函數now()postgres=# select clock_timestamp(),current_timestamp,localtimestamp;clock_timestamp | now | timestamp -------------------------------+-------------------------------+----------------------------2016-02-02 17:54:15.547194+08 | 2016-02-02 17:54:15.546956+08 | 2016-02-02 17:54:15.546956

clock_timestamp和current_timestamp都有時區信息，而localtimestamp沒有時區信息

時間戳加減

--時間加減 postgres=# select date '2016-02-02 10:00:00'+ interval '10 minutes'; ?column? ---2016-02-02 00:10:00

時間戳格式轉化

將時間戳根據具體格式轉換為字符串：

SELECT to_char((TIMESTAMP WITH TIME ZONE'epoch' + 1447898857 * INTERVAL '1 second' ),'yyyy-MM');2015-11

時間段內的隨機時間

--直接用sql生成隨機日期時間select '2015-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval;

random()生成一個0到1的隨機數，trunc()對生成隨機數進行截取，剩下的參考時間戳加減過程的相關做法。

--創建隨機日期時間函數 CREATE OR REPLACE FUNCTION rand_date_time(start_date date, end_date date) RETURNS TIMESTAMP AS BODY DECLARE interval_days integer; random_seconds integer; random_dates integer; random_date date; random_time time; BEGIN interval_days := end_date - start_date; random_dates:= trunc(random()*interval_days);random_date := start_date + random_dates; random_seconds:= trunc(random()*3600*24); random_time:=' 00:00:00'::time+(random_seconds || ' second')::INTERVAL;RETURN random_date +random_time; END; BODY LANGUAGE plpgsql; --生成指定時間內的隨機時間 SELECT rand_date_time('2000-01-01', '2013-12-31');

不同時區日期轉化

--不同時區之間的轉換,pst美國太平洋標準時間postgres=# select '2016-02-03 09:07:30.816885+08' at time zone 'pst'; timezone ---2016-02-02 17:07:30.816885 (1 row) --cct中國沿海時間 postgres=# select '2016-02-03 09:07:30.816885+08' at time zone 'cct'; timezone ---2016-02-03 09:07:30.816885 (1 row) --將西五區轉到東八區 postgres=# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'cct';timezone ---2001-02-17 09:38:40

系統時區查看

--查看系統支持的時區select * from pg_timezone_names ;

系統時區設置

--時區設置參數 timezone = 'PRC' --修改時區的方法1. 全局參數postgresql.conftimezone='UTC' 2. 數據庫級配置alter database dbname set timezone='UTC';pipeline=# select * from pg_db_role_setting ;setdatabase | setrole | setconfig -------------+---------+--------------------------------------14930 | 0 | {TimeZone=UTC}3. 用戶級配置alter role rolname set timezone='UTC';或者alter role all set timezone='UTC';pipeline=# select * from pg_db_role_setting ;setdatabase | setrole | setconfig -------------+---------+--------------------------------------14930 | 0 | {TimeZone=UTC}0 | 0 | {TimeZone=UTC}

執行休眠時間設置

--休眠1.5秒后執行，單位秒 SELECT clock_timestamp(),pg_sleep(1.5),clock_timestamp();--休眠5分鐘，單位interval SELECT clock_timestamp(),pg_sleep_for('5 minutes'),clock_timestamp();--到指定時間執行，注意這些休眠時間不是完全精確的 SELECT clock_timestamp(),pg_sleep_until('today 10:00'),clock_timestamp();

字符串到時間戳和日期

主要涉及到的函數為todate和toTimestamp，示例如下：

select to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') --轉化為日期 2018-03-12 --對日期進行比較，輸出booleanselect current_timestamp <= to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') f select current_timestamp <= to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') flag; select to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') --- f 2018-03-12 18:47:35+08

從上面比較可以看出，date和timestamp的主要區別，需要特別說明的一點是，在指定日期的格式的時候，特別注意時分秒的格式指定問題（使用慣了java的格式，用這個會有點不習慣）。

總結

以上是生活随笔為你收集整理的postgres的时间转换的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。