Date-Time type (was Re: [DB-SIG] DB-API Spec. 1.1a1)

[Bill Tutt]

> -----Original Message-----
> From:	M.-A. Lemburg [SMTP:lemburg@uni-duesseldorf.de]
> 
> > > There are basically two options:
> > >
> > >       1. use GM time (AKA Zulu time AKA UTC) as internal basis
> > >          and have some external method deduce the DST & time zone
> > >       2. store whatever the user gives to the object and return
> > >          it in the same way
> > >
> > No, there is only 1 option...
> > #2, store whatever the user gives us. It is the user's problem to go
> about
> > sorting out timezones and DST nonsense.
> 
> Hmm, note that option #1 can emulate option #2 if handled
> properly (ie. the conversion routines work right). #1 has the
> advantage of also allowing proper time calculations, which
> certainly have a use in other contexts apart from DB interfacing
> too.
> 
No, not really... people are much better off dealing with this themselves or
having an abstraction layer on top of the date/time type deal with this. The
job of this date/time type is to be as simple as possible, and certainly not
complicated... One may suggest and hint that people should always store
date/times as UTC Time/Timezone,
but one shouldn't present default conversions for these things.  In fact,
AFAIK most RDBMSs store datetimes blindly.
We should do the same.
A sane Python based date arithmetic package will be able to do the same
thing and be very clear whats happening. 


> Local times without DST won't have to use the C routines.
> UTC will also always work. The only thing to watch out
> here are leap seconds when calculating time differences
> in seconds.
> 
The date/time module won't be calculating time differences...
Thats somebody elses job..

> > > Conversion from date/time to strings is left to strftime(). The
> > > other way around could be handled by an elaborate module written
> > > in Python (something a la Skip's date.py) -- it's much too messy
> > > to get written in C.
> > >
> > Actually we should supply strptime() in C, esp. since the source is
> already
> > availible.
> > Easiest way to do that is to snarf the BSD libc functions from say:
> > ftp://ftp.freebsd.org/.25/FreeBSD/FreeBSD-current/src/lib/libc/stdtime
> > and modify as appropriate.
> > Locale stuff can be handled with OS's that support localeconv()
> > We can #ifdef the approriate sections if they haven't already.
> 
> That's a new one right ? I've never heard of it until now.
> My manpage says:
> 
>        This function is only available in  libraries  newer  than
>        libc version 4.6.5
> 
> I guess we could provide a version of our own here too.
> 
You're correct it is very new on UNIX boxes.. we should supply our own.
Using the BSD version is suggested because of copyright & reuse issues.

> > 
> > So all the C date/time module has to do is:
> > 1) Expose the following date/time type methods
> >         a)  Construction: taking a # of days since Sunday, December 31,
> 1
> > BC
> >              and the # of seconds past midnight
> >         b)  method to return the # of days, or the # of seconds
> >         c)  a method that returns a struct tm like tuple like
> localtime()
> > returns
> 
> Make that gmtime(). BTW: the tuple doesn't have a field for
> microseconds. Should we simply make the second entry a float to
> also hold the fraction part ?
> 
Err. yeah thats what I meant.  (The important part being that the DST field
MUST be 0, a -1 or a 1 WILL NOT be allowed.)

> I'm already working on this -- it'll also have methods/variables
> for day-of-week, day-of-year, year-offset (# days between 31.12.-1 and
> 1.1.
> of the represented year), flag is-leapyear and maybe one or two
> more. Everything is assumed to be UTC and Gregorian. It is the users
> responsibility to pass appropriate values (this can be misused to
> implement option #2 above).
> 
There isn't a need to assume UTC, let other code assume that.. 
We do need to assume Gregorian for converting into/outof our type though.
We don't need to do the leap year caclulation though...
The rest of the information should probably be cached me thinks...

> >         d)  a method that returns a float where the fraction part is:
> #secs
> > in a day/#seconds in this instance
> 
> That's the OLE date right ? I'll add that too.
> 
You got it..
I can toss you code for converting from the double -> TIMESTAMP_STRUCT
and struct tm -> double based off of MFC's routines that do similar things.]

> > 2) Expose strptime() that takes a format string, a string to attempt to
> > parse, and returns a struct tm like structure
> >     Possibly raising an exception if the string doesn't parse correctly?
> 
> Yep.
> 
We should at least exposes the CRT's strftime() for the sake of completeness
if not including our own version of that as well.. (That way we can garuntee
that things will come out ok for years prior to 1900)

> > 3) Method that takes a struct tm like tuple and generates a date/time
> type.
> 
> I plan to add a function for this. It will take the tuple
> as input and return a correctly set date/time instance.
> The entries are interpreted as being Gregorian dates and UTC time.
> 
No, no, no, bad, bad, bad.... :)
Let other bits of code worry about this timezone nonsense...

> > 
> > Everything else should live in Python code.
> 
> Agreed.
> 
> > The ODBC glue code can thusly call dbi.makeDateTime(#days, #secs) (or
> > whatever the exact name of it is)
> > The DBI module can then call the date/time type's construction function.
> 
> Right and on output (cursor.execute) it will return instances
> of the date/time-type. The user can then stick these into 
> calendar classes of his/her choice to have them converted
> to their timezone or special format.
> 
Yeppers

> > An interesting question in date/time types which there prolly is no
> RIGHT
> > answer is:
> > If my datetime type doesn't store month/day/year information should they
> be
> > cached calculations, or should I calculate
> > them each time?
> 
> Hmm, the date/time-types are immutable, so caching the output
> from 1.c) is possible. If think I could also expose the
> tuple as instance variables: day, month, etc.
> 
> Could someone point me to a URL where I can find the already
> inserted leap seconds ?
> 
I'll forward you those 2 C routines later today after I get some sleep.
That should take care of most of those annoying details I think...

> Does anyone know if we can make the new type pickleable by
> providing the __getinitargs__-method ? I've never done
> this, but it should work that way.
> 
cPickle is supposed to have a mechanism for pickling extension types that
would probably be fairly easy to accomplish... (Convert the type to a Python
tuple containing the # of days, and # of seconds and pickle that, I think)


Bill Tutt
billtut@microsoft.com
Not speaking for Microsoft, etc..


_______________
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
_______________