MYSQL and time sorting

Hexxx

I have a Table called observation which has two attributes, obs_s_time and obs_e_time of date time type, along with a viewer ID number.

I need to be able to go trough the table and check the ID number who were active in 10 minute intervals. The query I am trying is

 for($hour = 0; $hour < 24; $hour++){
            for ($interval = 0; $interval != 60; $interval = $interval + 10 ) {


$query = "select obs_no, viewer_id from observation ".
"$hour between hour(obs_s_time) and hour(obs_e_time) and ".
"( $interval <= minute(obs_s_time) or $interval <= minute(obs_e_time) or $hour between hour(obs_s_time) and hour(obs_e_time) ) and ".
"((date(obs_s_time) = '$date') or (date(obs_e_time) ='$date') or ('$date' between date(obs_s_time) and date(obs_e_time))) ";						
}

}

But it isn't selecting them correctly.

basically if my table is

obs_no   obs_s_time                     obs_e_time  

   3   2003-01-01  03:15:00     2003-01-01 03:45:00 
   6   2003-01-01  00:14:00     2003-01-01 00:17:00 
   8   2003-01-01  02:02:00     2003-01-01 02:15:00 
   9   2003-01-01  02:10:00     2003-01-01 02:25:00

What I what is something like this

     Time                                  obs_no
0:00 - 0:10                               

0:10 - 0:20                               6
0:20 - 0:30
0:30 - 0:40
0:40 - 0:50
0:50 - 1:00

1:00 - 1:10                               

1:10 - 1:20
1:20 - 1:30
1:30 - 1:40
1:40 - 1:50
1:50 - 2:00


2:00 - 2:10                             8       9           

2:10 - 2:20                             8       9
2:20 - 2:30                             9
2:30 - 2:40
2:40 - 2:50
2:50 - 3:00

3:00 - 3:10                               

3:10 - 3:20                           3
3:20 - 3:30                           3
3:30 - 3:40                           3
3:40 - 3:50                           3
3:50 - 4:00

... like that... basicaly just taking out the obs number and putting it in the 10 min interval which it would belong to (I need to use the number

Hexxx

anyone? It seems trivial and common enough that it should have a function for finding the time interval between dates somewhere.

Sxooter

Can you cast date time type to text in your version of mysql? I do something like

group by substring(cast(timestampfield as text),1,15)

in postgresql. I wrote a user function that takes that and tacks on 0:00 to the end to return a tuncated date so that 2007-01-29 12:34:56 becomes 2007-01-29 12:30:00.

Hope the idea helps.

If you can't cast to text, then maybe you could do it with modulo math on the datetime type. I'd look through the mysql manual for date / time functions to see what can get you there.

Hexxx

I'll try the casting and see what I could do.

To me the mysql between function doesn't really give what I expect when I use between with date, or datetime types but I could be interpreting it wrong, I''ll see if I can find an example and post back.