0

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
5

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.

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

@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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.