The difference between dates in two consecutive rows

Question

+----------+--------------+-------------------------+ | ticketid | ticketpostid | date | +----------+--------------+-------------------------+ | 1387935 | 3147808 | 2012-09-17 13:33:01 | | 1387935 | 3147812 | 2012-09-17 13:33:41 | | 1387938 | 3147818 | 2012-09-17 13:35:01 | | 1387938 | 3148068 | 2012-09-17 13:37:01 | | 1387938 | 3148323 | 2012-09-17 14:47:01 | | 1387939 | 3147820 | 2012-09-17 13:36:01 | | 1387939 | 3147834 | 2012-09-17 13:36:25 | | 1387939 | 3147851 | 2012-09-17 13:41:01 | | 1387939 | 3147968 | 2012-09-17 13:59:06 | | 1387939 | 3147996 | 2012-09-17 14:03:01 | 

This is the result of a query that I wrote. There are two or more two lines with the same ticket. I need to find the time difference between the first two dates in each ticket

Ref.

 +----------+--------------+-------------------------+ | ticketid | ticketpostid | date | +----------+--------------+-------------------------+ | 1387935 | 3147808 | 2012-09-17 13:33:01 | | 1387935 | 3147812 | 2012-09-17 13:33:41 | | 1387938 | 3147818 | 2012-09-17 13:35:01 | | 1387938 | 3148068 | 2012-09-17 13:37:01 | | 1387939 | 3147820 | 2012-09-17 13:36:01 | | 1387939 | 3147834 | 2012-09-17 13:36:25 | 

As a result

 +----------+--------------+ | ticketid |time diff(sec)| +----------+--------------+ | 1387935 | 40 | | 1387938 | 120 | | 1387939 | 24 | 

Can you tell me how I can do this?

Thanks.

Answer 1

For PostgreSQL, I think you want the lag window function to compare strings; It will be much more effective than self-connection and filter. This will not work with MySQL as it still does not support the standard SQL: 2003 window functions; see below.

To find only the two lowest, you can use the dense_rank window dense_rank through ticketid , and then filter the results to return only the rows where dense_rank() = 2 , i.e. the row with the second-lowest timestamp, where lag() will create the row with the least timestamp.

See this SQLFiddle for a sample DDL and output.

 SELECT ticketid, extract(epoch from tdiff) FROM ( SELECT ticketid, ticketdate - lag(ticketdate) OVER (PARTITION BY ticketid ORDER BY ticketdate) AS tdiff, dense_rank() OVER (PARTITION BY ticketid ORDER BY ticketdate) AS rank FROM Table1 ORDER BY ticketid) x WHERE rank = 2; 

I used ticketdate as the name of a date column because date is a terrible name for a column (this is a data type name) and should never be used; in many situations, it must be quoted twice.

The portable approach is probably a standalone association published by other users. The windowing approach above probably also works with Oracle, but doesn't seem to work in MySQL. As far as I know, it does not support SQL: 2003 window functions.

The schema definition will work with MySQL if you SET sql_mode = 'ANSI' and use timestamp instead of timestamp with time zone . Window functions do not seem to be; MySQL parses the OVER clause. See this SQLFiddle .

Answer 2

 select ticketid ,time_to_sec(timediff(t2.date, t1.date)) as timediff from table t1 join table t2 on t1.ticketid=t2.ticketid and t1.ticketpostid<t2.ticketpostid 

Answer 3

Try this query -

 INSERT INTO ticket_post(ticketid, ticketpostid, date) VALUES (1387935, 3147808, '2012-09-17 13:33:01'), (1387935, 3147812, '2012-09-17 13:33:41'), (1387938, 3147818, '2012-09-17 13:35:01'), (1387938, 3148068, '2012-09-17 13:37:01'), (1387938, 3148323, '2012-09-17 14:47:01'), (1387939, 3147820, '2012-09-17 13:36:01'), (1387939, 3147834, '2012-09-17 13:36:25'), (1387939, 3147851, '2012-09-17 13:41:01'), (1387939, 3147968, '2012-09-17 13:59:06'), (1387939, 3147996, '2012-09-17 14:03:01'); SELECT ticketid, TIME_TO_SEC(TIMEDIFF(( SELECT t.date FROM ticket_post t WHERE t.ticketid = t1.ticketid AND t.date > t1.date ORDER BY t.date LIMIT 1), MIN(date) )) diff FROM ticket_post t1 GROUP BY ticketid; +----------+------+ | ticketid | diff | +----------+------+ | 1387935 | 40 | | 1387938 | 120 | | 1387939 | 24 | +----------+------+