Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Data
-- 現在を秒数で取得
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.