[DB-SIG] Date/Time confusion in spec

[Stuart Bishop]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday, October 9, 2003, at 07:01  PM, M.-A. Lemburg wrote:

> Stuart Bishop wrote:
>> 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.

According to the Python Reference guide (which is where the DB API
spec says its definition is), epoch time is UTC. It defines the
start of the epoch as time.gmtime(0).

And if TimeFromTicks() is local time, then
Timestamp(1970,1,1) != TimestampFromTicks(0) unless TZ=UTC

Which mirrors gmtime(0) != localtime(0) unless TZ=UTC

If this is not the way existing drivers have been implemented, then
we will need to change the definition of ticks being used.

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

+1

>> 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.

This looks promising - thankfully both mxDateTime and the Python 2.3
datetime module use the same string representation so this is fine.
Anyone know if there are any drivers that have a different string
representation (yyyy-mm-dd hh:mi:ss.ssssss)? If none, it would be
good if this could be made explicit too.

> 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...

I'm adding tests to my DB API 2.0 compliance test suite, so
practicality is unnecessary in my case :-)

- -- 
Stuart Bishop <stuart at stuartbishop.net>
http://www.stuartbishop.net/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (Darwin)

iD8DBQE/hTo3AfqZj7rGN0oRAuJQAJ4gdlWoNT8+iwmzT0xCLNEzBkvcKgCgkDO8
k6P4GeNOsJ37BSAnbE/hRQ8=
=e9Kf
-----END PGP SIGNATURE-----