SQLite date fields


A

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
 
Ad

Advertisements

C

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
 
Ad

Advertisements

A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top