CODEWITHSUNDEEP
mysql
user2208373
user2208373

Reputation: 1

mySql TimeDiff between TimeStamp in Rows of a Single Table

I have 500 Kiosks in stores and have a table that tracks each print's timestamp by store ID. I need to produce a recordset that gives me the time between prints in seconds. All this data is stored in a single table. A record is inserted for each print and contains the store ID and timeStamp.

TABLE NAME = print
id  store_id  timestamp
1   1             2013-3-1 00:00:01
2   2             2013-3-1 00:00:01 *
3   3             2013-3-1 00:00:01
4   2             2013-3-1 00:00:12 *
5   3             2013-3-1 00:00:06
6   2             2013-3-1 00:00:15 *

I need to pull the time in seconds between ALL of store #2 prints. The * is so you can find the records I need to compare easily.

RecordSet Results Below:

id  store_id  myTimeDiffSeconds
2   2             0
4   2             11
6   2             3

This needs to be simple and fast. Can I do this without a temp table?

Upvotes: 0

Views: 801

Answers (1)

Basavaiah Thambara
Basavaiah Thambara

Reputation: 51

You can write a query in two ways
1. using correlated query
2. using session variable

First one is exactly what ethrbunny pointed out

mysql> SELECT t1.id,
          t1.store_id,
          timestampdiff(second,IFNULL( (SELECT MAX(t2.timestamp) 
                                          FROM print t2 
                                         WHERE t2.store_id=2 
                                               AND t2.timestamp< t1.timestamp)
                       ,t1.timestamp),t1.timestamp) myTimeDiffSeconds
     FROM print t1 
    WHERE t1.store_id=2 ORDER BY t1.timestamp;
+------+----------+-------------------+
| id   | store_id | myTimeDiffSeconds |
+------+----------+-------------------+
|    2 |        2 |                 0 |
|    4 |        2 |                11 |
|    6 |        2 |                 3 |
+------+----------+-------------------+
3 rows in set (0.00 sec)

the other way is to use a session variable to hold the previous time,but in this case we need to get the minimum timestamp for the first time 

mysql> select min(p.timestamp) into @prev_time from print p where p.store_id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select id,
          store_id,
          timestampdiff(second,@prev_time,timestamp) myTimeDiffSeconds,
          @prev_time:=timestamp 
     from print 
    where store_id=2 order by timestamp;
+------+----------+-------------------+---------------------+
| id   | store_id | myTimeDiffSeconds | @prev_time:=t       |
+------+----------+-------------------+---------------------+
|    2 |        2 |                 0 | 2013-03-01 00:00:01 |
|    4 |        2 |                11 | 2013-03-01 00:00:12 |
|    6 |        2 |                 3 | 2013-03-01 00:00:15 |
+------+----------+-------------------+---------------------+
3 rows in set (0.00 sec)

index on (timestamp,store_id) will make the query to perform better.

Upvotes: 2

Related Questions