Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
mysql date issues
- Thread starter Cabbit
- Start date
- Sort by reaction score
yyyy-mm-dd is the international standard date format, which has the advantage of not being ambiguous like 00-00-0000 (i.e. is it mm-dd-yyyy or dd-mm-yyyy?). Within MySQL statements you can use the str_to_date and date_format functions to change other formats into the format MySQL wants and vice versa. Or, you can do the transformation within the programming language - strtotime() and date() in PHP for example.
The problem with storing dates as strings is that you can't (i think) use MySQL's built in functions for manipulating dates, and it won't be as efficient.
The problem with storing dates as strings is that you can't (i think) use MySQL's built in functions for manipulating dates, and it won't be as efficient.
You can always alter them to whichever format you'd like when you're pulling them back out of the database pretty easily, if the format is a hassle.
If you are a programmer, there is actually a good reason for it! It is known as Japanese format and if the date was in a text string, it would be immediately sort-able without having to juxtapose the date's components first.
Some informal thoughts, a bit of a sidebar discussion...
Traditionally I use the "datetime" type, which is 0000-00-00 00:00:00 and parse the time/date as I need it via code. You can also sort on this field and its useful to calendar driven apps or apps that use form fields to search by year, day, month, hour min and second (plugging in variables into a query). This method is also quite useful when browsing the inserted records via phpMyAdmin or some external utility like Toad, etc., because the datetime fields are human readable. Plus, MySQL has alot of date and time functions that work very well with this field without needing to do any additional conversion.
Or...
If you don't need to browse the database manually nor do you wish to use the advanced date/time functions built into Myself then one of the most efficient and SIMPLE methods for storing and sorting based date and time is to define a field as int(11) , name it timestamp, and store the Unix timestamp. Then you can use from_unixtime() and unix_timestamp() functions to convert to/from that format. And obviously sorting is efficient, even if you define the type as varchr(11). It still works.
The OP wants to store only the date, I follow that, but both these procedures are efficient, sort well, and happen to include the time which might be useful to the developer anyway for history logging.
Just thinkin' out loud, the previous advice was excellent.
-jim
Traditionally I use the "datetime" type, which is 0000-00-00 00:00:00 and parse the time/date as I need it via code. You can also sort on this field and its useful to calendar driven apps or apps that use form fields to search by year, day, month, hour min and second (plugging in variables into a query). This method is also quite useful when browsing the inserted records via phpMyAdmin or some external utility like Toad, etc., because the datetime fields are human readable. Plus, MySQL has alot of date and time functions that work very well with this field without needing to do any additional conversion.
Or...
If you don't need to browse the database manually nor do you wish to use the advanced date/time functions built into Myself then one of the most efficient and SIMPLE methods for storing and sorting based date and time is to define a field as int(11) , name it timestamp, and store the Unix timestamp. Then you can use from_unixtime() and unix_timestamp() functions to convert to/from that format. And obviously sorting is efficient, even if you define the type as varchr(11). It still works.
The OP wants to store only the date, I follow that, but both these procedures are efficient, sort well, and happen to include the time which might be useful to the developer anyway for history logging.
Just thinkin' out loud, the previous advice was excellent.
-jim