CODEWITHSUNDEEP
mysqlsqldatetimestamp
user2476011
user2476011

Reputation: 19

MySQL: Substract Timestamp for Different IDs

Let's say I have one table with an ID, timestamp and an event.

For example: I habe a Book with the ID 1, another with ID 2. Both are lent at a specific timestamp, marked in the table by event 1.

Then Both are returned with event 2. So how can i find out the lending period for each of the books? I cant find an answer and i'm becoming crazy!

Table

ID   |   Timestamp   |   Event
-------------------------------
a1   |  2013-10-23   |    1
a2   |  2013-10-23   |    1
a1   |  2013-10-25   |    2
a2   |  2013-10-26   |    2

Result

ID   |   Days lent   |
-----------------------
a1   |      2        | 
a2   |      3        |

I dont have a better explanation, sorry. Every Subquery i tried results in an error with "more than one row".

I tried TIMESTAMPDIFF in subquerys, i tried to substract two querys. There are of course many different IDs, but the structure is exactly as posted

Upvotes: 0

Views: 97

Answers (3)

Pradeep Pati
Pradeep Pati

Reputation: 5919

In your design, you have insufficient data to do the calculations. E.g. If the book a1 is issued again, you would just insert another event 3, which would then create a confusion, like which of event 1, 2 and 3 are issue event and which are return event.

To solve this, I propose the following.

Use same event IDs for the issue/return event pair. Add new indicator to show which kind of record is it, issue or return.

E.g.

ID   |   Timestamp   |   Event | Type
---------------------------------------
a1   |  2013-10-23   |    1    | Issue
a2   |  2013-10-23   |    1    | Issue
a1   |  2013-10-25   |    1    | Return
a1   |  2013-10-26   |    1    | Return

So in this case the query would be 

Select Book.ID, (Return_T.return_date - Issue_T.issue_date) duration
from 
(Select ID, Timestamp issue_date from Book where Type='Issue') Issue_T,
(Select ID, Timestamp return_date from Book where Type='Return') Return_T,
Book
where Book.ID = Issue_T.ID
and Issue_T.ID = Return_T.ID

Upvotes: 1

fthiella
fthiella

Reputation: 49089

You could use a query like this:

SELECT
  id,   
  DATEDIFF(
    MAX(CASE WHEN Event=2 THEN Timestamp END),
    MIN(CASE WHEN Event=1 THEN Timestamp END)) AS days_lent
FROM
  schedule
GROUP BY
  id

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115610

SELECT 
    s.id,   
    DATEDIFF(e.timestamp, s.timestamp) AS Days_lent
FROM 
    schedule AS s          -- start
  JOIN
    schedule AS e          -- end
      ON  s.id = e.id
      AND s.timestamp < e.timestamp 
WHERE
    s.event = 1
  AND
    e.event = 2 ;

Test: SQL-Fiddle

Upvotes: 1

Related Questions