[odb-users] dates and times in sqlite3

[Dave Johansen]

On Thu, Jun 12, 2014 at 3:08 AM, Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Hi,
>
> MM <finjulhich at gmail.com> writes:
>
> > By default, *date* converts to extended iso string like so YYYY-MM-DD,
> > and *time_duration* converts to HH:MM:SS.fffffffff were fff is fractional
> > seconds that are only included if non-zero.
> >
> > For both, the default column type is TEXT.
>
> Yes, SQLite does not have date-time data types. These representations,
> however, are pretty standard. See this page, for example:
>
> http://www.sqlite.org/lang_datefunc.html
>
>
> > 1. for date, is this compatible with the default pysqlite3 converter for
> > datetime.date?
>
> Maybe someone else on the mailing list will be able to answer this,
> but generally, this mailing list is a wrong place to ask what a Python
> default converter might do. The relevant Python mailing list would be a
> much more sensible place.
>
> Boris
>
>
I'm probably a little late to this conversation and don't know if a change
like this is even possible in the ODB codebase, but all the uses of sqlite
that I've worked with store date/time in Julian Day/Date format (
http://en.wikipedia.org/wiki/Julian_day ) so that it's smaller and easier
to work with (- and + just work and have intuitive results). sqlite3 also
knows how to convert to/from Julian Day/Date and string formats (
http://www.sqlite.org/lang_datefunc.html ).