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

[Magnus Lycka]

At 16:11 1997-12-08 +0100, M.-A. Lemburg wrote:
>[Lots of code from Christian Egli]
>
>What an impressive list of date formats, thanks. I think we
>should (at first) stick to Gregorian and Julien dates for
>input/output methods. Your "absolute date" basis seems to be
>a reasonable internal format for the date integer. Time
>could be encoded as double expressing seconds since midnight.
>This is pretty accurate and allows for leap seconds etc.

In Sweden we often use week numbers of the ISO type, but only when
we refer to a particular week, not a specific day. For instance,
a reasonably complete Swedish version of the unix cal(1) program
would look more or less like this:

$ cal 12 1997

       December 1997
 Vecka M=E5 Ti On To Fr L=F6 S=F6
   49   1  2  3  4  5  6  7
   50   8  9 10 11 12 13 14
   51  15 16 17 18 19 20 21
   52  22 23 24 25 26 27 28
(98)1  29 30 31

Note that ISO week numbers are neither %U nor %W in the unix date(1)
command, but week 1 is the first week that has at least 4 days in
January. In other words, week 1998-01 starts in december 1997, and
week 1998-53 ends in January 1999.

For me, this doesn't feel like it should be a class of it's own though.
A date is a date is a date. I'd just like a method to extract the year/week
number from a gregorian date, but maybe I just haven't gotten used to=20
the idea of making this an independent class. Either way, I don't think
this has a lot to do with the DBI. As long as the DBI supplies a consistent
date type or class I can have what ever methods I want in external (locale)
classes. Maybe those classes should just have generic get/set functions =E0=
 la
unix date(1) or C's strftime with a default set through locale. (Maybe we
could just add ISO week as %v. Then I would be happy, but the problem is=
 that
around the new year %Y and %y might show the wrong year, so we need two more
year flags, or %v returning 199750 this week.)

I suppose it's understandable that for instance Oracles SQL which has=
 evolved
into a more extensive programming language and is used for ad hoq generation
of reports etc support advanced date formatting, but if we write programs
in a full language like Python we have no reason to mix data entry or=
 display
functionality with storage. In other words, lets have nice date formatting
functions, but not in the DBI.

To be honest I don't care a bit about dates several hundred years ago,
so for me, we can skip Julian dates. If there are people who are interested
in Arabic, Hebrew, Buddhist calendars, fine, they are in use, but Julian
dates are mainly useful for time span that is far outside any relevant use
for administrative systems, but far to tiny to be useful for geologists etc.
(On the other hand, if Julian here should be interpreted as in unix (%j) I
agreee that that can be a useful way of handling Gregorian calendar dates.)

Again, this is really not an issue for the DBI though. I agree that dates
(and timestamps) could be standard Python types.

I want my databases (and database interfaces) to recognize a date format
and a timestamp format. I don't see a point in time-interval formats, as
Hannu mentioned, and I haven't seen them in the databases I've used. In
for instance Informix there is some kind of time interval format, but what
that means is that you can decide what the resoultion and span of the type
should be, for instance from minutes to hours, meaning that the smallest
value you can store is 1 minute, and the largest is 23 hours and 59 minutes.

I want to be able to get an integer representing days by subtracting a date
from another, and by adding or subtracting an integer to/from a date I want
to get another date.=20

I want to be able to do corresponding things with timestamps, but I'm not
sure what types I want here. I think the suggestion above is OK. Maybe we
could simply decide that if an integer is added to or subtracted from a date
or timestamp, it's a number of days, and if the same thing is done with a=20
float, it's a number of seconds. That might not be extremely intuitive, but=
=20
it's simple.

Converting to dates from hand entered strings in all sorts of formats is
a big mess that should be kept out of a database interface. For instance
3/4/5 might at least mean three things (if we agree on which century we
refer to): 3 Apr 2005, 4 Mar 2005 or 5 Apr 2003. This should be handled
by L=F6wis locale stuff.


	Magnus

--
Magnus Lycka, S/W Engineer, M.Sc.E.E; Folktrov. 6C, 907 51 Umea, Sweden
Tel: +46(0)90 198 498, GSM: +46(0)70 582 80 65, Fax: +46(0)70 612 80 65
<mailto:magnus.lycka@tripnet.se>         <http://www1.tripnet.se/~mly/>


_______________
DB-SIG  - SIG on Tabular Databases in Python

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