如果你認識的人結婚,你可能會問喜宴是什麼時候?
接著有人會告訴你哪一天或是有人會告訴你哪一天的幾點幾分,
接著就在Google行事曆把這個 [日期] 或 [日期+時間] 給標記起來,
這是一件關於日期時間的日常事件,常常在標記的當下決策要記錄的多仔細。
資料庫則是一開始就要規劃好記錄的資料需要多仔細,並且視情況決定是否需要把時差給考量近來,我們曾經在[Day 08 建立資料表] 當中列出日期的資料型別,本篇將延伸這個主題介紹並且認識相關函數。
參考 : 8.5. 日期時間型別
參考 : 9.9 日期時間函式及運算子
PostgreSQL時間戳有兩種,不包含時區的TIMESTAMP
與包含時區的TIMESTAMPTZ
。
PostgreSQL預設使用UTC(世界標準時間)來儲存時間戳資料,如果是TIMESTAMPTZ
則會受postgresql.conf 設定值影響,這個設定檔是安裝PostgreSQL時會參考本機的時區配置,也就是說如果使用TIMESTAMP
時區設定沒有什麼影響。
官方文件會將UTC與GMT混為一談,這是因為UTC與GMT時間幾乎一致 (僅有潤秒差異),不過它們意義上並不相同,GMT(格林威治標準時間)是時區、UTC(世界標準時間)是時間標準。
這篇提供一篇有趣的文章,可以知道潤秒差異 : 到底是 GMT+8 還是 UTC+8 ?
PostgreSQL包含了三種型式的時區
SHOW TIMEZONE;
postgresql.conf設定檔在哪? 這邊僅提供我安裝在Windows11作業系統上的預設路徑做為參考。
C:\Program Files\PostgreSQL\14\data
我們有兩種變更時區的方法,一種是暫時性的,另一種是持久性的。
暫時性的更改時區 : 只存在於當下的SESSION,重新登入就會發現設定又回到原本的時間,通常是方便測試所使用,如果真的需要長期變更就必需使用持久性的語法。
// 暫時性
SET TIME ZONE 'UTC';
// 持久性
ALTER DATABASE ithome2022 SET timezone TO 'UTC';
日常生活中會看到很多不一樣的時間戳格式,例如我們習慣的2022/10/1 08:05或是西方使用的10/1/2022 08:05等等,PostgreSQL 使用 ISO-8601 國際標準化組織的日期和時間的表示方法。
ISO-8601 是一種格式化標準
-- YYYY-MM-DDTHH:MM:SS
-- 2022-10-01T09:30:25
我們可以下指令查看目前的時間為何,格式會是如上所描述的ISO-8601。
SELECT now();
接著我們來看TIMESTAMP與TIMESTAMPTZ的差異,我們存入帶有時差的時間格式,可以看到如果是TIMESTAMP則會忽略,TIMESTAMPTZ則會加上時差,如果沒有特殊需求一率建議使用TIMESTAMP。
CREATE TABLE timezones (
ts TIMESTAMP WITHOUT TIME ZONE,
tz TIMESTAMP WITH TIME ZONE
);
INSERT INTO timezones VALUES (
TIMESTAMP WITHOUT TIME ZONE '2022-10-01 11:30:00-05' ,
TIMESTAMP WITH TIME ZONE '2022-10-01 11:30:00-05'
);
那麼講這麼多TIMESTAMP ,一定要儲存TIMESTAMP? 不是還有DATES?
這取決於需求資料的時間顆粒,例如你的需求是記錄某個員工在哪一天離職,那麼完全可以使用DATES即可,但如果是要查看什麼時候登入系統? 通常就必需精細到秒,所以使用TIMESTAMP,如果是記錄某場考試當中,研究生各自花多少解題,就可以單純使用TIME。(不過TIME的使用情境較少)
SELECT NOW()::DATE;
SELECT CURRENT_DATE;
前面提到 PostgreSQL 使用 ISO-8601 儲存日期時間,不過我們可以透過TO_CHAR
函數改變輸出的格式,例如希望輸出的樣式跟WINDOWS電腦右下角表示時間一樣使用/來間隔年月日或是透過DDD這個參數來找出某個日期是一年當中的第幾天。day of year (001-366)
參考 : 9.8. 型別轉換函式
SELECT TO_CHAR(CURRENT_DATE, 'yyyy/mm/dd');
SELECT TO_CHAR(CURRENT_DATE, 'DDD');
想知道兩個日期差距幾天,可以直接使用運算符號減號做計算,會得到以天為單位的差距 (Days HH:MI:SS.MS),例如中華民國距離選舉日還有多久?
SELECT '2022-11-26' - NOW();
只想確認差幾天的話,可以再加上::DATE來取得。
SELECT '2022-11-26' - NOW()::DATE;
在許多的業務需求中會需要取得使用者的年齡,我們通常會儲存生日,再利用AGE函數去取得年齡。那麼不能直接儲存年齡多少嗎? 想想看如果你遇到一個小弟弟跟你說,我叫野原新之助,今年五歲,那就是明年六歲,後年七歲…資料庫將需要一直變更資料。
SELECT AGE(DATE '1987-07-22');
其實大S跟酷龍年齡沒有差很多!
SELECT AGE(DATE '1976-10-06' , DATE '1969-09-11');
來看比較實際一點的範例,假設有一個員工的資料表儲存生日欄位,那麼我們可以透過三種方式來取得年齡。
-- 方法1
SELECT name, DATE_PART('year', AGE(birthdate)) AS age
FROM employees
-- 方法2
SELECT name, DATE_TRUNC('year', AGE(birthdate)) AS age
FROM employees
-- 方法3
SELECT name, EXTRACT(YEAR FROM AGE(birthdate)) AS age
FROM employees
INTERVAL提供一種自然語言的方式來找到資料,例如找出30天內的訂單。
SELECT *
FROM orders
WHERE purchaseDate <= NOW() - INTERVAL '30 days';
Identifiers | 識別 |
---|---|
years | 年 |
months | 月 |
days | 日 |
hours | 時 |
minutes | 分 |
INTERVAL可以讓西元年轉民國年這件事情變簡單,這意味著你不需要取出日期之後轉字串並取出年的部分再減掉1911最後再組合來的方式,最後我用TO_CHAR讓年份顯示三位數即可。
SELECT TO_CHAR(ts - INTERVAL '1911 YEAR','yyy-mm-dd')
FROM timezones;