SQLで日付の操作

日付とか年齢とかの計算方法を忘れがちなので備忘録。

よく使う日付に関する型は以下の2つです。

• date型 : 日付まで。'YYYY-MM-DD'
• timestamp型 : 日付と時間。'YYYY-MM-DD HH:MM:SS'

今回は以下の表を操作します。

date_test表

 int_date |   str_date   |       ts_date       
----------+--------------+---------------------
 20200101 | 2020年1月1日 | 2020-01-01 00:00:00
 20200201 | 2020年2月1日 | 2020-02-01 00:00:00
 20200301 | 2020年3月1日 | 2020-03-01 00:00:00

文字列からdate型への変更

TO_DATE()を使います。

SELECT TO_DATE(str_date, 'YYYY年mm月dd日')
FROM date_test;

実行結果

   to_date   
------------
 2020-01-01
 2020-02-01
 2020-03-01
(3 rows)

intからdate型に変更

文字列に変換してからdateに変換します。

SELECT TO_DATE(TO_CHAR(int_date, 'FM99999999'), 'YYYYmmdd')
FROM date_test;

実行結果

  to_date   
------------
 2020-01-01
 2020-02-01
 2020-03-01
(3 rows)

timestampからdate型に変更

CAST(カラム名 AS DATE)を使います。

SELECT CAST(ts_date AS DATE) 
FROM date_test;

実行結果

 ts_date  
------------
 2020-01-01
 2020-02-01
 2020-03-01
(3 rows)

日付に関する演算

期間内のレコードを取得

date型同士は普通に足し引きできる。

SELECT str_date 
FROM date_test
WHERE '2020-04-01' - TO_DATE(str_date, 'YYYY年mm月dd日') < 40; 

実行結果

    date    
------------
 2020-03-01
(1 row)

2020年4月1日から40日以内のレコードのみ取得しました。

年齢のカラムを作成してみる

2038年時点での年齢を計算しました。

SELECT ('2038-01-01' - CAST(ts_date AS DATE) )/365 as age 
FROM date_test;

実行結果

 age 
-----
  18
  17
  17