DB

各DB上で特定の日付とシステム日付の1ヶ月前の日付を比較してみた

 

DBから特定の日付がシステム日付以降(1ヶ月以内)のデータを取得しようとした時に、日付の大小関係が分からなくなってしまったことがあった。

今回は、各DB上で「生年月日」を確認し、「生まれてから1ヶ月以内のデータ」「生まれてから1ヶ月を超えたデータ」「まだ生まれていない人のデータ」を取得してみたので、そのSQLを取得する。

前提条件

Oracle XEのインストールが完了していること。
また、下記記事に記載されている、MySQLとPostgreSQLでのユーザー作成が完了していること。

MySQLでユーザーを作成しテーブルを追加してみたMySQLの場合は、Workbenchを利用すると、スキーマ(データベース)やユーザーの作成をGUIベースで実施できる。今回は、Work...
Postgresqlでユーザーを作成しテーブルを追加してみたPostgresqlの場合は、pgAdminを利用すると、ユーザーやデータベースの作成をGUIベースで実施できる。今回は、pgAdmin...

さらに、下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

やってみたこと

  1. Oracleの場合の実行結果
  2. MySQLの場合の実行結果
  3. PostgreSQLの場合の実行結果
  4. SQL Serverの場合の実行結果

Oracleの場合の実行結果

Oracleの場合の実行結果は、以下の通り。

1) EMPLOYEEテーブルを作成する。

CREATE TABLE EMPLOYEE (
   ID INTEGER NOT NULL PRIMARY KEY,
   NAME VARCHAR2(60) NOT NULL,
   BIRTHDAY DATE NOT NULL,
   BOSS_ID INTEGER,
   FOREIGN KEY (BOSS_ID) REFERENCES EMPLOYEE (ID) ON DELETE SET NULL
)
Oracle実行結果_1

2) EMPLOYEEテーブルのデータを初期化する。

TRUNCATE TABLE EMPLOYEE;
INSERT INTO EMPLOYEE VALUES (1, 'テスト　プリン１', '2021-02-10', null);
INSERT INTO EMPLOYEE VALUES (2, 'テスト　プリン２', '2021-03-22', null);
INSERT INTO EMPLOYEE VALUES (3, 'テスト　プリン３', '2021-04-05', null);
INSERT INTO EMPLOYEE VALUES (4, 'テスト　プリン４', '2021-04-30', null);
COMMIT;
Oracle実行結果_2_1

実行後のデータの内容は、以下の通り。

SELECT * FROM EMPLOYEE
Oracle実行結果_2_2

3) 本日と、1ヶ月前の日付を取得する。これは、2021年4月18日に実行した結果となる。

SELECT 
    TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS TODAY
  , TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY/MM/DD HH24:MI:SS') AS ONE_MONTH_AGO
FROM DUAL
Oracle実行結果_3_1

時・分・秒を切り捨てるには、以下のように、TRUNC関数を利用する。

SELECT 
    TO_CHAR(TRUNC(SYSDATE), 'YYYY/MM/DD HH24:MI:SS') AS TODAY
  , TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1), 'YYYY/MM/DD HH24:MI:SS') AS ONE_MONTH_AGO
FROM DUAL
Oracle実行結果_3_2

4) 生年月日をみて、生まれてから1ヶ月以内のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN ADD_MONTHS(TRUNC(SYSDATE), -1) AND TRUNC(SYSDATE)
Oracle実行結果_4_1

また、同じデータをBETWEEN句を使わないで取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= ADD_MONTHS(TRUNC(SYSDATE), -1) 
AND BIRTHDAY <= TRUNC(SYSDATE)
Oracle実行結果_4_2

5) 生年月日をみて、生まれてから1ヶ月を超えたデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < ADD_MONTHS(TRUNC(SYSDATE), -1)
Oracle実行結果_5

6) 生年月日をみて、まだ生まれていない人のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > TRUNC(SYSDATE)
Oracle実行結果_6



