MySQL has unfortunate rules for dates with two-digit years, for the date range, for post-decimal digits, for timestamp defaults ... But other DBMSs can be equally bizarre
Start with two-digit years. Summarizing from the manual:
If the two-digit value is For example, if it's 65, then it's 2065.
It's an arbitrary extra detail that users have to learn. But PostgreSQL has exactly the same rule. And three other DBMSs -- DB2, Oracle, Firebird -- have an even more complex rule, saying in effect that the interpretation will vary depending what year it is when you do the input. SQL Server is even worse, it allows users to change the rule slightly. Only the SQL standard has a rule that's easy to learn and never changes: you have to enter four-digit years. Time for a modernized-English Shakespeare quote:
Hamlet: Why was he sent into England?
First Clown: Why, because he was mad. He shall recover his wits there, or, if he does not, it is no great matter there.
First Clown: It will not be seen in him there. There the men are as mad as he.
Applying the clown's logic, we see that MySQL's behaviour, in context, is no great matter.
Different DBMSs have different minimum dates, though they mostly have the same maximum.
Minimum Maximum Oracle 12c(timestamp) 4713-01-01 BC Julian 9999-12-31 SQL Server 2012 (datetime) 1753-01-01 AD Gregorian 9999-12-31 SQL Server 2012 (datetime2) 0001-01-01 AD Gregorian 9999-12-31 DB2 9 (timestamp) 0001-01-01 AD Gregorian 9999-12-31 Firebird 0100-01-01 AD 32768-02-29 PostgreSQL (timestamp) 4714-11-24 BC Gregorian 294276-12-31 MySQL (datetime) 1000-01-01 AD Gregorian 9999-12-31
At first glance it looks as if MySQL is out of step here -- everybody else can store dates before 1000 AD. (SQL Server was out of step too, until Microsoft
introduced the datetime2 data type in 2008.) But actually MySQL can store dates before that. Just don't try calculating on them, goes the recommendation. However, Oracle, and Oracle alone, switches to the Julian calendar for dates before October 1582. I think that's becoming the conventional thing to do, even though (sob) it's not standard SQL. Therefore, yes, MySQL is out of step, but so is everybody else except Oracle.
MySQL version 5.6.4 saw the implementation of WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds. Once again Oracle has hidden the task description but once again it's visible on wayback (as always, click "High Level Architecture" to see what the real spec is). In this case the implementation does not match the original spec. MySQL only supports 6 post-decimal digitsr (microseconds), and the same is true for MariaDB.
How bad is that? Let's compare:
Maximum Fraction DB2 9.7 .999999999999 Oracle 12c .999999999 SQL Server 2012 .99999999 PostgreSQL 9.3 .999999 MySQL 5.6 .999999 Firebird .9999
I look at that comparison and exclaim "MySQL is behind the Big Three Closed-Source DBMSs". But I acknowledge that others could look at the same comparison and shrug "Oh well, it's as good as the other open-source DBMSs". Merging the opinions, I'll say: post-decimal digit support is not good but, since 5.6.4, not bizarre.
Yes, but what about the fact that, in MySQL, the date 0000-00-00 is legal but becomes NULL if you retrieve it via ODBC or if you assign NULL to a TIMESTAMP NOT NULL, the result is the current timestamp and other equally odd-looking rules?
Here I can't defend MySQL on the basis that some other DBMS does it. These are MySQL innovations. And I have never heard somebody from outside MySQL say that they have been helpful in a serious project. I can only say, well, at least they're avoidable.
The claim is just that MySQL date support is not bizarre, in relative terms. The other DBMSs have quirks too. It's perhaps inevitable in this imperfect world which refuses to go around the sun in a regular and divisible-by-10 number of days.