Re: Locale support

On Sat, Aug 10, 2019 at 11:50 AM Thomas Munro <thomas.munro@gmail.com> wrote:
> On Fri, Aug 9, 2019 at 6:15 PM Yonatan Misgan <yonamis@dtu.edu.et> wrote:
> > Can I  implement it as a locale support? When the user want to change the lc _time = am_ET(Amharic Ethiopia ) the
dateand time representation of the database systems be in Ethiopian calendar.
 
>
> I'm not an expert in this stuff, but it seem to me that both the
> operating system and the date/time localisation code in PostgreSQL use
> Gregorian calendar logic, even though they can use local language
> names for them.  That is, they allow you to display the French, Greek,
> Ethiopian ... names for the Gregorian months, but not a a completely
> different calendar system.  For example, according to my operating
> system:
> ...

Reading about that led me to the ICU model of calendars.  Here's the C
API (there are C++ and Java APIs too, but to maximise the possibility
of producing something that could ever be part of core PostgreSQL, I'd
stick to pure C):

http://userguide.icu-project.org/datetime/calendar
http://icu-project.org/apiref/icu4c/ucal_8h.html
http://icu-project.org/apiref/icu4c/udat_8h.html

It does in fact work using locales to select calendars as you were
suggesting (unlike POSIX or at least the POSIX systems I tried), and
there it knows that am_ET is associated with the Ethiopic calendar, as
you were suggesting (and likewise for nearly a dozen other calendars).
When you call ucal_open() you have to say whether you want
UCAL_TRADITIONAL (the traditional calendar associated with the locale)
or UCAL_GREGORIAN.  In the usual ICU fashion it lets you mix and match
more explicitly, so you can use locale names like
"fr_FR@calendar=buddhist".  Then you can us the udat.h stuff to format
dates and so forth.

So now I'm wondering if the best idea would be to write an extension
that provides a handful of carefully crafted functions to expose the
ICU calendar date/time formatting/parsing/manipulation stuff to SQL.
I think that should be doable in a way that is not specific to
Ethiopic, so that users of Indian, Persian, Islamic etc calendars can
also benefit from this.  I'm not sure if it should use LC_TIME --
possibly not, because that would interfere with libc-based stuff and
the locale names don't match up; that might only make sense if this
facility completely replaced the built in date/time stuff, which seems
unlikely.  Perhaps you'd just want to pass in the complete ICU locale
string to each of the functions, to_char_icu(current_date, 'Month',
'fr_FR@calendar=buddhist'), or perhaps you'd want a GUC to control it
(extensions can have their own GUCs).  I'm not sure.

There has certainly been interest in exposing other bits of ICU to
PostgreSQL, either in core or in extensions: collations, Unicode
normalisation, and now this.  Hmm.

Another thing you might want to look into is whether the SQL standard
has anything to say about non-Gregorian calendars, and whether DB2 has
anything to support them (it certainly has ICU inside it, as does
PostgreSQL (optionally), so I wonder if they've exposed this part of
it to SQL and if so how).

-- 
Thomas Munro
https://enterprisedb.com