MySQL Date and time sorting

Started by alexfernandes, Sep 13, 2022, 12:53 AM

Previous topic - Next topic

alexfernandesTopic starter

Greetings! To put it briefly, I'm new to SQL and still learning. I came across a MySQL database that was handed down to me and noticed that the column for storing date/time was labeled as "date". When I tried executing a query using the following syntax:

SELECT * FROM mytable ORDER BY DATE DECS
the results only sorted by day with no consideration given to the month, year, and time values.

To sort the entries correctly, there are a few different options available. One option is to rename the column before executing the select query. Alternatively, you could modify the query to include the specific date/time column name. 
  •  

JeniAnderson

What is the correct syntax for sorting entries? Could it be either of the following:

SELECT * FROM my table ORDER BY [DATE] DESC
or

SELECT * FROM my table ORDER BY `DATE' DESC
It's worth noting that, in this particular case, the sorting seems to be correct based on the date-time values.
  •  

janiman

It seems there may be a misunderstanding or confusion in your SQL query syntax. The DATE in your query might be misinterpreted by MySQL as the DATE() function rather than the column name, which might be causing your problems. Also, there's a typo in your query: DECS should be DESC.

If you're dealing with a column storing date/time values and you want to sort them in descending order by the full date and time, the correct syntax should be:

SELECT * FROM mytable ORDER BY `date` DESC
Here, using backticks () around dateensures that MySQL treats it as a column name, rather than a function name. TheDESC` keyword specifies descending order - from the most recent date/time to the oldest.

If you still encounter issues after this, additionally ensure that the data type of your date column is either DATE, DATETIME, or TIMESTAMP which are the usual types for storing date and/or time in MySQL. If it's not, this might be the root of your sorting problem, because other data types like VARCHAR do not support proper date/time sorting.


In case the column storing date/time values is not of a date or timestamp type (i.e., it's stored as a string or some other format), you would need to convert it into a proper date/time type before sorting. You can use MySQL's STR_TO_DATE() or CAST() function for this purpose.

For example, if your dates are stored in a format like 'YYYY-MM-DD HH:MI:SS' in a VARCHAR or TEXT column, you should convert it to a datetime type first:

SELECT *, STR_TO_DATE(`date`, '%Y-%m-%d %H:%i:%s') as formatted_date
FROM mytable
ORDER BY formatted_date DESC
However, it's important to use this as a last resort. Converting column data with functions during a select operation can greatly reduce the performance of your queries. If possible, consider changing the data type of your column permanently to a date/time type. This could be done with an ALTER TABLE command:

ALTER TABLE mytable
MODIFY `date` DATETIME;
Keep in mind that the modification command should be used carefully and it's recommended to back up your data before making such changes since it can potentially lead to data loss if not used correctly.

Also, the term 'date' for a column name which stores date and time is somewhat misleading. To enhance the readability and maintainability of your code base, consider renaming it to something more descriptive like event_time or recorded_at. You can rename it with the following command:

ALTER TABLE mytable
CHANGE `date` `recorded_at` DATETIME;
After that, your select query would look like:

SELECT * FROM mytable
ORDER BY `recorded_at` DESC
Remember, the use of backticks () around your column names is especially important when you're using any MySQL reserved words as column names, like date` in this case.


MySQL provides several functions to manipulate and work with date and time values. Methods for date and time sorting are usually based on the following functions:

DATE(): Extracts the date part of a date or date/time expression.
TIME(): Extracts the time part of a date or date/time expression.
YEAR(), MONTH(), DAY(): Extracts the respective part of a date.
HOUR(), MINUTE(), SECOND(): Extracts the respective part of a time.
When sorting dates or times, you can use the ORDER BY clause. For example, if you have a table named "events" with a datetime column named "event_date", you can sort this table in ascending order based on the date like so:

SELECT * FROM events ORDER BY event_date ASC;
Or, in descending order:

SELECT * FROM events ORDER BY event_date DESC;
If you have separate date and time columns and want to sort by both, you can use a comma to separate the columns you want to sort by:

SELECT * FROM events ORDER BY event_date DESC, event_time DESC;
In this case, the records are first sorted by date in descending order. For records with the same date, they are then sorted by time in descending order.

Finally, you can utilize functions if needed. For example, to sort by the month, regardless of the year:

SELECT * FROM events ORDER BY MONTH(event_date);
One caveat to be aware of when working with dates is that they should be in a proper date or datetime format. If your dates are stored as strings in a different format, you'll need to use the STR_TO_DATE() function to convert them to a date format that MySQL can recognize and sort correctly.

Remember to ensure your data is in the appropriate date or datetime datatype in MySQL for accurate sorting. Trying to sort a date value saved as a string/VARCHAR may not yield correct results because it will be sorted as a string, not a date.
  •