Handling dates prior to 1970 in a repeatable way in MySQL and Python

Question

In my MySQL database I have dates going back to the mid 1700s which I need to convert somehow to ints in a format similar to Unix time. The value of the int isn't important, so long as I can take a date from either my database or from user input and generate the same int. I need to use MySQL to generate the int on the database side, and python to transform the date from the user.

Normally, the UNIX_TIMESTAMP function, would accomplish this in MySQL, but for dates before 1970, it always returns zero.

The TO_DAYS MySQL function, also could work, but I can't take a date from user input and use Python to create the same values as this function creates in MySQL.

So basically, I need a function like UNIX_TIMESTAMP that works in MySQL and Python for dates between 1700-01-01 and 2100-01-01.

Put another way, this MySQL pseudo-code:

select 1700_UNIX_TIME(date) from table;

Must equal this Python code:

1700_UNIX_TIME(date)

Answer 1

I don't have MySQL here installed, but when I look here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_to-days - I see an example TO_DAYS('2008-10-07') returning 733687.

The following Python function returns datetime(2008,10,7).toordinal() = 733322, which is 365 less than the MySQL's output.

So take this:

from datetime import datetime

query = '2008-10-07'
nbOfDays = datetime.strptime(query, '%Y-%m-%d').toordinal() + 365

and it should work for dates between 1700 and 2100.

Answer 2

According to the link that you gave,

Given a date date, returns a day number (the number of days since year 0).

mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321

Corresponding numbers in Python:

>>> import datetime
>>> datetime.date(1995,5,1).toordinal()
728414
>>> datetime.date(2007,10,7).toordinal()
732956

So the relationship is : mySQL_int == Python_int + 365 and you can convert in the other direction by using the fromordinal class method:

>>> datetime.date.fromordinal(728779 - 365)
datetime.date(1995, 5, 1)