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 - intervaltimestamp 型になるので要注意

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