CODEWITHSUNDEEP
phpmysql
Eric
Eric

Reputation: 119

MYSQL most recent time

Is there a way to grab the most recent time of day. If your data in the database is formatted like this 07:00AM and 08:00pm and 12:00pm. Sorta like max(). But for the time. In a Mysql query.

Thanks Eric

Upvotes: 2

Views: 201

Answers (4)

spanky
spanky

Reputation: 1499

You should try STR_TO_DATE() instead if you're using a string. If your times are always formatted as hh:mmAMPM, you can use:

MAX(STR_TO_DATE(YourTimeField,'%h:%i%p'))

This converts your string to a time, without any need to split it up by substring or anything, so MySQL would then see 09:07AM as 09:07:00 and 02:35PM as 14:35:00, and then would easily be able to determine the MAX of it.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838186

It would be best to store it in another format rather than as text. Or at least store it in 24 hour format, then a simple sort would work. You can convert it to 12-hour format when you display the data to the user.

But assuming you can't change your database schema, try this:

SELECT *
FROM your_table
ORDER BY STR_TO_DATE(your_time, '%h:%i%p') DESC
LIMIT 1

Note that this won't be able to use an index to perform the sorting.

Upvotes: 3

littlegreen
littlegreen

Reputation: 7420

Assuming you are dealing with a DATETIME field in your MySQL, you can use this query to get the max time per day:

SELECT DATE(YourDateField), MAX(TIME(YourDateField)) FROM YourTable
GROUP BY DATE(YourDateField)

When you are dealing with a VARCHAR field, you can try a hack like this:

SELECT YourDateField, SUBSTRING(MAX(
    CASE WHEN YourTimeField LIKE '%AM%' THEN '0' ELSE '1' END 
    + REPLACE(YourTimeField, '12:', '00:')
), 2)
GROUP BY YourDateField

Upvotes: 0

Sean Thayne
Sean Thayne

Reputation: 883

You can just sort.

select time_column from table order by time_column desc limit 1;

Upvotes: -1

Related Questions