postgresqlintervals

Is there a way to filter out negative Interval values?

Currently I have a interval column after subtracting two timestamp without time zone columns

SELECT started_at - ended_at AS ride_length
FROM [dbo]

However, I am getting negative values for the intervals and I want to filter them out.

I tried:

SELECT *
FROM [dbo]
WHERE ride_length < 0

and it returns this error

HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

But I cannot change the datatype of the column.

Solution

  • Compare with an interval:

    SELECT ended_at - started_at AS ride_length
FROM [dbo]
WHERE ended_at - started_at < INTERVAL '0 SECOND'

    Note also the reversal of the columns: duration is end minus start.