MySQL TimeSpan help

Hello,

I have a table like this:

table name:

useractivity

fields:

videoid int(5)
firstrecordeddatetime datetime
lastrecordeddatetime datetime

and I have an entry like this:

1
2010-07-14 01:00:00
2010-07-15 03:00:00

Now what I neeed is how many hours were watched in a given time frame. Lets say I want to know how much time in seconds video 1 was watched on 2010-07-14. What would that query look like?

Also, some other conditions if you can help me are:

There will duplicate video id entries. I need to group by the video id and get a sum of how much time in seconds was watched for video 1 on 2010-07-14.

Thank you so much for your help!

okay, now we can flesh out the SELECT clause…


SELECT SUM(
       CASE WHEN firstrecordeddatetime >= '2010-07-14'
            THEN UNIX_TIMESTAMP(
                 LEAST(lastrecordeddatetime                      -- case 1
                      ,firstrecordeddatetime + INTERVAL 1 DAY))  -- case 3
               - UNIX_TIMESTAMP(firstrecordeddatetime)
            ELSE UNIX_TIMESTAMP(
                 LEAST(firstrecordeddatetime + INTERVAL 1 DAY    -- case 4
                      ,lastrecordeddatetime))                    -- case 2
               - UNIX_TIMESTAMP('2010-07-14')
        END) AS seconds
  FROM ...

you may not be familiar with the LEAST function in mysql, and generally i don’t use non-standard functions, but it’s a lot easier to write LEAST(a,b) than CASE WHEN a < b THEN a ELSE b END

if you refer back to the timeline diagram you can confirm how i’ve covered all 4 cases

last step will be the construction of the WHERE clause, as it is very important to pull up only these 4 cases and no others, i.e. we’re only interested in videos which actually overlap ‘2010-07-14’

Hello again,

Thank you very much for all your help. Here is the query again with the WHERE clause how you explained. I also added GROUP BY videoid. Please have a look.


SELECT videoid, SUM(
       CASE WHEN firstrecordeddatetime >= '2010-07-14 00:00:00'
            THEN UNIX_TIMESTAMP(
                 LEAST(lastrecordeddatetime                      	-- case 1
                      ,firstrecordeddatetime + INTERVAL 1 DAY))  	-- case 3
               - UNIX_TIMESTAMP(firstrecordeddatetime)
            ELSE UNIX_TIMESTAMP(
                 LEAST(firstrecordeddatetime + INTERVAL 1 DAY    	-- case 4
                      ,lastrecordeddatetime))                    	-- case 2
               - UNIX_TIMESTAMP('2010-07-14 00:00:00')
        END) AS seconds
  FROM useractivity
  WHERE lastrecordeddatetime > '2010-07-14 00:00:00'			-- rules out case 5
  AND firstrecordeddatetime < ('2010-07-14 00:00:00' + INTERVAL 1 DAY)	-- rules out case 6
  GROUP BY videoid

Assuming that query is correct, if I wanted to get an hourly reading, would I just be able to do D + 1 hour instead of day? D being the start time? Sorry if I am jumping ahead!

what, you haven’t tested it yet? :wink:

yes, of course, D and D+1 are simply two datetimes, and, as i suggested, you should probably be feeding both values in from php (or whatever application language you’re using)

and there’s no jumping ahead, 'cause i think we’re done

:cool:

OMG OMG OMG i made a couple of huge errors in post #20

(what can i say, i’m sorry, but it was 6am and i hadn’t even had my coffee yet)

it should be like this (changes marked in red) –

SELECT SUM(
       CASE WHEN firstrecordeddatetime >= '2010-07-14'
            THEN UNIX_TIMESTAMP(
                 LEAST(lastrecordeddatetime             -- case 1
                      ,[COLOR="Red"]'2010-07-14'[/COLOR] + INTERVAL 1 DAY))  -- case 3
               - UNIX_TIMESTAMP(firstrecordeddatetime)
            ELSE UNIX_TIMESTAMP(
                 LEAST([COLOR="red"]'2010-07-14'[/COLOR] + INTERVAL 1 DAY    -- case 4
                      ,lastrecordeddatetime))           -- case 2
               - UNIX_TIMESTAMP('2010-07-14')
        END) AS seconds
  FROM ...

