mysql and order by date?

Question

Hi,

I want to know what format I should store dates in mysql database e.g. 10/09/07?? I need to order the dates when they are retrieved from the database, what date format will mysql understand when I use ORDERBY?

Answer 1

You should store them as the MySQL DATE or DATETIME data type, DATETIME includes a time as well e.g.

CREATE TABLE [Accounts] (
[account_key] int,
[added_date] DATETIME
)

As for writing the data away and performing comparisons you should use the format 'YYYY-MM-DD HH:MM:SS' e.g.

WHERE [added_date] > '2007-03-07 00:00:00'

This format avoids any ambiguity about which way round day and month values are; US dates are MM/DD/YYYY whereas UK dates are DD/MM/YYYY.

Answer 2

Check DATE function in www.php.net (the official site). All options are there.
When WRITING to DB, convert your date into TIMESTAMP format (just a long number: the number of seconds since January 1 1970 00:00:00). That is what is written in the DB field. The ORDERBY works very well in that case if your field is DATETIME type.
Then, when you READ from DB, the ORDERBY (ASC or DESC) will work fine, and when you display, convert your DATETIME format (long number) into the DATE() format you want...
Easy!

Answer 3

Mysql stores the date as a number
DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32,
It may make more sense if you understand the timestamp: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)
ORDERBY will just sort the "datenumber" as any other number.

Answer 4

the date is a datatype .. and a data type have rules for compare..

the "date format" is only for string conversion..