How to store dates in MySQL

You may be asking me, “Miester I know how to store dates in mysql – you just use the date or datetime data type … duh.” I disagree on this format of storing data. For the following reason …

It’s not flexible. If I store my date in there as YYYY-MM-DD then I have to do a whole bunch of parsing to change how it is displayed.

Why not just store it as a timestamp? You can do the same sorting as you can with dates as well as doing comparisons. Plus this method allows you to know seconds no matter what.

How do you start using this new method, you ask? Use the int(11) data type and then insert time() to insert the timestamp.

Are there any limitations? Yes, as you know (or should) the Unix timestamp was born in 1970 – therefore you will want to continue to use the date data type for birthdays or any date field that might contain a date prior to Jan 1st 1970.

That should be it. It works great for news postings, logs, etc. and is much more flexible than the date or datetime data type.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.