Locale support - Mailing list pgsql-hackers
| From | Yonatan Misgan |
|---|---|
| Subject | Locale support |
| Date | |
| Msg-id | ghotu1cmaeh6h98rxatrk7sg.1565248823889@email.android.com Whole thread Raw |
| Responses |
Re: Locale support
|
| List | pgsql-hackers |
I am Yonathan Misgan from Ethiopia, want to add some functionalities on PostgreSQL to support Ethiopian locales. I want your advice where I start to hack the PostgresSQL source code. I have attached some synopsis about the existing problems of PostgresSQL related with Ethiopian locale specially related with calendar, date and time format. Please don't mind about date and time written with Amharic because I used only to show the problems.
Calendar: A calendar is a system of organizing days for social, religious, commercial or administrative purposes. This is done by giving names to periods of time, typically days, weeks, months and years. A date is the designation of a single, specific day within such a system. The Gregorian calendar is the most widely used calendar in the world today specially for database and other computer system. It is the calendar used in the international standard for representation of dates and times: ISO 8601:2004. It is a solar calendar based on a 365 days common year divideinto 12 months of irregular lengths 11 of the months have either 30 or 31 days, while the second month, February, has only 28 days during the common year. However, nearly every four years is a leap year, when one extra or intercalary day is added on February. Making the leap year in the Gregorian calendar 366 days long. The days of the year in the calendar are divided into 7 days weeks. The international standard is to start the week on Monday. However, several countries, including the Ethiopia, US and Canada, count Sunday as the first day of the week .
The Ethiopian calendar is the principal calendar used in Ethiopia and serves as the liturgical year for Christians in Eritrea and Ethiopia belonging to the Eritrean Orthodox Tewahedo Church, Ethiopian Orthodox Tewahedo Church, Eastern Catholic Churches and Coptic Orthodox Church of Alexandria. The Ethiopian calendar has difference with the Gregorian calendar in terms of day, month and year. Like the Coptic calendar, the Ethiopic calendar has 12 months of 30 days plus 5 or 6 epagomenal days, which comprise a thirteenth month. Maybe the clearest example of the different ways cultures view the world around them is demonstrated by our perceptions of the nontangible entity of time. The seven days week is nearly universal but we disagree on what we consider the first day of the week to be. This is the case even under the same calendar system.
Date and Time: Ethiopia shares the 24 hour day convention with the rest of the world but differs on when the day begins. The two part day division around mid-day (AM for anti-meridian and PM for post-meridian) is also a foreign notion taken for universal in localization systems. Where the “<am>” and “<pm>” day divisions of Amharic translation is an approximation that is no more than serviceable:
<am>ጠዋት</am> and <pm>ከሰዓት</pm>
While these translations could be understood under the context of the foreign conventions that they map, they are not ideal for Ethiopia. Naturally, Ethiopia will want to apply its own conventions that are already millennium old. ጠዋት, ረፋድ, እኩለ ቀን, እኩለ ሌሊት some of the examples of day division in Ethiopia. Ethiopia does not have a well-established preference for digital time formats in database system and other computer systems, but we want to establish computerized systems into a society. An example digital time format under United States English conventions appears as: Mon 27 Feb 2018 12:00:00 PM EAT
The equivalent date and time under the Ethiopian Amharic convention as available on Linux systems today appears as:
ማክሰ ፌብሩ 26 ቀን 12： 00： 00 ከሰዓት EAT 2018 ዓ/ም
This represents a loose mapping of some Amharic conventions onto an external reckoning of time. This is only translation and not localization in its truest sense. A hypothetical Ethiopic date and time presentation might looks as:
ማክሰኞ፣ የካቲት 19 ቀን 6： 00： 00 እኩለ ቀን 2010 ዓ/ም or
ማክሰኞ፣ የካቲት 19 ቀን 6： 00： 00 እኩለ ቀን፳ ፻ ፲ዓ/ም
Let see the drawbacks that exist in PostgreSQL with examples
Most of the database systems use time stamp to store data is in Gregorian calendar system for specific time zone.
However, most the data in Ethiopia are available with Ethiopian calendar system and users in Ethiopia are not comfortable with the Gregorian calendar as they use Ethiopian calendar in their day-to-day activities. This usually create inconvenience when the user want to have a reference to Ethiopic date as they had Gregorian calendar at database system. An example query to demonstrate this is given below.
Q2: Select current_date;
Q2 returns ‘2019-08-08’ but currently in Ethiopia calendar the year is ‘2011’,
Q3: Select to_char(to_timestamp(to_char(4, '999'), 'MM'), 'Month');
Q3 returns ‘April’ whereas the 4th month is ታህሳስ(December) in Ethiopian calendar system.
Q4: Select to_char(to_timestamp (to_char(13, '999'), 'MM'), 'Month');
Q4 returns an error message since the GC have only ‘12’ month per a year.
Where Q2, Q3 and Q4 are queries.
pgsql-hackers by date: