4
votes

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.

1
I have <code>detect_types=sqlite3.PARSE_DECLTYPES</code> set btw - Dev Kanchen
This seems to be an issue with 'functions' in general. I tried this - SELECT (CURRENT_TIMESTAMP - maindb_last_checked) FROM tbl_dbsync_meta. And this returned me a unicode string too, rather than a DATETIME object. Whereas if I select just an existing TIMESTAMP field - such as "maindb_last_checked" - it returns a datetime object.......Is that the issue? - Dev Kanchen
How did you store the Python datetime objects into the timestamp columns? How are they converted? Please post some Python code, too. - user647772
@Tichodroma, that's a great question. I'm not actually storing Python datetime objects ever directly into sqlite. All I ever do is update these timestamps with a "UPDATE tbl_dbsync_meta SET global_scores_last_checked = CURRENT_TIMESTAMP". So the date-time values are all internal to SQLite. - Dev Kanchen

1 Answers

2
votes

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