sqlite3 and dates

[Chris Angelico]

On Wed, Feb 18, 2015 at 5:19 PM, Frank Millman <frank at chagford.com> wrote:
> However, the following does not return a date object -
>
>>>> cur.execute('SELECT CAST(? AS DATE)', ('2015-03-31',))
> <sqlite3.Cursor object at 0x00FE9BE0>
>>>> cur.fetchone()
> (2015,)
>>>>
>
> I don't know how easy this would be to implement, but it would be nice if it
> could be made to work.

Heh! Looks like the date is implemented as a slightly magical integer,
so "cast to date" becomes "cast to integer" and you just get back
2015. Could be really easy to fix, could be nigh impossible... but
sure, that seems a reasonable thing to ask for. Worst case, you get
told it's not practical.

But if you need more facilities than SQLite3 can offer, maybe it's
time to move up to a full database server, instead of local files.
Switching to PostgreSQL will give you all those kinds of features,
plus a lot of other things that I would have thought pretty basic -
like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't
support that.

ChrisA