Dragen Posted April 17, 2007 Share Posted April 17, 2007 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.... Link to comment https://forums.phpfreaks.com/topic/47460-solved-advice-on-how-to-format-dates-in-a-database/ Share on other sites More sharing options...
AndyB Posted April 17, 2007 Share Posted April 17, 2007 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. Link to comment https://forums.phpfreaks.com/topic/47460-solved-advice-on-how-to-format-dates-in-a-database/#findComment-231704 Share on other sites More sharing options...
rcorlew Posted April 17, 2007 Share Posted April 17, 2007 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. Link to comment https://forums.phpfreaks.com/topic/47460-solved-advice-on-how-to-format-dates-in-a-database/#findComment-231708 Share on other sites More sharing options...
Dragen Posted April 17, 2007 Author Share Posted April 17, 2007 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. Link to comment https://forums.phpfreaks.com/topic/47460-solved-advice-on-how-to-format-dates-in-a-database/#findComment-231711 Share on other sites More sharing options...
Barand Posted April 17, 2007 Share Posted April 17, 2007 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. Link to comment https://forums.phpfreaks.com/topic/47460-solved-advice-on-how-to-format-dates-in-a-database/#findComment-231717 Share on other sites More sharing options...
Dragen Posted April 17, 2007 Author Share Posted April 17, 2007 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. Link to comment https://forums.phpfreaks.com/topic/47460-solved-advice-on-how-to-format-dates-in-a-database/#findComment-231723 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.