かきスタンプ

福岡でフリーランスの物流系のエンジニアやってます。

1月31日の1ヶ月後は何日？　3月31日の1ヶ月前は何日？　各種RDBで確認してみた。

Oracle MySQL PostgreSQL SQL Server

『期限切れとなるのは、登録日より１ヶ月後です』
『１か月前に告知されます』
というのを見た時、「何日だろ？」と思ったのがきっかけ。
　 　
法的に決められたルールや、国際ルールがあるんかな。
パッとググってみた感じ、見つけきれなかったんで、RDBに聞いてみた。
　
試したのは、以下。

全 RDBにて、同じ結果となりました。

結果

確認した内容 結果
1/28 の１か月後 2/28
1/29 の１か月後 2/28
1/30 の１か月後 2/28
1/31 の１か月後 2/28
3/28 の１か月前 2/28
3/29 の１か月前 2/28
3/30 の１か月前 2/28
3/31 の１か月前 2/28
1/28 の１か月後（うるう年） 2/28
1/29 の１か月後（うるう年） 2/29
1/30 の１か月後（うるう年） 2/29
1/31 の１か月後（うるう年） 2/29
3/28 の１か月前（うるう年） 2/28
3/29 の１か月前（うるう年） 2/29
3/30 の１か月前（うるう年） 2/29
3/31 の１か月前（うるう年） 2/29

発行したSQL

MySQL

ver：5.7

SELECT
    DATE_ADD(CAST('2018/01/28' AS DATE), INTERVAL 1 MONTH) AS "1/28 の１か月後"
   ,DATE_ADD(CAST('2018/01/29' AS DATE), INTERVAL 1 MONTH) AS "1/29 の１か月後"
   ,DATE_ADD(CAST('2018/01/30' AS DATE), INTERVAL 1 MONTH) AS "1/30 の１か月後"
   ,DATE_ADD(CAST('2018/01/31' AS DATE), INTERVAL 1 MONTH) AS "1/31 の１か月後"

   ,DATE_ADD(CAST('2018/03/28' AS DATE), INTERVAL -1 MONTH) AS "3/28 の１か月前"
   ,DATE_ADD(CAST('2018/03/29' AS DATE), INTERVAL -1 MONTH) AS "3/29 の１か月前"
   ,DATE_ADD(CAST('2018/03/30' AS DATE), INTERVAL -1 MONTH) AS "3/30 の１か月前"
   ,DATE_ADD(CAST('2018/03/31' AS DATE), INTERVAL -1 MONTH) AS "3/31 の１か月前"

   ,DATE_ADD(CAST('2020/01/28' AS DATE), INTERVAL 1 MONTH) AS "2/28 の１か月後（うるう年）"
   ,DATE_ADD(CAST('2020/01/29' AS DATE), INTERVAL 1 MONTH) AS "2/29 の１か月後（うるう年）"
   ,DATE_ADD(CAST('2020/01/30' AS DATE), INTERVAL 1 MONTH) AS "2/30 の１か月後（うるう年）"
   ,DATE_ADD(CAST('2020/01/31' AS DATE), INTERVAL 1 MONTH) AS "2/31 の１か月後（うるう年）"

   ,DATE_ADD(CAST('2020/03/28' AS DATE), INTERVAL -1 MONTH) AS "3/28 の１か月前（うるう年）"
   ,DATE_ADD(CAST('2020/03/29' AS DATE), INTERVAL -1 MONTH) AS "3/29 の１か月前（うるう年）"
   ,DATE_ADD(CAST('2020/03/30' AS DATE), INTERVAL -1 MONTH) AS "3/30 の１か月前（うるう年）"
   ,DATE_ADD(CAST('2020/03/31' AS DATE), INTERVAL -1 MONTH) AS "3/31 の１か月前（うるう年）"

PostgreSQL

ver：9.6