MySQLの場合の実行結果

MySQLの場合の実行結果は、以下の通り。

1) EMPLOYEEテーブルを作成する。

CREATE TABLE EMPLOYEE (
   ID INT NOT NULL PRIMARY KEY,
   NAME VARCHAR(60) NOT NULL,
   BIRTHDAY DATE NOT NULL,
   BOSS_ID INT,
   FOREIGN KEY (BOSS_ID) REFERENCES EMPLOYEE (ID) ON DELETE SET NULL
)
MySQLの実行結果_1

2) EMPLOYEEテーブルのデータを初期化する。

TRUNCATE TABLE EMPLOYEE;
INSERT INTO EMPLOYEE VALUES (1, 'テスト　プリン１', '2021-02-10', null);
INSERT INTO EMPLOYEE VALUES (2, 'テスト　プリン２', '2021-03-22', null);
INSERT INTO EMPLOYEE VALUES (3, 'テスト　プリン３', '2021-04-05', null);
INSERT INTO EMPLOYEE VALUES (4, 'テスト　プリン４', '2021-04-30', null);
COMMIT;
MySQLの実行結果_2_1

実行後のデータの内容は、以下の通り。

SELECT * FROM EMPLOYEE
MySQLの実行結果_2_2

3) 本日と、1ヶ月前の日付を取得する。これは、2021年4月18日に実行した結果となる。

SELECT 
     NOW() AS TODAY
   , DATE_ADD(NOW(), INTERVAL -1 MONTH)  AS ONE_MONTH_AGO
MySQLの実行結果_3_1

時・分・秒を切り捨てるには、以下のように、CURRENT_DATE関数を利用する。

SELECT 
     CURRENT_DATE() AS TODAY
   , DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)  AS ONE_MONTH_AGO
MySQLの実行結果_3_2

4) 生年月日をみて、生まれてから1ヶ月以内のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH) AND CURRENT_DATE()
MySQLの実行結果_4_1

また、同じデータをBETWEEN句を使わないで取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)
AND BIRTHDAY <= CURRENT_DATE()
MySQLの実行結果_4_2

5) 生年月日をみて、生まれてから1ヶ月を超えたデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)
MySQLの実行結果_5

6) 生年月日をみて、まだ生まれていない人のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > CURRENT_DATE()
MySQLの実行結果_6



PostgreSQLの場合の実行結果

PostgreSQLの場合の実行結果は、以下の通り。

1) EMPLOYEEテーブルを作成する。

CREATE TABLE EMPLOYEE (
   ID INTEGER NOT NULL PRIMARY KEY,
   NAME VARCHAR(60) NOT NULL,
   BIRTHDAY DATE NOT NULL,
   BOSS_ID INTEGER,
   FOREIGN KEY (BOSS_ID) REFERENCES EMPLOYEE (ID) ON DELETE SET NULL
)
PostgreSQL実行結果_1

2) EMPLOYEEテーブルのデータを初期化する。

TRUNCATE TABLE EMPLOYEE;
INSERT INTO EMPLOYEE VALUES (1, 'テスト　プリン１', '2021-02-10', null);
INSERT INTO EMPLOYEE VALUES (2, 'テスト　プリン２', '2021-03-22', null);
INSERT INTO EMPLOYEE VALUES (3, 'テスト　プリン３', '2021-04-05', null);
INSERT INTO EMPLOYEE VALUES (4, 'テスト　プリン４', '2021-04-30', null);
COMMIT;
PostgreSQL実行結果_2_1

実行後のデータの内容は、以下の通り。

SELECT * FROM EMPLOYEE
PostgreSQL実行結果_2_2

3) 本日と、1ヶ月前の日付を取得する。これは、2021年4月18日に実行した結果となる。

SELECT 
     NOW() AS TODAY
   , NOW() - interval '1 month'  AS ONE_MONTH_AGO
PostgreSQL実行結果_3_1

