MySQL TimeSpan help

Databases
21 
           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
                 |               |

I marked it in red, hope you dont mind me using your chart!

For case 1, we would need to get F to L

For case 2, we would need to get from D to L

Am I right?

22

This would fall under case number 3. starts within 2010-07-14 and ends afterwards (7/15).

23

Hello again,

Thank you so much for spending time for this. Here is what I am coming up with. I am not sure if it is what is required.

  1. lastrecordeddatetime BETWEEN ‘2010-07-14 00:00:00’ AND ‘2010-07-14 23:59:59’
  2. lastrecordeddatetime < ‘2010-07-14 23:59:59’
  3. firstrecordeddatetime BETWEEN ‘2010-07-14 00:00:00’ AND ‘2010-07-14 23:59:59’
  4. firstrecordeddatetime < ‘2010-07-14 00:00:00’ AND lastrecordeddatetime > ‘2010-07-14 23:59:59’
24

Hello r937,

The query I posted is what I originally had. I am not sure how to edit it in order to satisfy the 4 conditions you mentioned. What you said is right, currently it does not get me what I would like to see. What you said in the original post is exactly how I need it. Could you give me an idea on how to satisfy those conditions?

Thank you for your time.

25

i’m not sure yo umeant this to be the logic for the first case i posted, but if do, it’s wrong

in case 1, where both start and end are within the 14th, the time difference is end - start

do you understand why?

what you had is essentially the entire day as the difference

i know it’s tricky, but you have to be able to figure this out before you try to write the SQL

26

Hello r937,

Thanks for your response. Here is the query I had:

SELECT videoid, SUM(TIME_TO_SEC(TIMEDIFF(lastrecordeddatetime, firstrecordeddatetime))) FROM useractivity WHERE lastrecordeddatetime > '2010-07-14 00:00:00' AND lastrecordeddatetime < DATE_ADD('2010-07-14 00:00:00', INTERVAL 1 HOUR) GROUP BY videoid
27

i think we got it :slight_smile:

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

so the portion that falls on the 14th is the portion shown in red (hope you can see that)

and the time difference is F to D+1

and i had previously given you the answer (although it probably wasn’t very clear) that for case 4, the portion that falls on the 14th is D to D+1

can you do case 1 and case 2?

:slight_smile: