Re: Calendar support in localization

On Wed, Mar 17, 2021 at 6:31 AM Surafel Temesgen <surafel3000(at)gmail(dot)com> wrote:
> Ethiopice calendar have 13 months so it can not be stored as date and timestamp type and you approach seems more complicated and i suggest to have this feature on the purpose of PostgreSQL popularities too not only for my need

I know, but the DATE and TIMESTAMPTZ datatypes don't intrinsically
know anything about months or other calendar concepts. Internally,
they are just a single number that counts the number of days or
seconds since an arbitrary epoch time. We are all in agreement about
how many times the Earth has rotated since then*. The calendar
concepts such as "day", "month", "year", whether Gregorian, Ethiopic,
Islamic, ... are all derivable from those numbers, if you know the
rules.

So I think you should seriously consider using the same types.

> Each calendar-aware date arithmetic is different so solving one calendar problem didn't help on other calendar

They have a *lot* in common though. They have similar "fields" (day,
month, year etc), based on the Earth, moon, sun etc, so it is possible
to use a common abstraction to interact with them. I haven't studied
it too closely, but it looks like ICU can give you a "Calendar" object
for a given Locale (which you create from a string like
"am_ET(at)calendar=traditional") and timezone ("Africa/Addis_Ababa").
Then you can set the object's time to X seconds since an epoch, based
on UTC seconds without leap seconds -- which is exactly like our
TIMESTAMPTZ's internal value -- and then you can query it to get
fields like month etc. Or do the opposite, or use formatting and
parsing routines etc. Internally, ICU has a C++ class for each
calendar with a name like EthiopicCalendar, IslamicCalendar etc which
encapsulates all the logic, but it's not necessary to use them
directly: we could just look them up with names via the C API and then
treat them all the same.

> I think you suggesting this by expecting the implementation is difficult but it's not that much difficult once you fully understand Gregorian calendar and the calendar you work on

Yeah, I am sure it's all just a bunch of simple integer maths. But
I'm talking about things like software architecture, maintainability,
cohesion, and getting maximum impact for the work we do.

I may be missing some key detail though: why do you think it should be
a different type? The two reasons I can think of are: (1) the
slightly tricky detail that the date apparently changes at 1:00am
(which I don't think is a show stopper for this approach, I could
elaborate), (2) you may want dates to be formatted on the screen with
the Ethiopic calendar in common software like psql and GUI clients,
which may be easier to arrange with different types, but that seems to
be a cosmetic thing that could eventually be handled with tighter
locale integration with ICU. In the early stages you'd access
calendar logic though special functions with names like
icu_format_date(), or whatever.

Maybe I'm totally wrong about all of this, but this is the first way
I'd probably try to tackle this problem, and I suspect it has the
highest chance of eventually being included in core PostgreSQL.

*I mean, we can discuss the different "timelines" like UT, UTC, TAI
etc, but that's getting into the weeds, the usual timeline for
computer software outside specialist scientific purposes is UTC
without leap seconds.