A function as straightforward as entering dates in a MySQL database should be nearly automatic, but the process is anything but foolproof. MySQL’s handling of invalid date entries can leave developers scratching their heads. In particular, the globalization of IT means you’re never sure where the server hosting your database will be located — or relocated. Plan ahead to ensure your database’s date entries are as accurate as possible.
DBAs know that if they want their databases to function properly, they have to follow the rules. The first problem is, some “rules” are more like guidelines, allowing a great deal of flexibility in their application. The second problem is, it’s not always easy to determine which rules are rigid, and which are more malleable.
An example of a rule with some built-in wiggle room is MySQL’s date handling. Database Journal’s Rob Gravelle explains in a September 8, 2014, post that MySQL automatically converts numbers and strings into a correct Date whenever you add or update data in a DATE, DATETIME, or TIMESTAMP column. The string has to be in the “yyyy-mm-dd” format, but you can use any punctuation to separate the three date elements, such as “yyyy&mm&dd”, or you can skip the separators altogether, as in “yyyymmdd”.
So what happens when a Date record has an invalid entry, or no entry at all? MySQL inserts its special zero date of “0000-00-00” and warns you that it has encountered an invalid date, as shown below.
Only the first of the four Date records is valid, so MySQL warns that there is an invalid date after entering the zero date of “0000-00-00”. Source: Database Journal
To prevent the zero date from being entered, you can use NO_ZERO_DATE in strict mode, which generates an error whenever an invalid date is entered; or NO_ZERO_IN_DATE mode, which allows no month or day entry when a valid year is entered. Note that both of these modes have been deprecated in MySQL 5.7.4 and rolled into strict SQL mode.
Other options are to enable ALLOW_INVALID_DATES mode, which permits an application to store the year, month, and date in three separate fields, for example, or to enable TRADITIONAL SQL Mode, which acts more like stricter database servers by combining STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER.
Avoid using DATETIME at all? Not quite
Developer Eli Billauer posits on his personal blog that it is always a mistake to use the MySQL (and SQL) DATETIME column type. He qualifies his initial blanket pronouncement to acknowledge that commenters to the post give examples of instances where use of DATETIME is the best approach.
Billauer points out that many developers use DATETIME to store the time of events, as in this example:
Using the DATETIME and NOW() functions creates problems because you can’t be sure of the local server’s time, or the user’s timezone. Source: Eli Billauer
Because DATETIME relies on the time of the local server, you can’t be sure where the web server hosting the app is going to be located. One way around this uncertainty is to apply a SQL function that converts timezones, but this doesn’t address such issues as daylight savings time and databases relocated to new servers. (Note that the UTC_TIMESTAMP() function provides the UTC time.)
There are several ways to get around these limitations, one of which is to use “UNIX time,” as in “UNIX_TIMESTAMP(thedate).” This is also referred to as “seconds since the Epoch.” Alternatively, you can store the integer itself in the database; Billauer explains how to obtain Epoch time in Perl, PHP, Python, C, and Javascript.
Troubleshooting and monitoring the performance of your MySQL, MongoDB, Redis, and ElasticSearch databases is a piece of cake when you use the Morpheus database-as-a-service (DBaaS). Morpheus provides a single, easy-to-use dashboard. In addition to a free full replica set of each database instance, you get backups of your MySQL and Redis databases.
Morpheus is the first and only DBaaS to support SQL, NoSQL, and in-memory databases. The service’s SSD-backed infrastructure ensures peak performance, and direct links to EC2 guarantee ultra-low latency. Visit the Morpheus site to create a free account.
