在MySQL和Python中以可重复的方式处理1970年之前的日期 [英] Handling dates prior to 1970 in a repeatable way in MySQL and Python

问题描述

在我的MySQL数据库中，我可以追溯到1700年代中期，我需要以某种类似于Unix时间的格式将其转换为整数. int的值并不重要，只要我可以从数据库或用户输入中获取日期并生成相同的int即可.我需要使用MySQL在数据库端生成int，并使用python从用户那里转换日期.

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.

通常， UNIX_TIMESTAMP函数，可以在MySQL中完成此操作，但是对于1970年之前的日期，它始终返回零.

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

TO_DAYS MySQL函数也可以，但是我无法从用户输入中获取日期，也无法使用Python创建与该函数在MySQL中创建的值相同的值.

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.

因此，基本上，我需要一个类似UNIX_TIMESTAMP的函数，该函数可在MySQL和Python中工作，其日期介于1700-01-01和2100-01-01之间.

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.

以另一种方式输入此MySQL伪代码:

Put another way, this MySQL pseudo-code:

select 1700_UNIX_TIME(date) from table;

必须等于此Python代码:

Must equal this Python code:

1700_UNIX_TIME(date)

推荐答案

我这里没有安装MySQL，但是当我在这里查看时:

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.

以下Python函数返回datetime(2008,10,7).toordinal() = 733322，比MySQL的输出少365.

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

，它应该可以在1700年到2100年之间使用日期.

and it should work for dates between 1700 and 2100.

这篇关于在MySQL和Python中以可重复的方式处理1970年之前的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！