CODEWITHSUNDEEP
mysqlsql
NeuroApex
NeuroApex

Reputation: 29

MySQL Date Format For Ordering By Date

What is the best way to format a date in a MySQL query?

I am currently running the query below which selects the data but doesn't order the information correctly, I presume this is due to the current format of the 'updatetime' row. 

SELECT * FROM updates  WHERE udcode='Remote Connection' ORDER BY updatetime DESC  LIMIT 20;

The current format is as follows:

31/03/2015 13:41:45

How should this date be formatted in order for the ORDERING to work correctly?

Thanks in advance.

Upvotes: 1

Views: 89

Answers (4)

Dr M L M J
Dr M L M J

Reputation: 2397

You can try following changes

Change date format to 

yyyy-mm-dd hh:mm:ss

And edit query to

SELECT * FROM updates  WHERE udcode='Remote Connection' ORDER BY `updatetime` DESC  LIMIT 0, 20;

Upvotes: 0

Anton
Anton

Reputation: 420

You are doing this correctly in the Query, however if you are getting undesired results then there is a possibility that the data type for your updatetime attribute is not correct.

You should use datetime as the data type so that the query is correctly able to distinguish that the information is a date and knows how to order it properly. For example if you are using a varchar it will think that the first digit reading left to right is the most significant rather than recognising the difference and relationship between days, months, years and time.

If you dont have the correct datatype you would either have to include formatting in a bloated query or you would end up with all the days from 10-19 ordered before the 20-29 and then followed by the days 3-9. In this situation the 30th would be considered to be ordered before the 4th for example.

There will be little relevance by the time you are ordering months or years as the day units will have mixed everything up

Upvotes: 0

oneeach
oneeach

Reputation: 87

you can change the format of your date in MySQL with DATE_FORMAT

SELECT *, DATE_FORMAT(`updatetime`, '%Y-%m-%d %H:%i:%S') AS mydate FROM updates WHERE udcode='Remote Connection' ORDER BY mydate DESC LIMIT 20;

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

Use:

ORDER BY DATE_FORMAT(updatetime, '%Y-%m-%d %H:%i:%S') DESC

Upvotes: 2

Related Questions