PostgreSQLでの日付の操作

現在の日付を取得する

PostgreSQLでは、組み込みのNOW()関数を使用して現在の日付と時刻を取得できます。次のステートメントは、日付と時刻の両方を返します。

SELECT NOW();

              now
-------------------------------
 2021-09-27 15:22:53.679985+02
(1 row)

時刻が不要な場合は、ダブルコロン::を使用してDATETIME値をDATE値にキャストすることで、日付のみを返すように指定することもできます。

SELECT NOW()::date;

    now
------------
 2021-09-27
(1 row)

CURRENT_DATEを使用することも、以下に示すように現在の日付を取得する別の方法です。

SELECT CURRENT_DATE;

 current_date
--------------
 2021-09-27
(1 row)

これらの3つのオプションはすべて、日付をyyyy-mm-dd形式で返します。PostgreSQL内では、必要に応じてこの出力の形式を調整できます。

特定の形式で日付値を出力する

特定の形式で日付値を出力するには、TO_CHAR()関数を使用します。この関数は2つのパラメータを受け入れます。

1. フォーマットする値
2. 出力形式を定義するテンプレート

SELECT TO_CHAR(NOW()::date, 'dd/mm/yyyy');

  to_char
------------
 27/09/2021
(1 row)

Sep 27, 2021のような形式で日付を表示することもできます。

SELECT TO_CHAR(NOW():: DATE, 'Mon dd, yyyy');

   to_char
--------------
 Sep 27, 2021
(1 row)

システムの要件によっては、特定の方法でフォーマットされた日付が必要になる場合があります。これは、PostgreSQLで出力を指定できることが役立つシナリオです。

2つの日付間の間隔を取得する

PostgreSQLでは、間隔を-演算子を使用して2つの日付間で取得できます。この演算子を使用すると、従業員の在職期間や本の出版からの時間などを計算できます。

この例では、ジョイスのユリシーズが出版されてから何日経過したかを、現在の日付からpublished_dateを引くことで求めます。

SELECT
                author_name,
                book_title,
                now()::date - published_date as diff
FROM
                checkouts;

結果

 author_name | book_title |            diff
-------------+------------+----------------------------
 James Joyce |  Ulysses   |         36397 days
(1 row)

日付値を使用した年齢の計算

同じ例を続けて、AGE()関数を使用して、現在の日付での年齢を年、月、日で計算できます。次のステートメントは、AGE()関数を使用して、ライブラリのcheckoutsテーブルから出版物の年齢を計算します。

SELECT
                author_name,
                book_title,
                AGE(published_date)
FROM
                checkouts;

この関数を使用すると、在庫内の本の古さを計算できます。

 author_name | book_title |           age
-------------+------------+-------------------------
 James Joyce |  Ulysses   | 99 years 7 mons 25 days
(1 row)

AGE()関数に単一の日付を渡すと、現在の日付を自動的に使用して減算および計算することに注意することが重要です。年齢を計算するために、2つの日付を関数に渡すこともできます。

SELECT
                author_name,
                book_title,
                AGE('2000-01-01',published_date),
FROM
                checkouts;

結果

 author_name | book_title |           age
-------------+------------+--------------------------
 James Joyce |  Ulysses   | 77 years 10 mons 27 days
(1 row)

日付値から年、四半期、月、週、または日を抽出する

最後に説明する関数は、PostgreSQLのEXTRACT()関数です。これにより、年、四半期、月、日などの日付のコンポーネントを分離できます。

次のステートメントは、ユリシーズの出版日から年、月、日を抽出します。

SELECT
            author_name,
            book_title,
            EXTRACT(YEAR FROM published_date) AS YEAR,
            EXTRACT(MONTH FROM published_date) AS MONTH,
            EXTRACT(DAY FROM published_date) AS DAY
FROM
            checkouts;

結果は次のようになります。

 author_name | book_title | year | month | day
-------------+------------+------+-------+-----
 James Joyce |  Ulysses   | 1922 |   2   |  2
(1 row)

これは、たとえば、データを使用した計算に日付値の一部のみが必要な場合に知っておくと便利な関数です。