CODEWITHSUNDEEP
pythondatetimesqlitetimestamp
Dev Kanchen
Dev Kanchen

Reputation: 2352

sqlite - python Retrieve CURRENT_TIMESTAMP as datetime object

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

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)

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

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.

I tried casting CURRENT_TIMESTAMP:

SELECT CAST(CURRENT_TIMESTAMP AS TIMESTAMP), * FROM tbl_dbsync_meta

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

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.

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.

Thanks for your help.

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.

Upvotes: 4

Views: 3052

Answers (1)

egbokul
egbokul

Reputation: 3974

You need to set PARSE_COLNAMES when connecting:

sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_COLNAMES)

Then the following will work:

select current_timestamp as 'ts [timestamp]'

This will return "2012-08-07 13:20:42" (<type 'datetime.datetime'>).

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).

Upvotes: 2

Related Questions