SELECT
    CAST('2018/01/28' AS DATE) + interval '1 months' AS "1/28 の１か月後"
   ,CAST('2018/01/29' AS DATE) + interval '1 months' AS "1/29 の１か月後"
   ,CAST('2018/01/30' AS DATE) + interval '1 months' AS "1/30 の１か月後"
   ,CAST('2018/01/31' AS DATE) + interval '1 months' AS "1/31 の１か月後"
   
   ,CAST('2018/03/28' AS DATE) + interval '-1 months' AS "3/28 の１か月前"
   ,CAST('2018/03/29' AS DATE) + interval '-1 months' AS "3/29 の１か月前"
   ,CAST('2018/03/30' AS DATE) + interval '-1 months' AS "3/30 の１か月前"
   ,CAST('2018/03/31' AS DATE) + interval '-1 months' AS "3/31 の１か月前"   
   
   ,CAST('2020/01/28' AS DATE) + interval '1 months' AS "1/28 の１か月後（うるう年）"
   ,CAST('2020/01/29' AS DATE) + interval '1 months' AS "1/29 の１か月後（うるう年）"
   ,CAST('2020/01/30' AS DATE) + interval '1 months' AS "1/30 の１か月後（うるう年）"
   ,CAST('2020/01/31' AS DATE) + interval '1 months' AS "1/31 の１か月後（うるう年）"
   
   ,CAST('2020/03/28' AS DATE) + interval '-1 months' AS "3/28 の１か月前（うるう年）"
   ,CAST('2020/03/29' AS DATE) + interval '-1 months' AS "3/29 の１か月前（うるう年）"
   ,CAST('2020/03/30' AS DATE) + interval '-1 months' AS "3/30 の１か月前（うるう年）"
   ,CAST('2020/03/31' AS DATE) + interval '-1 months' AS "3/31 の１か月前（うるう年）"

SQL Server

ver：14.0

SELECT
    DATEADD(MONTH, 1, CAST('2018/01/28' AS DATE)) AS "1/28 の１か月後"
   ,DATEADD(MONTH, 1, CAST('2018/01/29' AS DATE)) AS "1/29 の１か月後"
   ,DATEADD(MONTH, 1, CAST('2018/01/30' AS DATE)) AS "1/30 の１か月後"
   ,DATEADD(MONTH, 1, CAST('2018/01/31' AS DATE)) AS "1/31 の１か月後"

   ,DATEADD(MONTH, -1, CAST('2018/03/28' AS DATE)) AS "3/28 の１か月前"
   ,DATEADD(MONTH, -1, CAST('2018/03/29' AS DATE)) AS "3/29 の１か月前"
   ,DATEADD(MONTH, -1, CAST('2018/03/30' AS DATE)) AS "3/30 の１か月前"
   ,DATEADD(MONTH, -1, CAST('2018/03/31' AS DATE)) AS "3/31 の１か月前"

   ,DATEADD(MONTH, 1, CAST('2020/01/28' AS DATE)) AS "1/28 の１か月後（うるう年）"
   ,DATEADD(MONTH, 1, CAST('2020/01/29' AS DATE)) AS "1/29 の１か月後（うるう年）"
   ,DATEADD(MONTH, 1, CAST('2020/01/30' AS DATE)) AS "1/30 の１か月後（うるう年）"
   ,DATEADD(MONTH, 1, CAST('2020/01/31' AS DATE)) AS "1/31 の１か月後（うるう年）"

   ,DATEADD(MONTH, -1, CAST('2020/03/28' AS DATE)) AS "3/28 の１か月前（うるう年）"
   ,DATEADD(MONTH, -1, CAST('2020/03/29' AS DATE)) AS "3/29 の１か月前（うるう年）"
   ,DATEADD(MONTH, -1, CAST('2020/03/30' AS DATE)) AS "3/30 の１か月前（うるう年）"
   ,DATEADD(MONTH, -1, CAST('2020/03/31' AS DATE)) AS "3/31 の１か月前（うるう年）"

Oracle

ver：11g

SELECT
    ADD_MONTHS(TO_DATE('2018/01/28'), 1) AS "1/28 の１か月後"
   ,ADD_MONTHS(TO_DATE('2018/01/29'), 1) AS "1/29 の１か月後"
   ,ADD_MONTHS(TO_DATE('2018/01/30'), 1) AS "1/30 の１か月後"
   ,ADD_MONTHS(TO_DATE('2018/01/31'), 1) AS "1/31 の１か月後"
    
   ,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "3/28 の１か月前"
   ,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "3/29 の１か月前"
   ,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "3/30 の１か月前"
   ,ADD_MONTHS(TO_DATE('2018/03/31'), -1) AS "3/31 の１か月前"
   
   ,ADD_MONTHS(TO_DATE('2020/01/28'), 1) AS "1/28 の１か月後(閏年)"
   ,ADD_MONTHS(TO_DATE('2020/01/29'), 1) AS "1/29 の１か月後(閏年)"
   ,ADD_MONTHS(TO_DATE('2020/01/30'), 1) AS "1/30 の１か月後(閏年)"
   ,ADD_MONTHS(TO_DATE('2020/01/31'), 1) AS "1/31 の１か月後(閏年)"
    
   ,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "3/28 の１か月前(閏年)"
   ,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "3/29 の１か月前(閏年)"
   ,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "3/30 の１か月前(閏年)"
   ,ADD_MONTHS(TO_DATE('2020/03/31'), -1) AS "3/31 の１か月前(閏年)"
FROM 
    DUAL