[SOLVED] advice on how to format dates in a database...

[Dragen]

Hi,

what is the best or easiest format for storing dates in a mysql database? I'm wanting to store a lot of things with a date value and want it to be as easy as possible to read the date from it... I've had problems with getting dates in the past because of how I stupidly stored them.

I was thinking of simply having

day/month/year
e.g:
22/05/1988

or should I use a unix timestamp?

I really don't know....

[AndyB]

use date or datetime - you can operate on either since they are rational. All sorts of methods exist for displaying them in whatever local format you want.

[rcorlew]

I would recommend using the mysql native date format yyyy-mm-dd, you can do some fairly powerful comparisons easily with it stored that way, as long as the column is set to 'date' type.

[Dragen]

well at the moment my column is called date (for obvious reason..) and is set as date.

I was using dd/mm/yyyy as the format, but changing it to yyyy-mm-dd as changing the column to date seems to require it being formated like this. I'll just have to re-format it using php to display it in dd/mm/yyyy format.

[Barand]

I've said this few times in these forums - store dates for functionality and not appearance.

 

dd/mm/yyyy format makes it impossible to compare date values ( > or < ) and thus you can't order by that column or select ranges. Nor can you use the many date functions provided in MySql.

[Dragen]

yeah.. that's one of the problems I've had.. I'm now using the yyyy-mm-dd format and am going to use php to take it from the table and re-format it for display.