SQLite date fields

[Duncan Booth]

Tim Roberts <timr at probo.com> wrote:

>>However, when it comes to writing-back data to the table, SQLite is
>>very forgiving and is quite happy to store '25/06/2003' in a date
>>field, 
> 
> SQLite is essentially typeless.  ALL fields are stored as strings,
> with no interpretation.  You can store whatever you want in any
> column.  The column types are basically there to remind YOU how to
> handle the data. 
> 

Not all fields are stored as strings; they may also be stored as 
integer, floating point values or binary data.

If you create a table with a column of type DATE or DATETIME then the 
column affinity is NUMERIC. See http://www.sqlite.org/datatype3.html

It happens that Python stores datetime values in sqlite databases as 
text strings by default, but that means if you actually declare the 
column as DATE or DATETIME you are overriding the default type for each 
column. Not that it is normally noticeable but:

>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> c = conn.cursor()
>>> c.execute('''CREATE TABLE t1 (d DATE, t TEXT);''')
<sqlite3.Cursor object at 0x02A18350>
>>> from datetime import datetime
>>> d = datetime.now()
>>> c.execute('INSERT INTO t1 (d,t) VALUES (julianday(?),julianday(?))', 
(d, d))
<sqlite3.Cursor object at 0x02A18350>
>>> for row in c.execute('SELECT * FROM t1'):
	print(row)

	
(2455527.0482040276, '2455527.04820403')
>>> for row in c.execute("SELECT datetime(d), datetime(t) FROM t1"):
	print(row)

	
('2010-11-26 13:09:24', '2010-11-26 13:09:24')


-- 
Duncan Booth http://kupuguy.blogspot.com