"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> hernan gonzalez <hgonzalez@gmail.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