PostgreSQLによる日付・時刻データの足し算・引き算

２つの日付・時刻データの差分や、日付・時刻データの加算方法を紹介します。
会員登録時からの経過日数や、登録された生年月日から現時点の年齢を計算するなどに使えます。

↓より詳しく知りたい方はこちらを参照してください。

ビッグデータ分析・活用のためのSQLレシピ

マイナビ出版

Amazonの商品レビュー・口コミを見る

Amazon

楽天

会員登録日時と生年月日のテストデータテーブルの作成
日付・時刻データの差分や、日付・時刻データの加算方法
会員登録から現在までの日数を計算
PostgresSQLで生年月日からユーザーの現在の年齢を計算する
1. 日付を整数にして引き算をした結果を１００００で割って整数部のみ取り出すと年齢を計算できる

会員登録日時と生年月日のテストデータテーブルの作成

まずは実験するためのテストデータを作成します。

会員登録日時と生年月日のテーブルを作成します。

DROP TABLE IF EXISTS mst_users_with_dates;

CREATE TABLE mst_users_with_dates (
  userid text,
  register_stamp timestamp,
  birth_date date
);

INSERT INTO
  mst_users_with_dates (userid, register_stamp, birth_date)
VALUES
  ('U0001', '2020-02-28 10:00:00', '1996-02-29'),
  ('U0002', '2020-02-29 10:00:00', '1996-02-29'),
  ('U0003', '2020-03-01 10:00:00', '1996-02-29');

 userid |   register_stamp    | birth_date 
--------+---------------------+------------
 U0001  | 2020-02-28 10:00:00 | 1996-02-29
 U0002  | 2020-02-29 10:00:00 | 1996-02-29
 U0003  | 2020-03-01 10:00:00 | 1996-02-29
(3 rows)

日付・時刻データの差分や、日付・時刻データの加算方法

SELECT
  userid,
  register_stamp :: timestamp AS register_stamp,
  register_stamp :: timestamp + '1 hour' :: interval AS after_1_hour,
  register_stamp :: timestamp - '30 minutes' :: interval AS before_30_minutes,
  register_stamp :: date AS register_date,
  (register_stamp :: date + '1 day' :: interval) :: date AS after_1_day,
  (register_stamp :: date - '1 month' :: interval) :: date AS before_1_month
FROM
  mst_users_with_dates;

PostgreSQLでの日時の操作はかなり直感的に行えるので、すぐに慣れると思います。

 userid |   register_stamp    |    after_1_hour     |  before_30_minutes  | register_date | after_1_day | before_1_month 
--------+---------------------+---------------------+---------------------+---------------+-------------+----------------
 U0001  | 2020-02-28 10:00:00 | 2020-02-28 11:00:00 | 2020-02-28 09:30:00 | 2020-02-28    | 2020-02-29  | 2020-01-28
 U0002  | 2020-02-29 10:00:00 | 2020-02-29 11:00:00 | 2020-02-29 09:30:00 | 2020-02-29    | 2020-03-01  | 2020-01-29
 U0003  | 2020-03-01 10:00:00 | 2020-03-01 11:00:00 | 2020-03-01 09:30:00 | 2020-03-01    | 2020-03-02  | 2020-02-01
(3 rows)

会員登録から現在までの日数を計算

SELECT
  userid,
  CURRENT_DATE AS today,
  register_stamp :: date AS register_date,
  CURRENT_DATE - register_stamp :: date AS diff_days
FROM
  mst_users_with_dates;

CURRENT_DATEで現在の日時が取得できるので、CURRENT_DATE - register_stamp :: dateで差分を求めれば会員登録日(register_stamp)から現在までの日数を取得できる。

 userid |   today    | register_date | diff_days 
--------+------------+---------------+-----------
 U0001  | 2020-05-04 | 2020-02-28    |        66
 U0002  | 2020-05-04 | 2020-02-29    |        65
 U0003  | 2020-05-04 | 2020-03-01    |        64
(3 rows)

PostgresSQLで生年月日からユーザーの現在の年齢を計算する

age関数とEXTRACT関数を使用することで現在の年齢と登録時点での年齢を計算することができます。

SELECT
  userid,
  CURRENT_DATE AS today,
  register_stamp :: date AS register_date,
  birth_date :: date AS birth_date,
  EXTRACT(
    YEAR
    FROM
      age(birth_date :: date)
  ) AS current_age,
  EXTRACT(
    YEAR
    FROM
      age(register_stamp :: date, birth_date :: date)
  ) AS register_age
FROM
  mst_users_with_dates;

 userid |   today    | register_date | birth_date | current_age | register_age 
--------+------------+---------------+------------+-------------+--------------
 U0001  | 2020-05-04 | 2020-02-28    | 1996-02-29 |          24 |           23
 U0002  | 2020-05-04 | 2020-02-29    | 1996-02-29 |          24 |           24
 U0003  | 2020-05-04 | 2020-03-01    | 1996-02-29 |          24 |           24
(3 rows)

日付を整数にして引き算をした結果を１００００で割って整数部のみ取り出すと年齢を計算できる

SELECT
  floor((20200504 -19960229) / 10000) AS age;

 age 
-----
  24
(1 row)

日付を整数で表現して差分をとると年齢を計算できます。

SELECT
  userid,
  substring(CAST(register_stamp AS text), 1, 10) AS register_date,
  birth_date,
  floor(
    (
      CAST(
        replace(
          substring(CAST(register_stamp AS text), 1, 10),
          '-',
          ''
        ) AS integer
      ) - CAST(
        replace(CAST(birth_date AS text), '-', '') AS integer
      )
    ) / 10000
  ) AS register_age,
  floor(
    (
      CAST(
        replace(CAST(CURRENT_DATE AS text), '-', '') AS integer
      ) - CAST(
        replace(CAST(birth_date AS text), '-', '') AS integer
      )
    ) / 10000
  ) AS current_age
FROM
  mst_users_with_dates;

substringで１文字目から１０文字目までを切り出します。

具体的には、2020-02-28 10:00:00 から 2020-02-28 を切り出しています。

replaceで'-'を''で取り替えます（ここでは'-'が取り除かれる結果になる）。

2020-02-28は20200228に変換されます。

適切にCAST（型変換）しないとうまく動きません。

↓より詳しく知りたい方はこちらを参照してください。