I am sure you are a very prolific web developer and love to know more and more about development standards. I share the same with you then. I too love to code and build awesome working structures on the internet. But what if some small details are letting you down? MySQL is obviously your favorite but it happens to ditch at times as well. The ditch however does not come in any way from the MySQL code or syntax; it comes in your code. So, in this article I’ll discuss some of the lesser known facts about MySQL DATETIME that some developers might have overlooked in the MySQL manual.
Common date errors that stick around often
Common date errors stick out very prominently. By this I mean that you might not be able to figure out why it is happening but it will give a huge blow to your program. In my article PHP-MySQL date format, I have demonstrated how you can compose a date in PHP for use in SQL. If you compose a time like this “10:39:12” and try to store it in a MySQL field with data type DATE. Any idea what happens? Your database stores a value “0000-00-00” for you.
Now, let us see why this happens. MySQL allows you to send date value as string and there you can separate month, year etc. with any delimiter. So, when you submit the above data to the database the value is treated as date components and they are changed to “0000-00-00” because the value 39 does not symbolize month value. This is one among many common date errors.
Servers normally want you to provide the month and date values perfectly. Unless you do this, it’s a rule violation and they penalize you. You again get a “0000-00-00” stored in your database. Dates such as “2013-02-30” are considered invalid. You just can supply any arbitrary value between 1-12 and 1-31. The server monitors the data sensibly.
Common date errors – some more
TIMESTAMP in MySQL also has some specific error criterion. Whenever you feed in value to a TIMESTAMP field in the database, remember this that TIMESTAMP does not allow the day and month values to be zero when a new record is being added. You would only see these two fields zero when the whole value is nil – “0000-00-00 00:00:00”.
Year values when mentioned in two digits cause an ambiguity for the MySQL server. So, they have a technique of their own to solve two digit year value issues. Whenever they encounter a two digit value, if the value is more than 0 and less than 69 it changes to 20s (suppose 13 is supplied, it changes to 2013). Otherwise it changes to19s (so for 70 it becomes 1970).
These errors are very common and they are the basic components of MySQL debugging. So, always keep these handy while you code. For more information on MySQL dates and date data types you can try reading Working with MySQL date type.