Data

new_gist_file

-- 現在を秒数で取得
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP);

-- 日時の文字列から秒数に変換
SELECT EXTRACT(EPOCH FROM CAST('2007-08-25 13:15:30' AS TIMESTAMP));

-- 日の文字列から秒数に変換
SELECT EXTRACT(EPOCH FROM CAST('2007-08-01' AS DATE));

-- 7日後を求める
SELECT CAST('2008-04-25' AS DATE) + CAST('7 days' AS INTERVAL);

-- 14日前を求める
SELECT CAST('2008-04-25' AS DATE) - CAST('14 days' AS INTERVAL);

--4月の末日ならば5月1日から1日を引く
SELECT CAST('2008-05-01' AS DATE) - CAST('1 day' AS INTERVAL);

-- 2ヶ月後を求める
SELECT CAST('2008-07-31' AS DATE) + CAST('2 months' AS INTERVAL);

-- うるう年の1月31日の1ヶ月後を求める
SELECT CAST('2008-01-31' AS DATE) + CAST('1 month' AS INTERVAL);

--日付の比較を行う
SELECT true WHERE CAST('2008-04-30' AS DATE) > CAST('2008-01-31' AS DATE);
SELECT true WHERE CAST('2008-04-10' AS DATE) > CAST('2008-04-20' AS DATE);

--2つの日付の差（何日間あるか）を求める
SELECT CAST('2008-04-30' AS DATE) - CAST('2008-01-31' AS DATE);
SELECT CAST('2008-04-10' AS DATE) - CAST('2008-04-20' AS DATE);

--任意の日付の曜日を取得
SELECT (ARRAY['日','月','火','水','木','金','土'])[EXTRACT(DOW FROM CAST('2008-04-21' AS DATE)) + 1];

--当月の月初日を取得
SELECT DATE_TRUNC('month', now());

--当月の月末日を取得
SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';