SQLite date fields

[Alan Harris-Reid]

Hi,

I am having design problems with date storage/retrieval using Python and
SQLite.

I understand that a SQLite date column stores dates as text in ISO
format (ie. '2010-05-25'). So when I display a British date (eg. on a
web-page) I convert the date using
datetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y').

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, but
this is not ideal because a) I could be left with a mixture of date
formats in the same column, b) SQLite's date functions only work with
ISO format.
Therefore I need to convert the date string back to ISO format before
committing, but then I would need a generic function which checks data
about to be written in all date fields and converts to ISO if
necessary. That sounds a bit tedious to me, but maybe it is inevitable.

Are there simpler solutions? Would it be easier to change the date
field to a 10-character field and store 'dd/mm/yyyy' throughout the
table? This way no conversion is required when reading or writing from
the table, and I could use datetime() functions if I needed to perform
any date-arithmetic.

How have other developers overcome this problem? Any help would be
appreciated. For the record, I am using SQLite3 with Python 3.1.

Alan

 

[CM]

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, but
this is not ideal because a) I could be left with a mixture of date
formats in the same column,  b) SQLite's date functions only work with
ISO format.
Therefore I need to convert the date string back to ISO format before
committing, but then I would need a generic function which checks data
about to be written in all date fields and converts to ISO if
necessary.  That sounds a bit tedious to me, but maybe it is inevitable..

It doesn't strike me as particularly tedious if it is just the line:
datetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y')

Are there simpler solutions?  Would it be easier to change the date
field to a 10-character field and store 'dd/mm/yyyy' throughout the
table?  This way no conversion is required when reading or writing from
the table, and I could use datetime() functions if I needed to perform
any date-arithmetic.

Maybe I've misunderstood, but wouldn't you have to do the conversion
to
display the date anyway? It seems to me like, whichever approach you
take, you will have to do a conversion. For that reason, isn't it
better to leave the date fields in ISO so that you can take advantage
of SQLite's date functions?

Che

 

[Tim Chase]

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

Where the test gets funky:

'baz')''')

[(u'foo', u'bar', u'baz')]


Reminds me a bit of this [1] thread.

-tkc

[1]
http://www.mail-archive.com/[email protected]/msg108200.html

 

[Alan Harris-Reid]

To all those who have replied on this thread - many thanks. It looks as
though I've got to look further into date objects, SQLite's native date
functions, detect_types, etc..

Regards,
Alan