[DB-SIG] Date/Time confusion in spec

[M.-A. Lemburg]

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

The ticks value itself is based on UTC, but when
converting from and to ticks, the time zone you are worried
about is the one that is used in the conversion, not the
one that happens to be used for the definition of the
underlying value.

So, agreed, ticks do refer to UTC and the epoch is
also defined in terms of UTC, but the user is usually
not interested in that detail, but rather in what time zone
is being used by whatever mechanism is using ticks
for calculation or representation purposes.

That said, it's always better to store date/time values
as UTC in a database, since it avoids all the time
zone confusion that local time introduces.

> 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 :-)

In that case I'd just you the ISO string representation
to check date/time values (forget about the seconds fraction,
BTW, these are highly non-portable between DB backends).

_______________________________________________
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 ! ::::