時・分・秒を切り捨てるには、以下のように、CURRENT_DATE関数を利用する。

SELECT 
     CURRENT_DATE AS TODAY
   , CURRENT_DATE - interval '1 month'  AS ONE_MONTH_AGO
PostgreSQL実行結果_3_2

4) 生年月日をみて、生まれてから1ヶ月以内のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN (CURRENT_DATE - interval '1 month') AND CURRENT_DATE
PostgreSQL実行結果_4_1

また、同じデータをBETWEEN句を使わないで取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= (CURRENT_DATE - interval '1 month')
AND BIRTHDAY <= CURRENT_DATE
PostgreSQL実行結果_4_2

5) 生年月日をみて、生まれてから1ヶ月を超えたデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < (CURRENT_DATE - interval '1 month')
PostgreSQL実行結果_5

6) 生年月日をみて、まだ生まれていない人のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > CURRENT_DATE
PostgreSQL実行結果_6



SQL Serverの場合の実行結果

SQLServerの場合の実行結果は、以下の通り。

1) EMPLOYEEテーブルを作成する。なお、制約は「ON DELETE SET NULL」だとエラーになるため、「ON DELETE NO ACTION」を指定している。

CREATE TABLE EMPLOYEE (
   ID INT NOT NULL PRIMARY KEY,
   NAME NVARCHAR(60) NOT NULL,
   BIRTHDAY DATE NOT NULL,
   BOSS_ID INT,
   FOREIGN KEY (BOSS_ID) REFERENCES EMPLOYEE (ID) ON DELETE NO ACTION
)
SQLServer実行結果_1

2) EMPLOYEEテーブルのデータを初期化する。

TRUNCATE TABLE dbo.EMPLOYEE;
INSERT INTO dbo.EMPLOYEE VALUES (1, 'テスト　プリン１', '2021-02-10', null);
INSERT INTO dbo.EMPLOYEE VALUES (2, 'テスト　プリン２', '2021-03-22', null);
INSERT INTO dbo.EMPLOYEE VALUES (3, 'テスト　プリン３', '2021-04-05', null);
INSERT INTO dbo.EMPLOYEE VALUES (4, 'テスト　プリン４', '2021-04-30', null);
SQLServer実行結果_2_1

実行後のデータの内容は、以下の通り。

SELECT * FROM dbo.EMPLOYEE
SQLServer実行結果_2_2

3) 本日と、1ヶ月前の日付を取得する。これは、2021年4月18日に実行した結果となる。

SELECT 
     GETDATE() AS TODAY
   , DATEADD(month, -1, GETDATE())  AS ONE_MONTH_AGO
SQLServer実行結果_3_1

時・分・秒を切り捨てるには、以下のように、CONVERT関数を利用する。

SELECT 
     CONVERT(date, GETDATE()) AS TODAY
   , DATEADD(month, -1, CONVERT(date, GETDATE()))  AS ONE_MONTH_AGO
SQLServer実行結果_3_2

4) 生年月日をみて、生まれてから1ヶ月以内のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN DATEADD(month, -1, CONVERT(date, GETDATE())) AND CONVERT(date, GETDATE())
SQLServer実行結果_4_1

また、同じデータをBETWEEN句を使わないで取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= DATEADD(month, -1, CONVERT(date, GETDATE()))
AND BIRTHDAY <= CONVERT(date, GETDATE())
SQLServer実行結果_4_2

5) 生年月日をみて、生まれてから1ヶ月を超えたデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < DATEADD(month, -1, CONVERT(date, GETDATE()))
SQLServer実行結果_5

6) 生年月日をみて、まだ生まれていない人のデータを取得するSQLは、以下の通り。

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > CONVERT(date, GETDATE())
SQLServer実行結果_6

要点まとめ

  • 各DB上で「生年月日」を確認し、「生まれてから1ヶ月以内のデータ」を取得するには、「生年月日」がシステム日付の1ヶ月前～システム日付のデータを取得すればよい。
RELATED POST