# compare dates in results

I'm looking to compare the dates in the result set

Example:

``````SELECT * FROM tbl WHERE foo=bar
``````

Output

``````id, datetime, name
1, 2014-01-01 18:13:45, test 1
2, 2014-01-01 19:01:32, test 2
3, 2014-01-01 20:34:44, test 3
4, 2014-01-03 04:45:22, test 4
``````

I would like to be able to tell between each record if the difference in time is less or more than 24 hours of the previous record.

Example:

``````1, 2014-01-01 18:13:45, test 1 <-- first record since the previous record
2, 2014-01-01 19:01:32, test 2 <-- second record was less than 24 hours since the previous record
3, 2014-01-01 20:34:44, test 3 <-- third record was less than 24 hours since the previous record
4, 2014-01-03 04:45:22, test 4 <-- fourth record was more than 24 hours since the previous record
``````

thoughts?

• try the `LAG()` function. – ypercubeᵀᴹ Mar 17 '14 at 18:44
• What determines the order of rows? id, datetime , name or something else? What if the difference is exactly 24h? – Erwin Brandstetter Mar 19 '14 at 16:47

As said by @ypercube, `lag()` will help.

``````select id,
(d - lag) as lag,
case when (d - lag) < interval '24 hours' then false else true end as p
from (
select id,
(datetime) as d,
lag(datetime) over (order by id) as lag
from lagtest
) as t;
``````

For the first row no lag will be displayed.

• or `over (order by datetime)`. It's not clear which of the two OP wants. – ypercubeᵀᴹ Mar 18 '14 at 9:12

@Fabrizio is basically right, but I would simplify:

``````SELECT id, datetime
lag(datetime) OVER (ORDER BY id) + interval '1d' < datetime AS more_than_a_day
FROM   tbl;
``````

There is no need for a subquery or even a `CASE` statement.