[DB-SIG] Date/Time confusion in spec

[M.-A. Lemburg]

Stuart Bishop wrote:
> Hi.
> 
> The DBAPI 2.0 spec does not mention if the parameters to
> the Date(), Time() or Timestamp() constructors wants times
> in the local time zone, in UTC, or a naive time.

Right, because this depends on what the program and/or
database uses as time zone. It's an application scope
issue, not an interface issue.

> DateFromTicks(), TimeFromTicks() and TimestampFromTicks()
> use epoch time, which is UTC.

There's no correspondence between epoch time and a time zone
such as UTC. You are right in that the spec is unclear at
this point. It does not say which time zone should be used
when converting the ticks value to a date/time value.

Traditionally, though, this has always been the local time
zone. Perhaps we should make that explicit in the spec ?!

> This seems an important point. If Timestamp() wants UTC or local
> time, it is all unambiguous if we know which. If Timestamp() is
> timezone naive, then either the backend needs to be able to support
> both naive and TZ aware timestamps, or the *FromTicks() constructors
> need to convert the ticks to the local time zone. Does this sound
> correct?

Yes.

> Currently there are constructors for creating DATETIME objects.
> However, there is no way of converting back into some known format.
> So although I can insert timestamps into a database without knowing
> about the drivers particular DATETIME implementation (mxDateTime?
> Python datetime? An ISO8601 string?), I can't retrieve and process
> them.
> 
> A sane way of addressing this would be another constructor be added
> to the API, taking a driver specific DATETIME and returning something
> known (eg. epoch time or a tuple as per the standard time module).

Well, all formats can be converted to strings and you can
then take it from there, but I agree that the situation is
not all that clear.

The problem with this is that you really don't want to
have to fiddle with all rows in a result set just to get
them to use known data types. I'm using a special attribute
in mxODBC to let the user set the data type that he wants
to see for date/time values. That's usually more practical
than having to filter all result sets...

_______________________________________________
DB-SIG maillist  -  DB-SIG at python.org
http://mail.python.org/mailman/listinfo/db-sig

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Oct 09 2003)
 >> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::