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](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-
functions.html#function_unix-timestamp), would accomplish this in MySQL, but
for dates before 1970, it always returns zero.

The [TO_DAYS MySQL function](http://dev.mysql.com/doc/refman/5.1/en/date-and-
time-functions.html#function_to-days), 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: 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.

