I am using Python to create an in-memory sqlite3 database with a timestamp column. When I use min() or max() on this column in my query, the column is returned as a string rather than a Python datetime object. I read a previous question on Stackoverflow that provided a solution for normal SELECT statements, but it doesn't work if max() or min() is used. Here's an example:
>>> db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
>>> c = db.cursor()
>>> c.execute('create table foo (bar integer, baz timestamp)')
<sqlite3.Cursor object at 0x7eff420e0be0>
>>> c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now()))
<sqlite3.Cursor object at 0x7eff420e0be0>
>>> c.execute('select * from foo')
<sqlite3.Cursor object at 0x7eff420e0be0>
>>> c.fetchall()
[(23, datetime.datetime(2010, 12, 14, 1, 15, 54, 685575))]
>>> c.execute('select max(baz) from foo')
<sqlite3.Cursor object at 0x7eff420e0be0>
>>> c.fetchall()
[(u'2010-12-14 01:15:54.685575',)]
I tried to cast the result to a timestamp but it only returns the year:
>>> c.execute('select cast(max(baz) as timestamp) from foo')
<sqlite3.Cursor object at 0x7eff420e0be0>
>>> c.fetchall()
[(2010,)]
Is there any way to fetch a proper datetime object, without manually converting the string using datetime.strptime() after fetching it?
First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.
Date and Time Datatype. SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.
Overview. SQLite supports six date and time functions as follows: date(time-value, modifier, modifier, ...)
The fetchone returns the next row of a query result set, returning a single tuple, or None when no more data is available. In this script we connect to the database and fetch the rows of the cars table one by one.
You have to set detect_types to sqlite.PARSE_COLNAMES and use as "foo [timestamp]"
like this:
import sqlite3
import datetime
db = sqlite3.connect(':memory:', detect_types = sqlite3.PARSE_COLNAMES)
c = db.cursor()
c.execute('create table foo (bar integer, baz timestamp)')
c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now()))
c.execute('insert into foo values(?, ?)', (42, datetime.datetime.now() + datetime.timedelta(-1)))
c.execute('select bar, baz as "ts [timestamp]" from foo')
print c.fetchall()
c.execute('select max(baz) as "ts [timestamp]" from foo')
print c.fetchall()
Did a nice little Google search and found this message.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With