Amazon Redshift date calculation Issue/Bug

Question

Experts can you advise why redshift is doing this wrong calculation. If I backtrack and go to previous day before 1nov17, I should get the output as 31-oct but it is returning 30-oct. Is it a bug ??

select
    dateadd(month,
            -6,
            dateadd(day, -2, date('20180503'))
           ) AS "this is 1nov17",
    dateadd(month,
            -6,
            dateadd(day, -3, date('20180503'))
           ) AS "should be 31oct17"

The output is:

 this is 1nov17          should be 31oct17 
 2017-11-01 00:00:00.0   2017-10-30 00:00:00.0

John Rotenstein John Rotenstein · Accepted Answer · 2018-05-03T06:16:25

It is behaving perfectly correctly.

This:

dateadd(month,
            -6,
            dateadd(day, -3, date('20180503'))
           ) AS "should be 31oct17"

is saying: "Give me the date which is 6 months before the date that is 3 days before 2018-05-03".

The date that is 3 days before 2018-05-03 is 2018-04-30

The date that is 6 months before 2018-04-30 is 2017-10-30

If, instead, you reversed the two operations:

The date that is 6 months before 2018-05-03 is 2018-11-03
The date that is 3 days before 2018-11-03 is 2018-10-31 (one day later)

So, the problem is caused by some months having 30 days and other months having 31 days.

It is possible that you are wanting "the last day of the month". This is always difficult and confusing for us humans and is best avoided. If you really need it, then it is easiest to obtain by subtracting a day from the first day of the next month:

SELECT
  dt,
  (dt + '1 month'::interval - '1 day'::interval)::date
FROM (SELECT ('2018-01-01'::date + '1 month'::interval * generate_series(0,11))::date as dt)

returns:

2018-01-01  2018-01-31
2018-02-01  2018-02-28
2018-03-01  2018-03-31
2018-04-01  2018-04-30
2018-05-01  2018-05-31
2018-06-01  2018-06-30
2018-07-01  2018-07-31
2018-08-01  2018-08-31
2018-09-01  2018-09-30
2018-10-01  2018-10-31
2018-11-01  2018-11-30
2018-12-01  2018-12-31

Amazon Redshift date calculation Issue/Bug

1 Answers