MAX time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

MAX time

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post by pickle »

I'm not entirely sure what your problem is. Of course 2300 is later than 0000.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: MAX time

Post 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 ?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: MAX time

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post by pickle »

How do you figure 06:00:00 the maximum end time when 21:57:00 is later?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: MAX time

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: MAX time

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: MAX time

Post 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……
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MAX time

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: MAX time

Post 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
Post Reply