Subtracting two dates using PostgreSQL

Question

I am trying to subtract 2 dates from each other but it seems that it is not subtracting properly and i am not sure what i am doing wrong here. I am using case statement to flag as 1 if the difference between the dates are less than 90 days else flag it as 0. But it is always flagging as 1 even if the difference between the dates are greater than 90 days. I am PostgreSQL here and here is my case statement:

CASE WHEN EXTRACT(DAY FROM CAST(SVS_DT AS DATE) - CAST(DSCH_TS AS DATE)) <90 
      THEN 1 ELSE 0 END AS FU90

example of the dates are here:

SVS_DT                         DSCH_TS
2013-03-22 00:00:00            2010-05-06 00:00:00

it is suppose to flag as 0 in this case but it is flagging as 1 because the difference between these 2 dates are greater than 90 days.

Mureinik · Accepted Answer · 2014-10-18 18:17:25Z

4

extract of a day returns the day element of a date. Since days are always between 1 and 31, the maximum difference is 30, and cannot be larger than 90.

Subtracting dates returns the difference in days, as an integer. So you need only drop the extract calls:

CASE WHEN (CAST(SVS_DT AS DATE) - CAST(DSCH_TS AS DATE)) < 90 THEN 1 
                                                              ELSE 0 
     END AS FU90

answered Oct 18, 2014 at 18:17

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Expurple Over a year ago

I've also bumped into this, expecting date diff to be an interval, same as timestamp diff

Prasad Khode · Accepted Answer · 2014-10-18 18:16:16Z

1

you can use below one:

CASE WHEN (EXTRACT(EPOCH FROM (DATE_COLUMN_2 - DATE_COLUMN_1)) < (90*24*60*60) 
    THEN 1 ELSE 0 END AS FU90

here it returns the deference in seconds so you need to convert 90 days into seconds

answered Oct 18, 2014 at 18:16

Prasad Khode

6,77712 gold badges48 silver badges62 bronze badges

Comments

Tim Duncklee · Accepted Answer · 2017-03-12 20:19:43Z

0

A slightly shorter CAST version.

SELECT CASE WHEN SVS_DT::DATE - DSCH_TS::DATE < 90
    THEN 1
    ELSE 0
END 
AS FU90

answered Mar 12, 2017 at 20:19

Tim Duncklee

1,4201 gold badge24 silver badges36 bronze badges

Comments

Pang · Accepted Answer · 2018-03-01 06:46:38Z

0

We can also do it without using any EXTRACT or CAST keyword like:

SELECT CASE WHEN (DATE(SVS_DT)- DATE(DSCH_TS)) < 90
             THEN 1
       ELSE 0
END AS FU90

edited Mar 1, 2018 at 6:46

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Mar 1, 2018 at 3:47

Rahul Ghadge

1317 bronze badges

Collectives™ on Stack Overflow

Subtracting two dates using PostgreSQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related