sqlite - python Retrieve CURRENT_TIMESTAMP as datetime object

Question

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.

I have <code>detect_types=sqlite3.PARSE_DECLTYPES</code> set btw — Dev Kanchen
– Dev Kanchen, Commented Aug 7, 2012 at 6:28
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
– Dev Kanchen, Commented Aug 7, 2012 at 8:00
How did you store the Python datetime objects into the timestamp columns? How are they converted? Please post some Python code, too. — user647772
– user647772, Commented Aug 7, 2012 at 8:33
@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
– Dev Kanchen, Commented Aug 7, 2012 at 9:49

egbokul · Accepted Answer · 2012-08-07 13:26:53Z

2

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

answered Aug 7, 2012 at 13:26

egbokul

3,9747 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dev Kanchen Over a year ago

This is (literally) years later, and I'm sorry I can't recall why I didn't respond to this answer at the time - couple of doubts. First, what would be the difference between "SET column_name = CURRENT_TIMESTAMP" and then reading that column, versus "SELECT CURRENT_TIMESTAMP"? Because I got a date-time object in the first case. Could this simply be a bug like you mentioned? Second question - my SQL syntax knowledge is rusty - in "ts [timestamp]" is "timestamp" supposed to indicate the datatype of the "virtual" "ts" column returned in the query?

Collectives™ on Stack Overflow

sqlite - python Retrieve CURRENT_TIMESTAMP as datetime object

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related