Re: Timezones (in 8.5?)

hernan gonzalez <hgonzalez(at)gmail(dot)com> wrote:

> I believe that this distinction between two realms: one related to
> (say) "physical time" and the other to (say) "civil date-time", is
> the key to put some order... conceptually, at least (I'm not
> speaking about feasibility for now). This is the approach of some
> Date-Time APIs, for example the "Joda" Java library
> http://joda-time.sourceforge.net/ (headed to replace soon
> https://jsr-310.dev.java.net/ the original ugly JDK
Date-Calendar
> API) and I believe it's the right way.

Congratulations on the most sane and thoughtful discussion of this
I've seen! In our shop we had so many problems with the "physical
time" based implementation of dates, times, and timestamps in Java
that we wrote our own library to cover our needs. I hadn't heard
about Joda; we should probably look at it to see if we can migrate
from our home-grown solution.

One thing you didn't address is the "end-of-month" issues -- how do
you handle an order that someone pay a set amount on a given date and
monthly thereafter, when the date might be past the 28th? I'm curious
to hear your opinion on that topic. I have seen in this real-world
financial applications several times. They have usually wanted to go
to the last day of the month when there aren't enough days in a given
month, but then go back out to the original day-of-month whenever
possible; but sometimes the payment "one month" after the 31st of
January has to be 30 days past the 1st of the next month. The SQL
standard solution to this is much ridiculed here, even though I
suspect many have seen monthly bills or statements at some point in
their lives.... ;-)

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> hernan gonzalez <hgonzalez(at)gmail(dot)com> wrote:
>> I believe that this distinction between two realms: one related to
>> (say) "physical time" and the other to (say) "civil date-time", is
>> the key to put some order... conceptually, at least (I'm not
>> speaking about feasibility for now).

> Congratulations on the most sane and thoughtful discussion of this
> I've seen!

Yeah. As Hernan says, our notion of timestamptz corresponds to physical
time, although the input/output conventions for it blur that rather
badly. You can use the AT TIME ZONE constructs to convert between
physical and civil times, but only according to the system's current
understanding of the civil calendar, which will change anytime you
install an update of the zic database. We haven't got a datatype that
corresponds directly to "an instant in civil time" --- you could store
timestamp-without-tz and a time zone name, but it's not built in.

I could see developing new types that correspond more directly to
physical and civil time --- the first is probably exactly the same as
timestamptz except it always displays in UTC, and the second needs two
fields. I think that trying to substitute either of these for the
existing types is probably a lost cause though.

Trying to deal with different civil calendars (changes in zic database
rules) seems way too hard for what it would buy us. I think if you're
using the civil time type, you're assuming that "10AM Nov 17 2009" means
"10AM local time", even if the powers that be change the GMT offset
sometime during the period that the data value is of interest.

> One thing you didn't address is the "end-of-month" issues -- how do
> you handle an order that someone pay a set amount on a given date and
> monthly thereafter, when the date might be past the 28th?

This seems to be an arithmetic operator issue and not directly a
property of the type --- you could imagine different "datetime + interval"
operators giving different answers for this but still working on the
same underlying civil-time type.

regards, tom lane