I have two tables: Positions and Uploads. They contain a Username and a Timestamp among other information. I want to find how long a player spent before uploading content. To do this, I need the lowest time stamp from the position table to get their start time. Then I need to subtract it from the first upload timestamp.
So, in other words: I want to get how long the player spent in the game before uploading their first content.
I could just write a php deal to handle the calculation, but I felt like it might be overkill and there's probably a basic SQL command that can do it automatically. I was brainstorming a bit on how to do it and this is what I have so far:
Select a.UserID, DATEDIFF(Min(a.timestamp),Min( b.Timestamp))
From db.Uploads a, db.Position b
WHERE
a.UserID = b.UserID
GROUP BY a.UserID
Anyone have ideas? I know my code's pretty lame but for the moment that's what I was coming up with.
For some reason it's not super crazy about me using DateDiff but when I did normal subtraction it worked? Bluehost's table setup must be doing something weird.
Select a.UserID, Min(a.timestamp)-Min(b.Timestamp)
From db.Uploads a, db.Position b
WHERE
a.UserID = b.UserID
GROUP BY a.UserID