iT邦幫忙

2022 iThome 鐵人賽

1

如果你認識的人結婚,你可能會問喜宴是什麼時候?
接著有人會告訴你哪一天或是有人會告訴你哪一天的幾點幾分,
接著就在Google行事曆把這個 [日期] 或 [日期+時間] 給標記起來,
這是一件關於日期時間的日常事件,常常在標記的當下決策要記錄的多仔細。

資料庫則是一開始就要規劃好記錄的資料需要多仔細,並且視情況決定是否需要把時差給考量近來,我們曾經在[Day 08 建立資料表] 當中列出日期的資料型別,本篇將延伸這個主題介紹並且認識相關函數。

時間資料型別

  • DATE : 只有日期
  • TIME : 只有時間
  • TIMESTAMP : 日期+時間
  • TIMESTAMPTZ : 日期+時間+時區

參考 : 8.5. 日期時間型別

相關函數

  • TIMEZONE
  • NOW
  • CURRENT_TIME
  • CURRENT_DATE

參考 : 9.9 日期時間函式及運算子

時間戳(TIMESTAMP)

PostgreSQL時間戳有兩種,不包含時區的TIMESTAMP與包含時區的TIMESTAMPTZ

PostgreSQL預設使用UTC(世界標準時間)來儲存時間戳資料,如果是TIMESTAMPTZ則會受postgresql.conf 設定值影響,這個設定檔是安裝PostgreSQL時會參考本機的時區配置,也就是說如果使用TIMESTAMP時區設定沒有什麼影響。

官方文件會將UTC與GMT混為一談,這是因為UTC與GMT時間幾乎一致 (僅有潤秒差異),不過它們意義上並不相同,GMT(格林威治標準時間)是時區、UTC(世界標準時間)是時間標準。

這篇提供一篇有趣的文章,可以知道潤秒差異 : 到底是 GMT+8 還是 UTC+8 ?

PostgreSQL包含了三種型式的時區

  1. 完整時區名稱,例如Asia/Taipei。
  2. 時區縮寫,例如PST,美國太平洋標準時間。
  3. POSIX-style時區,例如PST8PDT。

顯示時區

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 VS TIMESTAMPTZ

接著我們來看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'
);

https://ithelp.ithome.com.tw/upload/images/20221001/20129430t3DteLhafZ.png

TIMESTAMP OR DATES ?

那麼講這麼多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)

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;

上一篇
Day 21 子查詢
下一篇
Day 23 SQL條件判斷式
系列文
資料庫新手入門--以PostgreSQL為例30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言