CODEWITHSUNDEEP
sqlpostgresqldatetimestamp
JarochoEngineer
JarochoEngineer

Reputation: 1787

Get difference between day just when the difference is positive

Using Postgresql, I need to substract two dates and to show the result just when the result of the difference between dates is positive or 0. In order to substract the dates I am using this:

SELECT
EXTRACT(EPOCH FROM ('2019-02-11 17:59:01.953894'::timestamp - '2019-12-09 02:08:16.01493'::timestamp))/60

However, I would need something like this:

SELECT
    DECODE(SIGN(EXTRACT(EPOCH FROM ('2019-02-11 17:59:01.953894'::timestamp - '2019-12-09 02:08:16.01493'::timestamp))/60 as d),-1, None,
                                                                                                                                ,1, d)

Running the previous query, I am getting an error in the following:

ERROR:  syntax error at or near "as"
LINE 2: ...mestamp - '2019-12-09 02:08:16.01493'::timestamp))/60 as df)
                                                                 ^

Do you have an idea how to get over this?

Thanks a lot

Upvotes: 0

Views: 78

Answers (2)

Eugene
Eugene

Reputation: 1533

Try this: 

  SELECT greatest((EXTRACT(epoch from age('2019-02-11', now())) / 86400)::int, 0)

Upvotes: 1

GMB
GMB

Reputation: 222642

You could use GREATEST to turn negative values to 0:

GREATEST(
    EXTRACT(EPOCH FROM (ts1 - ts2))/60,
    0
)

Or, if you want a null result when the difference is negative, you can use a case expression that compares the timestamps before attempting to substract them:

CASE WHEN ts1 > ts2 THEN  EXTRACT(EPOCH FROM (ts1 - ts2))/60 END

Upvotes: 2

Related Questions