sqlite - python将CURRENT_TIMESTAMP检索为datetime对象 [英] sqlite - python Retrieve CURRENT_TIMESTAMP as datetime object

问题描述

我使用本地sqlite数据库对我的应用程序中的元数据进行脱机处理，元数据包含时间戳。

I'm using a local sqlite database to do offline processing of metadata in my application and the metadata includes timestamps.

我有一个表声明如下： / p>

I have a table declared as follows:

CREATE TABLE if not exists tbl_dbsync_meta (
             maindb_last_checked TIMESTAMP,
             global_scores_last_checked TIMESTAMP,
             localscores_syncup_last_checked TIMESTAMP)

现在，在某一时刻，我检索到 CURRENT_TIMESTAMP 从sqlite以及上面的所有其他时间戳，以便自然地计算出现在和所有其他时间戳之间的时间差。

Now, at one point I retrieve the CURRENT_TIMESTAMP from sqlite along with all the other timestamps above, so as to - naturally - figure out the differences in time between now and all these other timestamps.

SELECT CURRENT_TIMESTAMP as this_time, * FROM tbl_dbsync_meta

现在的问题是，其他字段在Python中被正确地检索为datetime对象。但是， CURRENT_TIMESTAMP 作为字符串返回。

Now the problem is, that all the other fields are retrieved correctly as "datetime" objects in Python. However CURRENT_TIMESTAMP is returned as a string.

我尝试转换 CURRENT_TIMESTAMP ：

SELECT CAST(CURRENT_TIMESTAMP AS TIMESTAMP), * FROM tbl_dbsync_meta

但是现在它作为INTEGER返回，所有返回的都是2012年。

But now it's being returned as an INTEGER, and all that's returned is the year (2012).

一个简单的方法可以解决这个问题，就是直接从sqlite本身直接 SELECT 而不是将它们检索到python中，然后玩耍。另一个选择是让我使用 CURRENT_TIMESTAMP 返回的字符串，将其在Python中转换为datetime，并使用它。

An easy way for me to work around this is to just directly SELECT the time-diffs from sqlite itself, instead of retrieving them into python and then playing around. Another option is for me to use the string returned by CURRENT_TIMESTAMP, convert it in Python to a "datetime" and use that.

然而，我需要了解 CURRENT_TIMESTAMP （演员或其他）与其他 TIMESTAMP 字段之间的区别是的

However I need to understand WHAT the difference between CURRENT_TIMESTAMP (cast or otherwise) and the other TIMESTAMP fields is. It just seems wrong that this doesn't work.

感谢您的帮助。

PS：请注意我从来没有将Python datettime对象直接存储到SQLite中。任何时候，我需要更新这个表，我只是 SET global_scores_last_checked = CURRENT_TIMESTAMP 。我只是从SQLite中将日期从Python中检索出来。

P.S: Please note, I never store Python datettime objects directly into SQLite. Anytime I need to update this table, I just SET global_scores_last_checked = CURRENT_TIMESTAMP. I only retrieve the dates from SQLite into Python out of necessity.

推荐答案

你需要设置 PARSE_COLNAMES 连接时：

sqlite3.connect（'：memory：'，detect_types = sqlite3.PARSE_COLNAMES）

然后，以下内容将起作用：

Then the following will work:

select current_timestamp as'ts [timestamp]'

这将返回2012-08-07 13:20:42（<键入'datetime.datetime'>）。

CAST不起作用（很可能是一个错误），CURRENT_TIMESTAMP不返回datetime类型确实，这可能是一个错误或功能（记住，Sqlite没有日期/时间类型的概念，将它们存储为TEXT / REAL / INTEGER）。

CAST does not work (most probably a bug) and CURRENT_TIMESTAMP does not return a datetime type indeed, which might be a bug, or a feature (remember, Sqlite has no notion of date/time types, it stores them as TEXT/REAL/INTEGER).

这篇关于sqlite - python将CURRENT_TIMESTAMP检索为datetime对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！