[Zope3-dev] Dates and times

[Marius Gedminas]

On Thu, Nov 14, 2002 at 02:24:18PM -0500, Jim Fulton wrote:
> >We've now working on PsycopgDA type conversion for date/time fields.
> >Some of PostgreSQL types (DATE, TIMESTAMP, TIMESTAMPTZ) nicely map to
> >new Python datetime types (date, datetime, datetimetz).  The question is
> >what should we do with TIME and TIMETZ? 
> 
> Do you know how other Python database APIs handle this? What does
> Psycopg do with these.

PostgreSQL has the following date/time types:

  DATE, TIME, TIMESTAMP (= date + time) -- nothing fancy here
  TIMETZ, TIMESTAMPTZ -- TIME/TIMESTAMP + timezone information
  INTERVAL -- time delta

(There are also ABSTIME, RELTIME, TINTERVAL, but those are obsolete, may
disappear in future PostgreSQL versions, etc.)

Psycopg returns mx.DateTime instances for DATE, TIME and TIMESTAMP and
mx.DateTimeDelta for INTERVALs.  TIMEs are returned with 1970-01-01 for
the date part.  DATE/TIMESTAMP with value 'infinity' is translated to
999999-12-31 00:00:00, '-infinity' to -999998-1-1 00:00:00.

TIMETZ/TIMESTAMPTZ are treated like TIME/TIMESTAMP, and the timezone
info is just ignored.  I don't like that.

ZPsycopgDA has an option to use Zope2 DateTime class instead of
mx.DateTime.  In that case TIMEs are returned with today's date instead
of 1970-01-01, and INTERVALs are returned as strings.

BTW, regarding INTERVALs: PostgreSQL allows one to specify a number of
months in an interval, and expressions like date 'YYYY-MM-DD' + interval
'1 month' return the same DD on the next month.  This cannot be
approximated by converting '1 month' to 30 days, and thus cannot be
currently represented by datetime.timedelta.

> > subtracting two times to get an interval
> >(which perhaps should always be positive, i.e. some sort of modular
> >arithmetic (modulo 24 hours)), and adding an interval to a time (also
> >mod 24 hours). 
> 
> No. No. No. No math on times. :)

But modular arithmetic is fun! :)

> I think it would be nice to have a time type for completeness and to
> better interoperate with databases. I would keep it as simple as
> possible. I'd keep it a basic container for the time data that would
> otherwise appear in datetime and datetimetz objects. There shouldn't
> be math support. There should be special constructors for combining a
> date or datetime(tz) and a time(tz).

How would you combine a datetime with a time?  Just replace the existing
time part, i.e. treat the datetime as date?

> Wanna propose an interface? :)
> 
> It should be posted somewhere at:
> http://www.zope.org/Members/fdrake/DateTimeWiki/FrontPage

I'll see what I can come up with.

Marius Gedminas
-- 
I've been in the sun for a week.  I took the bold step of leaving my
laptop at home.  I found only 4K messages pending when I returned.
	-- Keith Packard