日期與時間函式

Redshift 入門

Jason Myers

Principal Architect

取得目前日期與時間

SYSDATE 交易開始時的日期與時間

-- 取得目前日期與時間
SELECT SYSDATE;

timestamp
============================
2024-01-27 20:05:55.976353

GETDATE() 陳述式開始時的日期與時間，需加括號

-- 取得目前日期與時間
SELECT GETDATE();

timestamp
============================
2024-01-27 20:06:55.976353

日期與時間函式的行為

注意僅限 leader 節點的函式！

用 DATEDIFF 取代 AGE
用 GETDATE／SYSDATE 取代僅限 leader 的函式：
- CURRENT_TIME
- CURRENT_TIMESTAMP
- ISFINITE
- LOCALTIME
- LOCALTIMESTAMP
- NOW

截斷日期與時間

TRUNC 從 timestamp 傳回 date

-- 依據下列 SYSDATE 取得當天日期 
-- 2024-01-27 20:05:55.976353
SELECT TRUNC(SYSDATE);

2024-01-27

DATE_TRUNC('datepart', timestamp) 依指定單位（如 hour、day）截斷

-- 依據下列 SYSDATE 截斷到 minute 
-- 2024-01-27 20:05:55.976353
SELECT DATE_TRUNC('minute', SYSDATE);

2024-01-27 20:05:55

¹ https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html

擷取日期與時間戳的部分欄位

DATE_PART(datepart, date or timestamp)
- 從 date 或 timestamp 擷取指定部分

-- 依據下列 SYSDATE 取得目前月份 
-- 2024-01-27 20:05:55.976353
SELECT DATE_PART(month, SYSDATE);

不只支援 month、day、year
- 範例：dayofweek、quarter、timezone

-- 依據下列 SYSDATE 取得星期幾 
-- 2024-01-27 20:05:55.976353
SELECT DATE_PART(dayofweek, SYSDATE);

比較日期與時間

DATE_CMP(date_1, date_2) 相對比較
- 若 date_1 較早則回傳 -1
- 若相等則回傳 0
- 若 date_1 較晚則回傳 1
型別專用函式
- DATE_CMP_TIMESTAMP
- DATE_CMP_TIMESTAMPTZ
- TIMESTAMP_CMP
- TIMESTAMP_CMP_TIMESTAMPTZ
- TIMESTAMPTZ_CMP

-- 依據下列 SYSDATE 比較資料表中的 5 個日期 
-- 2024-01-27 20:05:55.976353
  SELECT date_col, 
         TRUNC(SYSDATE) AS current_date,
         DATE_CMP(date_col, SYSDATE)
    FROM combined_history_projections
ORDER BY date_col
   LIMIT 3;

 date_col  |  current_date | date_cmp
===========|===============|==========
2024-01-26 | 2024-01-27    |       -1
2024-01-27 | 2024-01-27    |        0
2024-01-28 | 2024-01-27    |        1

計算差異

DATEDIFF(datepart, value_1, value_2)
任一位置可為 date、time、timetz 或 timestamp
- 必須包含 datepart
回傳
- 若 value_2 較早則為負值
- 若相等則為 0
- 若 value_2 較晚則為正值

使用 DATEDIFF

-- 距離第一季結束還有幾天（依據下列 SYSDATE）
-- 2024-01-27 20:05:55.976353
SELECT DATEDIFF(day,TRUNC(SYSDATE),'2024-03-31') AS days_diff;

days_diff
===========
64

遞增日期與時間

DATEADD(datepart, quantity, value)
支援 date、time、timetz、timestamp
quantity 可為負數以執行相減

-- 對日期加上一週（依據下列 SYSDATE）
-- 2024-01-27 20:05:55.976353
SELECT TRUNC(SYSDATE) AS todays_date,
       TRUNC(DATEADD(week, 1, SYSDATE)) AS next_weeks_date;

todays_date | next_weeks_date
============|==================
2024-01-27  | 2024-02-03

遞增日期與時間的注意事項

以月份增加閏年日期會回傳該月月底

-- 以月份為單位對日期加上年份
SELECT DATEADD(month, 12, '2024-02-29');

2025-02-28 00:00:00

以年份增加閏年日期會回傳隔天

-- 以年份為單位對日期加上一年
SELECT DATEADD(year, 1, '2024-02-29');

2025-03-01 00:00:00

一起來練習吧！

Redshift 入門