MySQL - Select Max Date and Max time for that Date

Find the closest date and time value to now

In a MySQL database you can create a column which has both date and time within it - DATETIME. This column holds both these values in the format YYYY:MM:DD HH:MM:SS. This can be very handy in many situations. However life is not perfect and you may find yourself working with a database where the date and time columns values are in separate columns, in a DATE column and in a TIME column.

Generally SQL is very straightforward to start off with and selecting and returning data is very easy. But SQL can be very complex especially when you work with multiple tables and a lot of different columns. You can CONCAT values within your SQL statement to return them as a single value and in theory you can return the separate DATE and TIME columns as a DATETIME value. If you wanted to return the maximum date and maximum time from a database table you may do something like:

"SELECT CONCAT(MAX(date),' ',MAX(time)) AS date_time FROM table_name" // the wrong way

While on the surface this may look like the correct way to return the maximum (closest) date and the maximum (closest) time for that date, the effect is probably not the one you want. What this will actually do is return the maximum date and the maximum time for any date. I recently found myself in the situation where I had to return the maximum date and time for that date and at first while this query looked like the way to go I found that the result just was not returning what I wanted. After figuring out the logic this is the query which will return the result I wanted:

"SELECT CONCAT(date,' ',time) AS date_time
FROM table_name WHERE time
IN(SELECT MAX(time) FROM table_name WHERE date IN(SELECT MAX(date) FROM table_name))"

If you have a basic knowledge of SQL the light bulb will probably switch on pretty quickly. For others a quick explanation is SQL is not read exactly how it is written from left to right. Different things are read before the select part of the statement is read. You can’t select from something unless you know first what you are selecting from and what conditions need to be fulfilled.

If you skip to the last line above you will see the simple logic behind what is happening here. In the last set of brackets (SELECT MAX(date) FROM table_name) you are selecting the MAX date and because of this condition you are now selecting the max time from this date. You can look at this as a small sub query first which is used by the time query.

Instead of searching through all the database columns and grabbing the maximum date and maximum time you are grabbing the maximum date then grabbing the maximum time from this date. It’s always a good idea to check that SQL queries are returning exactly what you want especially if you are working with max and min values which are dependent on certain conditions.