各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に変更してみたので、そのサンプルプログラムを共...

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
)

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

)

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;

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;

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

SELECT * FROM EMPLOYEE

1	SELECT * FROM EMPLOYEE

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

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

時・分・秒を切り捨てるには、以下のように、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

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

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN ADD_MONTHS(TRUNC(SYSDATE), -1) AND TRUNC(SYSDATE)

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY BETWEEN ADD_MONTHS(TRUNC(SYSDATE), -1) AND TRUNC(SYSDATE)

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= ADD_MONTHS(TRUNC(SYSDATE), -1) 
AND BIRTHDAY <= TRUNC(SYSDATE)

SELECT * FROM EMPLOYEE

WHERE BIRTHDAY >= ADD_MONTHS(TRUNC(SYSDATE), -1)

AND BIRTHDAY <= TRUNC(SYSDATE)

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < ADD_MONTHS(TRUNC(SYSDATE), -1)

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY < ADD_MONTHS(TRUNC(SYSDATE), -1)

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > TRUNC(SYSDATE)

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY > TRUNC(SYSDATE)

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
)

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

)

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;

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;

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

SELECT * FROM EMPLOYEE

1	SELECT * FROM EMPLOYEE

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

SELECT 
     NOW() AS TODAY
   , DATE_ADD(NOW(), INTERVAL -1 MONTH)  AS ONE_MONTH_AGO

SELECT

NOW() AS TODAY

, DATE_ADD(NOW(), INTERVAL -1 MONTH) AS ONE_MONTH_AGO

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

SELECT 
     CURRENT_DATE() AS TODAY
   , DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)  AS ONE_MONTH_AGO

SELECT

CURRENT_DATE() AS TODAY

, DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH) AS ONE_MONTH_AGO

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH) AND CURRENT_DATE()

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH) AND CURRENT_DATE()

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)
AND BIRTHDAY <= CURRENT_DATE()

SELECT * FROM EMPLOYEE

WHERE BIRTHDAY >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)

AND BIRTHDAY <= CURRENT_DATE()

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY < DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > CURRENT_DATE()

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY > CURRENT_DATE()

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
)

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

)

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;

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;

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

SELECT * FROM EMPLOYEE

1	SELECT * FROM EMPLOYEE

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

SELECT 
     NOW() AS TODAY
   , NOW() - interval '1 month'  AS ONE_MONTH_AGO

SELECT

NOW() AS TODAY

, NOW() - interval '1 month' AS ONE_MONTH_AGO

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

SELECT 
     CURRENT_DATE AS TODAY
   , CURRENT_DATE - interval '1 month'  AS ONE_MONTH_AGO

SELECT

CURRENT_DATE AS TODAY

, CURRENT_DATE - interval '1 month' AS ONE_MONTH_AGO

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN (CURRENT_DATE - interval '1 month') AND CURRENT_DATE

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY BETWEEN (CURRENT_DATE - interval '1 month') AND CURRENT_DATE

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= (CURRENT_DATE - interval '1 month')
AND BIRTHDAY <= CURRENT_DATE

SELECT * FROM EMPLOYEE

WHERE BIRTHDAY >= (CURRENT_DATE - interval '1 month')

AND BIRTHDAY <= CURRENT_DATE

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < (CURRENT_DATE - interval '1 month')

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY < (CURRENT_DATE - interval '1 month')

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > CURRENT_DATE

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY > CURRENT_DATE

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
)

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

)

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);

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);

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

SELECT * FROM dbo.EMPLOYEE

1	SELECT * FROM dbo.EMPLOYEE

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

SELECT 
     GETDATE() AS TODAY
   , DATEADD(month, -1, GETDATE())  AS ONE_MONTH_AGO

SELECT

GETDATE() AS TODAY

, DATEADD(month, -1, GETDATE()) AS ONE_MONTH_AGO

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

SELECT 
     CONVERT(date, GETDATE()) AS TODAY
   , DATEADD(month, -1, CONVERT(date, GETDATE()))  AS ONE_MONTH_AGO

SELECT

CONVERT(date, GETDATE()) AS TODAY

, DATEADD(month, -1, CONVERT(date, GETDATE())) AS ONE_MONTH_AGO

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY BETWEEN DATEADD(month, -1, CONVERT(date, GETDATE())) AND CONVERT(date, GETDATE())

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY BETWEEN DATEADD(month, -1, CONVERT(date, GETDATE())) AND CONVERT(date, GETDATE())

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY >= DATEADD(month, -1, CONVERT(date, GETDATE()))
AND BIRTHDAY <= CONVERT(date, GETDATE())

SELECT * FROM EMPLOYEE

WHERE BIRTHDAY >= DATEADD(month, -1, CONVERT(date, GETDATE()))

AND BIRTHDAY <= CONVERT(date, GETDATE())

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY < DATEADD(month, -1, CONVERT(date, GETDATE()))

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY < DATEADD(month, -1, CONVERT(date, GETDATE()))

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

SELECT * FROM EMPLOYEE
WHERE BIRTHDAY > CONVERT(date, GETDATE())

1 2	SELECT * FROM EMPLOYEE WHERE BIRTHDAY > CONVERT(date, GETDATE())

要点まとめ

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

前提条件

やってみたこと

Oracleの場合の実行結果

MySQLの場合の実行結果

PostgreSQLの場合の実行結果

SQL Serverの場合の実行結果

要点まとめ

各DB上でテーブルのデータをCSVファイルに出力してみた

各DBのUPDATE文でJOIN句とCASE式を使ってみた

sqlplusコマンドで2通りの方法でOracleに接続してみた

各DB上でストアドプログラムを試してみた

Oracle上でソートして上位n件のデータを取得してみた

SQLServer認証のユーザーを作成してみた