interval, timestamp, date, time の違い
interval
timestamp, date, time の時間を変動させる時に利用する関数
以下の sql と結果を参照
(RDBMS ごとに文法が違うので注意)
timestamp
日付と時刻
SELECT (timestamp'2014-10-17 22:00:00') AS timestamp_col; timestamp_col --------------------- 2014-10-17 22:00:00
SELECT ( timestamp'2014-10-17 22:00:00' + (INTERVAL '2 DAYS') ) AS timestamp_col; timestamp_col --------------------- 2014-10-19 22:00:00
date
時刻を含めない日付
SELECT (date'2014-10-17') AS date_col; date_col ------------ 2014-10-17
SELECT ( date'2014-10-17' - (INTERVAL '1 DAY') ) AS date_col; date_col ------------ 2014-10-16 00:00:00
date - interval
は timestamp
型になるので要注意
time
日付を含めない時刻
SELECT (time'00:00:00') AS time_col; time_col ---------- 00:00:00
SELECT ( time'00:00:00' - (INTERVAL '1 MIN') ) AS time_col; time_col ---------- 23:59:00
PostgreSQL: Documentation: 9.4: Date/Time Types
PostgreSQL: Documentation: 9.4: Date/Time Functions and Operators
Working with Dates and Times in PostgreSQL - PostgreSQL wiki