Geek Answers Handbook

Subtracting two dates using PostgreSQL

I am trying to subtract two dates from each other, but it seems that it is not subtracted correctly, and I'm not sure what I am doing wrong here. I use case register to mark as 1 if the difference between dates is less than 90 days, otherwise mark it as 0. But it is always marked as 1, even if the difference between dates exceeds 90 days. I am PostgreSQL here, and here is my case argument:

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

Date example:

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

in this case it should be indicated as 0, but it is marked as 1, because the difference between the two dates is more than 90 days.

+4
source share
4 answers

extract . 1 31, 30 90.

date . extract:

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

:

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

, 90

+1

A bit shorter version of CAST.

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

We can also do this without using any EXTRACT or CAST keyword, for example:

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

More articles:


All Articles