On Fri, Aug 9, 2019 at 6:15 PM Yonatan Misgan <yona...@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 date and time representation of the > database systems be in Ethiopian calendar.
Hi Yonatan, 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: $ LC_TIME=en_GB.UTF-8 date Sat 10 Aug 2019 10:58:42 NZST $ LC_TIME=fr_FR.UTF-8 date sam. 10 août 2019 10:58:48 NZST $ LC_TIME=el_GR.UTF-8 date Σάβ 10 Αυγ 2019 10:58:51 NZST $ LC_TIME=am_ET.UTF-8 date ቅዳሜ ኦገስ 10 10:58:55 NZST 2019 These all say it's Saturday (ቅዳሜ) the 10th of August (ኦገስ) on the Gregorian calendar. Looking at POSIX date[1] you can see they contemplated the existence of non-Gregorian calendars in a very limited way, but no operating system I have access to does anything other than Gregorian with %x and %c: "The date string formatting capabilities are intended for use in Gregorian-style calendars, possibly with a different starting year (or years). The %x and %c conversion specifications, however, are intended for local representation; these may be based on a different, non-Gregorian calendar." PostgreSQL behaves the same way when you ask for the localised month in am_ET: tmunro=> set lc_time to 'am_ET.UTF-8'; SET tmunro=> select to_char(now(), 'TMMon'); to_char ----------- ኦገስ (1 row) This is hard coded into the system, as you can see from src/backend/utils/adt/pg_locale.c where the *twelve* month names are loaded into localized_abbrev_months and other similar arrays. That's the first clue that this system can't handle the thirteen Ethiopian months, not to mention the maths required to work with them. That's why I think you need a new, different to_char() function (and probably more functions). In that skeleton code I posted, you can see I defined a function to_char(date, format, calendar) that takes a third argument, for the calendar name. You might also wonder if that new function should respect the locale settings, but I'm not sure if it could in general; you'd have to be able to get (say) the Greek names for the Ethiopian calendar's months, which the OS won't be able to give you. Though perhaps you'd want some way to select between the Ethiopian script and the transliterations into Latin script, which would presumably be hard coded into the extension, I have no idea if that's useful to anyone... BTW there have been earlier discussions of this: https://www.postgresql.org/message-id/flat/CAM7dW9iBXDJuwZrEXW%2Bdsa_%3Dew%3D%2BFdv7mcF51nQLGSkTkQp2MQ%40mail.gmail.com It shows that Apple has an Objective-C NSCalendar class that understands Ehtiopian, Persian, Hebrew, ... calendars, which made me wonder if LC_TIME=am_ET.UTF-8 would trigger something special on a Mac, but nope, its libc still just gives Ethiopian names for Gregorian months as I expected. [1] http://pubs.opengroup.org/onlinepubs/009695399/utilities/date.html -- Thomas Munro https://enterprisedb.com
