[Zope3-dev] Dates and times

[Guido van Rossum]

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

What's the range for all those types (apart for infinity)?

What kind of arithmetic does it support?

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

What are the timezone semantics for TIMETZ/TIMESTAMPTZ?

> ZPsycopgDA

(BTW, that's the weirdest package name I've seen in a while. :-)

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

What's the point of representing INTERVALs as strings?  Doesn't that
make them useless?

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

You can do this by extracting year, month day from a date or datetime,
adding 1 to the month, adding a carry to the year if necessary, and
then creating a new one.

But how often do you need this?

Is this standard SQL, or a PostgreSQL extension?

Does PostgreSQL supports subtracting two dates giving an interval?
If so, does that return a number of months or a number of days?  I
note that both are useful, in different situations.

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

You don't.  You combine a date with a time.

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

Thanks!

--Guido van Rossum (home page: http://www.python.org/~guido/)