CODEWITHSUNDEEP
mysqldatetime
jakedyson
jakedyson

Reputation: 23

how to calculate time difference between two dates? - MySql

code    id                  date               time difference   
AiK4JJ  kcy2000ok   2012-01-31 17:25:41         13    
unBG1D  gktoql      2012-01-31 17:25:35          3    
vzqeWU  gktoql      2012-01-31 17:25:32          4    
vvkOSd  judyssi     2012-01-31 17:25:32          8    
uwhbGt  kcy2000ok   2012-01-31 17:25:28 ?    
unBG1D  gktoql      2012-01-31 17:25:27         ?    
vvkOSd  judyssi     2012-01-31 17:25:24         ?

I want to calculate the time difference in seconds between recent date and previous date based on id.

If you look at kcy2000ok, time difference is 13 seconds for last row.

time difference for gktoql is 3 seconds for last row.

I need to make a query that calculate time difference based on id.

Can you help me to build MySql query?

Upvotes: 0

Views: 3052

Answers (2)

Bohemian
Bohemian

Reputation: 425328

If you mean find the number of seconds since the most recent record for each user:

SELECT id, min(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time))
GROUP BY id

Or to get the most recent record for each user:

SELECT * FROM (
    SELECT *
    FROM mytable
    ORDER BY `date` desc) x
GROUP BY id

Upvotes: 0

iblue
iblue

Reputation: 30434

SELECT id, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) as difference FROM <your-table-name>

Addendum: Your question was a little bit hard to understand until you editied it. So to make this clear: If you want to calculate the minimum difference grouped by id, you can do:

SELECT 
  id,
  MIN(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time)) as difference
FROM your_table_name
GROUP BY id

Upvotes: 3

Related Questions