Why do two MySQL dates in 1582 appear to be the same but comparison result is false?

Question

I know that Gregorian calendar started on Oct 15th 1582, and during the transition from Julian calendar, 10 days had been dropped.

When I'm doing this query:

SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d')

I'm getting this result:

1582-10-15 (the 10 days difference). 

But when I'm trying to match between such dates I'm getting the original date (Oct 5th and not 15th).

For example:

SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d') = STR_TO_DATE('1582-10-15', '%Y-%m-%d')

I'm getting a false response, although you would have expected to get a true since Oct 5th actually count as Oct 15th, as we saw in the first example.

Anyone can explain what's going on here?

Hypesthesia

Are you running the command in MySQL, or something else? MySQL uses a proleptic calendar so it should just show whatever value you enter into it (it does on my machine, and it does on rextester). On sqlfiddle it returns the 15th - but i can't help wondering if that isn't down to some internal processing not related to MySQL. Incidentally, in the UK the Gregorian calendar was adopted in September 1752, and in Russia I think it was at the October (so actually November) revolution – Nickolai

@Strawberry, I'm using MySQL on Amazon's EC2 instance. I'm connecting through DataGrip (not that's important). I tried it on Redshift as well and got the same results. On rextester I'm not seeing the 10 days difference. – Hypesthesia

dev.mysql.com/doc/refman/5.7/en/mysql-calendar.html -- SELECT TO_DAYS('1582-10-15') - TO_DAYS('1582-10-04'); gives me 11; should be 1? – Sienkiewicz

Answer 1

On documentation it is stated that, TO_DAYS and FROM_DAYS functions must be called cautiously because of the transformation you noticed. Additionally, when I inspect the source codes of MySQL, I realized that STR_TO_DATE uses similar methodology with these functions. As I understand, cutover dates are completely unsafe to store or apply operations. Documentation says; "Dates during a cutover are nonexistent.", too.

Also for the inconsistency between different servers I may have an explanation. I have 2 different machines which have MySQL installed in Istanbul, Turkey and Frankfurt, Germany. They have same setup excluding localisation settings. First one shows 1, the other one shows 11 for the date substraction query. It means (in my humble opinion) there is unexplained sections about calendar cutover & localisation on official documentation.

Cheque

Answer 2

Please see the following results:

SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d');
# Result #1: 1582-10-15

SELECT DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d');
# Result #2: 1582-10-05

SELECT DATE_FORMAT(STR_TO_DATE('1582-10-15', '%Y-%m-%d'), '%Y-%m-%d');
# Result #3: 1582-10-15

SQL fiddle demo.

These indicate the problem lies in the way the 1582-10-05 date is displayed rather than how it is stored. Result #2 shows that if the DATE_FORMAT function is used instead to explicitly convert the date into the same string format then the input date is displayed. This also explains why the comparison query in the question returns false: Behind the scenes, the two stored dates are different.

As you've discovered, this quirk occurs for all dates between 1582-10-05 and 1582-10-14 inclusive, i.e. the range of dates that don't really exist: The implicit conversion to text for all of these gives a date 10 days after. So if for some reason there is a need to display dates in this range (perhaps questionable), a simple workaround is to always use the DATE_FORMAT function.

Bedroom

Thanks for the explanation. Just to make sure I understand your point. You claim that there is a bug in mySql which cause it to show 1582-10-05 as 1582-10-15 (on STR_TO_DATE function) but behind the scenes it's still 1582-10-05? – Hypesthesia

Pretty much, although I'd hesitate to use the word "bug" as their documentation does mention that the dates in the mentioned range are (a) non-existent in the calendar used by MySQL and (b) unsafe to use (as per Tuğca Eker's answer). – Bedroom

Another example is this: SELECT DATE_ADD(DATE_ADD(STR_TO_DATE('1582-10-04', '%Y-%m- %d'), INTERVAL 1 DAY), INTERVAL 1 DAY) which will give you a result of 1582-10-16 . Hence 1582-10-05 was actually treated just like 1582-10-15 – Hypesthesia

Looks like this has the same explanation: The dates are correct behind the scenes so the calculated answer is 6th October but MySQL is displaying this as 16th October. Try DATE_FORMAT(DATE_ADD(DATE_ADD(STR_TO_DATE('1582-10-04', '%Y-%m- %d'), INTERVAL 1 DAY), INTERVAL 1 DAY), '%Y-%m-%d') and you'll see that it results in 6th October. – Bedroom

Write a bug report asking for a clarification in the documentation. Use the above comments as a basis. bugs.mysql.com – Sienkiewicz