Page 1 of 1

MAX time

Posted: Mon Jul 19, 2010 10:43 am
by incubi
Hello,

This is more a MySql question than PHP but I don’t know any good MySql forums. Maybe someone here is good enough with it to help.

I have a PHP script that is designed to show values in a table. The way it works makes it very difficult to change field types without a lot of recoding. So, I have a time value/field in the format 00:00:00 24hr time and no date value I can use with it. In my query I group multiple records and need to get the MAX of the time field.

MAX(TIME_TO_SEC(TIME_FORMAT(End, '%r') ) ) AS MaxEnd,

The issue is, in Military time hour 0 is less than 23 so it works until hour 0 then it fails. In am/pm 2pm is less than 12pm or 11am so it fails. I tried converting it to seconds as above but it works with some recodes and not others. For example, the above works if the time is 00:06:00 but not if it is 14:30:00. If I change it to this

MAX(TIME_TO_SEC(End) ) AS MaxEnd,

It works for 14:30:00 but not for 00:06:00.

I need to do this in the query because I’ve already grouped the records by the time I use the values in PHP.
Also, I can’t do any sorting or other things will get messed up otherwise I would just sort for the max time.

Thanks

incubi

Re: MAX time

Posted: Mon Jul 19, 2010 10:46 am
by pickle
incubi wrote:This is more a MySql question than PHP but I don’t know any good MySql forums
Our Databases forum would be a good start.

Topic moved.

Re: MAX time

Posted: Mon Jul 19, 2010 10:48 am
by pickle
I'm not entirely sure what your problem is. Of course 2300 is later than 0000.

Re: MAX time

Posted: Mon Jul 19, 2010 10:58 am
by incubi
In a nut shell. I trying to get the Min and Max times of a range or a Start and End time if you will in the Mysql Query.

For example,

Start End
7/19/2010 8:00:00 PM 7/19/2010 12:06:00 AM
7/19/2010 1:51:00 PM 7/19/2010 7:30:00 PM
7/19/2010 1:39:00 PM 7/19/2010 1:51:00 PM
7/19/2010 8:00:00 PM 7/19/2010 9:57:00 PM

Start End
20:00:00 00:06:00
13:51:00 19:30:00
13:39:00 13:51:00
20:00:00 21:57:00

How would you get the Min Start and the Max End times faced with these values and the limits I outlined ?

Re: MAX time

Posted: Mon Jul 19, 2010 11:18 am
by pickle
MIN(`start`), MAX(`end`)

If you're still flexible with the date format, I'd suggest standardizing on the MySQL timestamp format: YYYY-MM-DD HH:mm:ss - then all the MySQL date functions will be available to you.

Re: MAX time

Posted: Mon Jul 19, 2010 11:38 am
by incubi
Thanks, no I don't have a date value I can work with. The MIN(`start`), MAX(`end`) doesn't work with time values right form what I have been dealing with.

Say the Min Start time is 13:39:00 and the Max end time is 00:06:00 but another time in the End field is 21:57:00
MAX will not get 00:06:00 because it falls on another day and is a smaller number than 21:57.

Re: MAX time

Posted: Mon Jul 19, 2010 11:58 am
by pickle
How do you figure 06:00:00 the maximum end time when 21:57:00 is later?

Re: MAX time

Posted: Mon Jul 19, 2010 12:04 pm
by incubi
Thanks for the reply

00:06:00 not 06:00:00

21:57 is not later when 00:06:00 (12:06AM) falls on a new day.

thanks
incubi

Re: MAX time

Posted: Mon Jul 19, 2010 12:07 pm
by pickle
But you said you have no dates associated with the times. If I see two times - 6 o'clock in the morning and 21:57 at night, I'm going to think 21:57 is later. MySQL is going to think that as well.

Re: MAX time

Posted: Mon Jul 19, 2010 12:22 pm
by incubi
That's my dilemma. I have to somehow know it’s a new day with out a date. I fully understand that using a date value is the right way to do this but this change is a request after all the program and creating the database tables were done so I would have to go back and start over. So, I was hoping for some trick or uncommon way to get there.

Re: MAX time

Posted: Mon Jul 19, 2010 12:33 pm
by pickle
Unless you have some business processes you can rely on to say "this 00:06:00 is definitely on the same day as the start time but this 00:06:00 is definitely on a later day", then you're hooped.

Re: MAX time

Posted: Mon Jul 19, 2010 12:44 pm
by incubi
I’m thinking there could be some type of conditional code that could be done in MySql to get this to work but I just don’t know how to do it. MySql support IF, CASE and stuff so……

Re: MAX time

Posted: Mon Jul 19, 2010 12:50 pm
by pickle
Ya it does, but without any additional rules to base a decision or condition on - and you haven't specified any - there's no way for MySQL to determine what day a given time is on.

Re: MAX time

Posted: Tue Jul 20, 2010 9:39 am
by incubi
I was on a mysql forum and a sharp cookie there (Chad) was able to answer this so I wanted to share it with you all.

This is what he came up with.

select empId,
min(startTime) 'Min Start',
date_format(max(str_to_date(concat(curdate() +
interval case when startTime > endTime then 1 else 0 end day, endTime),
'%Y-%m-%d%T')), '%T') 'Max End'
from table
group by empId;

So far its working well!!

Credit to Chad Bourque