that query isn’t even close to what you said you wanted

the WHERE clause apparently selects only those rows which ended between midnight and 1am on the 14th

but the calculation seems to be adding up the entire interval from start to finish, even if the start occurred well before the 14th

i mentioned there were 4 cases you should analyse, and your query doesn’t satisfy any of them

Ah, LEAST is a new one for me. I read up about it on the MySQL docs. It returns the smallest value out of the given values. What all would we need in the WHERE clause?

Thanks for your time!

Everything is working with the day. The datetime will be inserted by PHP. When I try to edit the query for hours, I am getting incorrect values (even negative values). Here is what the hourly query I did looks like:


SELECT videoid, COUNT(*) AS count, SUM(
       CASE WHEN firstrecordeddatetime >= '2010-07-14 00:00:00'
            THEN UNIX_TIMESTAMP(
                 LEAST(lastrecordeddatetime                      	-- case 1
                      ,firstrecordeddatetime + INTERVAL 1 HOUR))  	-- case 3
               - UNIX_TIMESTAMP(firstrecordeddatetime)
            ELSE UNIX_TIMESTAMP(
                 LEAST(firstrecordeddatetime + INTERVAL 1 HOUR    	-- case 4
                      ,lastrecordeddatetime))                    	-- case 2
               - UNIX_TIMESTAMP('2010-07-14 00:00:00')
        END) AS seconds
  FROM useractivity
  WHERE lastrecordeddatetime > '2010-07-14 00:00:00'			-- rules out case 5
  AND firstrecordeddatetime < ('2010-07-14 00:00:00' + INTERVAL 1 HOUR)	-- rules out case 6
  GROUP BY videoid

Aslo added a COUNT in there.

presumably the start and end datetimes can be any valid times on the 24-hr clock, yes?

so what you have to do is set up the logic to handle several situations

  1. starts and stops entirely within 2010-07-14
  2. starts before 2010-07-14 and ends within
  3. starts within 2010-07-14 and ends afterwards
  4. starts before 2010-07-14 and ends afterwards

make sure you understand all 4 of those conditions first, yes? and work out the logic for each one in terms of which value to subtract from which value in order to get the duration specifically within 2010-07-14

i’ll do the 4th one for you: the duration is midnight to midnight, i.e. 86400

:slight_smile:

finally, we can make progress

so now let’s take your single row of data and see where it falls

I have an entry like this:

1
2010-07-14 01:00:00
2010-07-15 03:00:00
which case is this?

Hello again,

I understood the diagram. That is exactly what I need. In each case in the diagram, I need to know how many seconds were watched between 2010-07-14 00:00:00 and 2010-07-15 00:00:00.

okay, maybe i don’t understand what you really want

you gave only 1 example of data, and i am trying to get you to understand which of the 4 cases it represents

let’s try a diagram

suppose we let firstrecordeddatetime be represented by the letter F, and lastrecordeddatetime be represented by the letter L

then the duration of the video can be represented by a line: F----------------L

with me so far?

now your original question was “I want to know how much time in seconds video 1 was watched on 2010-07-14

so we’re going to examine the 4 separate cases in a diagram

first, a quick recap of the 4 cases –

  1. starts and stops entirely within 2010-07-14
  2. starts before 2010-07-14 and ends within
  3. starts within 2010-07-14 and ends afterwards
  4. starts before 2010-07-14 and ends afterwards

now the diagram –

              2010-07-14     2010-07-15
               00:00           00:00
                 |               |
case 1           |   F-------L   |         
                 |               |
case 2      F----------L         |
                 |               |
case 3           |      F--------------L
                 |               |
case 4     F---------------------------L
                 |               |

first of all, do you understand this diagram?

please don’t try to race ahead of me here, or i will seriously lose patience

do you undestand this diagram and the 4 cases?

