Re: to_timestamp() and timestamp without time zone - Mailing list pgsql-general

> I meant that time-calculations themselves have lots of issues and
subtleties.

Fair enough, and I agree there is no magic API to solve the difficulties of
adapting rational, logic based systems to a Calendar system last edited by
the Pope and based upon the imperfect movement of Sol relative to Earth.
But we've already detailed why this specific case could use some more
attention.

> Calculations for long prior dates/times have things like a few minute jump
> when (at least in the US) an interval crosses Sunday, November 18,
> 1883 ("the day of two noons"). And although October 1582 (Catholic
> regions) or September 1752 (Protestant regions/Unix-assumption) or later
> (Orthodox) are missing 10-days, PostgreSQL follows the SQL standard which
> does not show those dates as missing at all.
>

This really falls into application knowledge since the vast majority of
use-cases use fairly recent dates.  Those who deal with long-ago dates
should be expected to understand the limitations of their reality and would
devise means to accommodate them.  Likewise, from the omitted next
paragraph, those who are relying on time need to take into consideration
that changes happen.  The effort to deal with that change is then trade-off
against the cost of the failure occurring.

In some/most cases, over a short timeframe, the proper solution is to be
flexible and/or relative. Examples:
Scheduler:  Run the schedule the first chance you get when the "system"
hour/minute is after/greater than the "schedule" hour/minute.  You may or
may not need to ensure that "schedule" hour/minute entries are sorted so
those with a longer delay are completed first  - just as they would be in
normal circumstances.

Hospital:  Dispense the next dose 5 hours from now (about the broadest unit
you can safely use is DAY).  In this case the software should be able to
"count" forward minute-by-minute, using the TimeZone rules to skip around if
necessary, and determine whether 5 hours from 0:30 is 4:30, 5:30, or 6:30.
The API implements this "counting" via the "addition operator".

In theory adding "months/years" should be forbidden and a "procedure" that
applies a consistent "rule set" should be used instead.  Some standard ones
can be provided and the user can always create their own.  One possible rule
would be that adding or subtracting months to a date that is the last day of
its month always returns the last of the resultant month.  Another
rule/function could implement the current behavior where the day does not
change (and then you have two variations - if the new date is invalid you
either fail or coerce).

While the discussion or core vs. extension comes up consider that many users
and evaluators are going to look at the core first and, as I've said before,
if they see something that appears like it will work they will just use it.
So you'd either want to have no (or very minimal) time-oriented API or have
it be full-featured (and also have a "save me from myself" quality to it -
given time's complexities).

All this said, I am getting worked up by this particular thread but, in
reality, the status-quo does not truly harm me that I know of - but my usage
of PostgreSQL is very light/flexible (lots of spare capacity).  I'm coming
at this both from a desire to learn more and also "what would I do if I was
starting from scratch?".  The best approach, since we are not starting from
scratch, would be for interested and capable parties to work on a full-blown
"time" extension that, while maybe less user friendly, is safer to use and
much more explicit.  However, there are likely to be some components in such
an extension that would be forward-only and thus could be introduced to the
core API without any difficulty (a "to_timestamp_abstract(text,
text)->timestamp" function comes to mind - note the name change; see other
recent post for reason).  And since interested and capable are not mutually
inclusive those who are interested but not capable would probably appreciate
more than just "here is a workaround" from the community.  At the same time,
interested parties need to put together a precise and coherent proposal that
can be discussed and acted upon - with a clear (even if possibly incorrect)
assertion about why something is either wrong or difficult to use.


>
> There are different definitions of when a year starts so be sure not to
grab
> the wrong week-number or day-number - ISO and Julian are not the same.
>

Agreed; but people who are going to choose a calendar other than the
Gregorian Calendar should be expected to learn and abide by the rules of
that Calendar.  The responsibility of the API is to correctly apply those
rules (and help the user abide by them where possible).

> And, of course, everything starts with the ethnocentric assumption of what
> calendar system to use. From my experience, there is not a lot of good SQL
> support for data using Islamic, Chinese, Hebrew, Hindu, Iranian, Coptic or
> Ethiopian calendars.

Supply and Demand.  Iran is thinking about creating their own Internet - let
THEM fund and develop a PostgreSQL extension for their Calendar...

Taking Hernan's comments even further a "point-in-time" is: { Calendar,
Location, DATE, TIME, LocationCode }  which would allow you to say:

"[TIME=1:30 AM] {LocationCode=DST} on [DATE], while in
{Location=America_NewYork}, using {Calendar=Gregorian}".

Unless converted even if you are in Vienna if you look at that particular
time that is what you see.  Using the rules, since every valid instant
exists everywhere, the corresponding time in a different location can always
be calculated.

Whether or how to convert between Calendars would be different matter
altogether.

Also, do NOT go down the path of whether a particular Calendar is in use
(has rules) for a particular location on a particular date (i.e., is an
Iranian Calendar in America_NewYork even valid?).  At some point you simply
put down default rules that will apply when more specific rules are not
provided.

David J.

Note: I am writing this and a response to Hernan at the same time (no pun
intended...)