Postgresql timestamp and NULL difference

Question

I'm novice in PostgreSQL. I have a column timestamp without time zone named FailureTime. At the beginning it has NULL value.

When SELECT I need to get all entries that has current_timestamp - FailureTime > interval '7'.

But when FailureTime is NULL the result of operation is NULL. Can I cast NULL to ZERO value or somehow get just current_timestamp as result of the operation?

Why don't you add FailureTime IS NOT NULL in your "WHERE" clause ? — gkalpak
– gkalpak, Commented May 23, 2013 at 17:38

Clodoaldo Neto · Accepted Answer · 2013-05-23 17:41:35Z

2

current_timestamp - coalesce(FailureTime, current_timestamp) > interval '7'

answered May 23, 2013 at 17:41

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

gkalpak · Accepted Answer · 2013-05-23 17:41:03Z

1

You can exclude NULL elements by specifying something like:

SELECT ... 
WHERE (FailureTime IS NOT NULL) 
AND (current_timestamp - FailureTime > interval '7')

edited May 23, 2013 at 17:41

gkalpak

48.2k8 gold badges108 silver badges120 bronze badges

answered May 23, 2013 at 17:38

Steve

7,2712 gold badges33 silver badges54 bronze badges

Collectives™ on Stack Overflow

Postgresql timestamp and NULL difference

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related