I think I misunderstood what you wrote. Sorry about that. I think this is what you wanted me to do:

for case no. 1, i want the elapsed time from 2010-07-14 01:00:00 to 2010-07-14 02:00:00 so the duration watched on the 14th would be 1 hour
for case no. 2, i want the elapsed time from 2010-07-13 23:00:00 to 2010-07-14 01:00:00 so the duration watched on the 14th would be 1 hour
for case no. 3, i want the elapsed time from 2010-07-14 23:00:00 to 2010-07-15 01:00:00 so the duration watched on the 14th would be 1 hour
for case no. 4, i want the elapsed time from 2010-07-13 23:00:00 to 2010-07-15 01:00:00 so the duration watched on the 14th would be 24 hours

Thank you for your patience.

but that’s what i was trying to get you to do – think about the 4 cases

okay, here is a template for you:[indent]for case no. ___, i want the elapsed time from ________ to _______[/indent]as i mentioned earlier, i already did case no. 4 for you --[indent]for case no. 4, i want the elapsed time from midnight morning on the 14th to midnight evening on the 14th[/indent]you need to figure out the other three, and i’ll take care of putting the SQL together for all 4 cases

:slight_smile:

Still following! :slight_smile:

okay, let’s go back to the very beginning of the problem

I have an entry like this:

1
2010-07-14 01:00:00
2010-07-15 03:00:00

Lets say I want to know how much time in seconds video 1 was watched on 2010-07-14.
which of the 4 cases is this? no, don’t guess, i will tell you – it’s case 3

3. starts within 2010-07-14 and ends afterwards

correct? i mean, if you can’t follow along now, then you might want to consider outsourcing your programming

so video 1 starts within 2010-07-14 (specifically, at 1 am), and ends afterwards (specifically, on the next day)

so the time difference for the 14th is from 01:00 until midnight – you can’t count anything before 1:00 on the 14th (because the watching hasn’t started yet), and you can’t count anything after midnight on the 14th/15th (because then it’s no longer in the 14th)

do you understand this?

this was an example of case 3

i already did case 4 for you

now would you please try cases 1 and 2?

:slight_smile:

I was thinking something like using ‘OR’ in between all the cases? I am not sure I understand what you mean by end-start for the first one. End of the day - start of the day? Or end time of the video - start time?

in the WHERE clause, all you need to do is rule out cases 5 and 6 in the following diagram –

                  2010-07-14[COLOR="red"](D)[/COLOR]     2010-07-15[COLOR="red"](D+1)[/COLOR]
                      00:00           00:00
                        |               |
case 1                  |   [COLOR="Red"]F-------L[/COLOR]   |         
                        |               |
case 2             F----[COLOR="Red"]x-----L[/COLOR]         |
                        |               |
case 3                  |      [COLOR="Red"]F--------o[/COLOR]-----L
                        |               |
case 4            F-----[COLOR="Red"]x---------------o[/COLOR]-----L
                        |               |
case 5      F-----L     |               |
                        |               |
case 6                  |               |   F-------L

the WHERE clause is now easy to write –


WHERE L > D    -- rules out case 5
  AND F < D+1  -- rules out case 6

note that both conditions must be true, i.e. it has to be AND not OR

now all that remains for you to do is substitute for F and L and D, finish writing the entire query, and alternatively substituting actual date values (using php or whatever you’re using) for D and D+1

i’d be interested in seeing the final query when you’re done

Thank you very much! It is working like a champ now. I appreciate all your help & patience especially even before having your coffee :slight_smile:

Thanks again!

YES!!! :stuck_out_tongue:

sorry for my earlier impatience, but if you review the thread, you will see where i was going all along…

okay, so we have 4 different cases to consider

it’s going to look something like this …

SELECT CASE WHEN ... -- case 1
            THEN L - F
            WHEN ... -- case 2
            THEN L - D
            WHEN ... -- case 3
            THEN D+1 - F
            WHEN ... -- case 4
            THEN D+1 - D

still with me?? :slight_smile: