[SQL] TIMESTAMP,DATE,DATETIMEについて

日付や時間について

日付や時間はTIMESTAMP,DATE,DATETIMEが主に使われる。自分が扱うデータについてまず中心に学んでおけば同じような関数は使いこなせる。

なお社内、せめてプロダクトレベルではどれを使うかは統一しておいた方がよく、されていないのであれば統一することを提案しておきたい（そうしないと後で自分が大変になる）。

日付や時間を扱う関数についてみていく前にタイムゾーンについても説明しておく。

タイムゾーンについて

DATEやDATETIMEにはタイムゾーンがあり、扱うデータが日本時間なのか標準時間なのか（あるいはその他）を確認してから使うこと。時差は9時間あり、日本時間から9時間を引くと標準時間。逆に言うと標準時間に9時間を足すと日本時間。

関数で指定する場合は日本時間は 'Asia/Tokyo’ を指定。タイムスタンプは標準時間のみ。

それではここから関数と合わせて使い方を解説していく。

現在時刻 CURRENT

現在時刻は CURRENT_DATE / CURRENT_DATETIME / CURRENT_TIMESTAMP 。例は日本時間で2020年1月1日の6:00ちょうどに使ったらどのような結果になるかの一覧。なお実際にはミリ秒が入るが今回は除外している。

書き方 結果 説明
CURRENT_DATE() 2019-12-31 9時間引くと標準時間ではまだ前日の21時
CURRENT_DATE('Asia/Tokyo’) 2020-01-01 日本時間なのでその日
CURRENT_DATETIME() 2019-12-31T21:00:00 前日の21時
CURRENT_DATETIME ('Asia/Tokyo’) 2020-01-01T06:00:00 日本時間なのでその日時
CURRENT_TIMESTAMP() 2020-12-31 21:00:00 UTC 前日の21時
CURRENT_TIMESTAMP('Asia/Tokyo’) エラー

〇日後や〇時間後（ADD）

DATE_ADD/DATETIME_ADD/TIMESTAMP_ADDは指定した単位の日時を加える。それぞれ指定できる範囲が違う。マイナスの数値も指定できてその場合は〇日前、〇時間後になる。次のSUBも参考のこと。

DATE_ADD

DATE_ADDはDAY、WEEK、MONTH、QUARTER、YEARが使える。

DATE_ADD(DATE '2020-01-01’ , INTERVAL 3 DAY) #3日後
DATE_ADD(DATE '2020-01-01’ , INTERVAL 3 YEAR) #3年後

TIMESTAMP_ADD

TIMESTAMP_ADDはMICROSECOND、MILLISECOND、SECOND、MINUTE、HOUR が使える。

TIMESTAMP_ADD(TIMESTAMP '2020-01-01 00:00:00’ , INTERVAL 3 MINUTE) #3分後
TIMESTAMP_ADD(TIMESTAMP '2008-12-25 00:00:00’ , INTERVAL 3 HOUR) #3時間後

DATETIME_ADD

DATETIME_ADDはMICROSECOND、MILLISECOND、SECOND、MINUTE、HOUR 、DAY、WEEK、MONTH、QUARTER、YEAR（つまりDATEとTIMESTAMPの両方）が使える。

DATETIME_ADD(DATETIME '2020-01-01 00:00:00’ , INTERVAL 3 MINUTE) #3分後
DATETIME_ADD(DATETIME '2020-01-01 00:00:00’ , INTERVAL 3 DAY) #3日後

〇日前や〇時間前（ADD）

ADDの変わりにSUBにすると時間を減らす。ADDでマイナスの値を書いたのと同じ。

日付や時間を取り出す、切り詰める（TRUNC）

DATE_TRUNC/DATETIME_TRUNC/TIMESTAMP_TRUNCは指定した粒度で取り出す、あるいは切り詰める。

DATETIME_TRUNC/TIMESTAMP_TRUNCはMICROSECONDからYEARまで、DATE_TRUNCはDAYからYEARまで。

DATE_TRUNC (DATE '2020-01-20’ , MONTH) #その月の1日
DATETIME_TRUNC (DATETIME '2020-01-20 01:23:45’ , DAY) #その日の00:00:00
TIMESTAMP_TRUNC (TIMESTAMP '2020-01-20 01:23:45’, MINUTE) #その分の00秒

月や週単位で集計するために切り詰めたり、ADDやSUBと組み合わせることで当月の月末や翌月の月初、翌週の最初の日なども作れる。

WEEKは週の始めを何曜日にするかを指定できる。

DATETIME_TRUNC (DATETIME '2020-01-20 01:23:45’ , WEEK) #指定が無ければ日曜日で19日
DATETIME_TRUNC (DATETIME '2020-01-20 01:23:45’ , WEEK(MONDAY)) #20日になる

２つの日付の差（DIFF）

DATE_DIFF/DATETIME_DIFF/TIMESTAMP_DIFFは２つの日付の差を返す。

DATE_DIFF(日付1, 日付2 , 単位)
DATETIME_DIFF (日付1, 日付2 , 単位)
TIMESTAMP_DIFF (日付1, 日付2 , 単位)

日付２から日付１までどれぐらい（日付１－日付２）を取るので、日付２の方が日付１よりも後ならば結果はマイナスになる。

使える単位はADDと同様。DATE_DIFF DAY、WEEK、MONTH、QUARTER、YEARが使える。TIMESTAMP_DIFFはMICROSECOND、MILLISECOND、SECOND、MINUTE、HOURが使える。DATETIMEは両方使える。

書き方 結果 説明
DATE_DIFF ( DATE '2020-01-21', DATE '2020-01-20' , DAY) 1 日単位で1日
DATE_DIFF ( DATE '2020-01-20', DATE '2020-01-21' , DAY) -1 21より1日前
DATE_DIFF ( DATE '2020-01-20', DATE '2020-01-21' , MONTH) 0 月単位では0（同月）
DATETIME_DIFF ( DATETIME '2020-01-21 00:00:01', DATETIME '2020-01-20 23:59:59' , DAY) 1 日付が違うので1

SQL

Posted by 管理人