Locale support

[Yonatan Misgan]

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 “” and “” 
day divisions of Amharic translation is an approximation that is no more than 
serviceable:
ጠዋት and ከሰዓት
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.

RE: Locale support

[Yonatan Misgan]

Thank you for your quick response. I am also impressed to develop Ethiopian 
calendar as an extension on PostgreSQL and I I have already developed the 
function that convert Gregorian calendar time to Ethiopian calendar time. But 
the difficulty is on how to use this function on PostgreSQL as well on 
PostgreSQL month names are key words when I  am developing Ethiopian calendar 
the date data type is doesn't accept Ethiopian month name as a date data type 
value only the numeric representation of the months are accepted by compiler.
So my question is after  developing the converter function where I put it for 
accessing it on PostgreSQL.



 Original message 
From: Thomas Munro 
Date: 8/8/19 11:34 AM (GMT+03:00)
To: Yonatan Misgan 
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Locale support

On Thu, Aug 8, 2019 at 7:31 PM Yonatan Misgan  wrote:
> 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.

Hi Yonatan,

I'm not sure if this requires hacking the PostgreSQL source code.  It
sounds more like an extension.  My first impression is that you might
not need new types like "date".  Instead you might be able to develop
a suite of functions that can convert the existing types to and from
the display formats (ie strings) and perhaps also components (year,
month, day etc) that you use in your calendar system.  For example:

SELECT to_char_ethiopian(CURRENT_DATE, '-MM-DD'), or whatever kind
of format control string would be more appropriate.

However, I see from https://en.wikipedia.org/wiki/Time_in_Ethiopia
that new days start at 1 o'clock, not midnight, so that makes
CURRENT_DATE a bit more confusing -- you might need to write a
function current_eth_date() to deal with that small difference.  Other
than that detail, which is really a detail of CURRENT_DATE and not of
the date type, dates are internally represented as a number of days
since some arbitrary "epoch" day (I think Gregorian 2000-01-01), not
as the components you see when you look at the output of SELECT
CURRENT_DATE.  That is, the Gregorian calendar concepts exist mostly
in the display/input functions, and the operators that can add
intervals etc.  You could supply a different set of functions, but use
the same types, and I suspect that'd be convenient because then you'll
be able to use Gregorian and Ethiopian conventions with the same data,
whenever you need to.  It's much the same for timestamps, but with
more complicated details.

I see that there are libraries and bits of example code around to do
the various kinds of calendar maths required for Ethiopian dates in
Perl, Python etc.  If I were you I think I'd experiment with a
prototype implementation using  PL/Perl, PL/Python etc (a way to
define new PostgreSQL functions written in those languages), and if
that goes well, try writing an extension in C to do it more
efficiently.

The end goal of that woudn't need to be part of PostgreSQL itself, but
just an extension that anyone can download and install to use
Ethiopian dates conveniently.

--
Thomas Munro
https://enterprisedb.com

RE: Locale support

[Yonatan Misgan]

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.

 Original message 
From: Thomas Munro 
Date: 8/9/19 1:17 AM (GMT+03:00)
To: Peter Geoghegan 
Cc: Yonatan Misgan , pgsql-hackers@lists.postgresql.org
Subject: Re: Locale support

On Fri, Aug 9, 2019 at 6:19 AM Peter Geoghegan  wrote:
> On Thu, Aug 8, 2019 at 6:29 AM Yonatan Misgan  wrote:
> > So my question is after  developing the converter function where I put it 
> > for accessing it on PostgreSQL.
>
> Maybe you can take some inspiration from the postgresql-unit extension:
>
> https://github.com/df7cb/postgresql-unit

Here's a 5 minute bare bones extension with place holders functions
showing what I had in mind.  That is, assuming that "date" is a
reasonable type, and we're just talking about different ways of
converting to/from text.

https://github.com/macdice/calendars

--
Thomas Munro
https://enterprisedb.com

Extension development

[Yonatan Misgan]

Hello, I am trying to develop calendar extension for PostgreSQL  but there is a 
difficulties on how to get day, month and year from PostgreSQL source code 
because when am read the PostgreSQL source code it uses DateADT as a data type 
and this DateADT returns the total numbers of day. So how can  I get day, month 
or year only. For example the below code is PostgreSQL source code to return 
current date.
/*
* GetSQLCurrentDate -- implements CURRENT_DATE
*/
DateADT
GetSQLCurrentDate(void)
{
TimestampTz ts;
struct pg_tm tt,
   *tm = &tt;
fsec_t   fsec;
int   tz;

ts = GetCurrentTransactionStartTimestamp();

if (timestamp2tm(ts, &tz, tm, &fsec, NULL, NULL) != 0)
ereport(ERROR,

(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),

errmsg("timestamp out of range")));

return date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - 
POSTGRES_EPOCH_JDATE;
}
>From this source code how can I get only the year to convert my own calendar 
>year.  I need this because Ethiopian calendar is totally differ from GC in 
>terms of day, month and year.


Regards,

Yonathan Misgan
Assistant Lecturer, @ Debre Tabor University
Faculty of Technology
Department of Computer Science
Studying MSc in Computer Science (in Data and Web Engineering)
@ Addis Ababa University
E-mail: yona...@dtu.edu.et
yonathanmisga...@gmail.com
Tel:   (+251)-911180185 (mob)

Extension development

[Yonatan Misgan]

I am developed my own PostgreSQL extension for learning purpose and it is 
working correctly but I want to know to which components of the database is my 
own extension components communicate. For example I have c code, make file sql 
script, and control file after compiling the make file to which components of 
the database are each of my extension components to communicate. Thanks for 
your response.


Regards,

Yonathan Misgan
Assistant Lecturer, @ Debre Tabor University
Faculty of Technology
Department of Computer Science
Studying MSc in Computer Science (in Data and Web Engineering)
@ Addis Ababa University
E-mail: yona...@dtu.edu.et
yonathanmisga...@gmail.com
Tel:   (+251)-911180185 (mob)

Extension development

[Yonatan Misgan]

Is there any one who help me what the architecture of an extension should looks 
like in PostgreSQL database.


Regards,

Yonathan Misgan
Assistant Lecturer, @ Debre Tabor University
Faculty of Technology
Department of Computer Science
Studying MSc in Computer Science (in Data and Web Engineering)
@ Addis Ababa University
E-mail: yona...@dtu.edu.et
yonathanmisga...@gmail.com
Tel:   (+251)-911180185 (mob)

RE: Extension development

[Yonatan Misgan]

I have done the hard code. But my question is related to the concept how these 
extension components working together as a system. For example what the use 
case diagram looks like for my extension and also the other architectural view 
of the extension should look like.


Regards,

Yonathan Misgan
Assistant Lecturer, @ Debre Tabor University
Faculty of Technology
Department of Computer Science
Studying MSc in Computer Science (in Data and Web Engineering)
@ Addis Ababa University
E-mail: yona...@dtu.edu.et<mailto:yona...@dtu.edu.et>
yonathanmisga...@gmail.com<mailto:yonathanmisga...@gmail.com>
Tel:   (+251)-911180185 (mob)


From: Ahsan Hadi 
Sent: Tuesday, November 12, 2019 10:50:23 PM
To: Yonatan Misgan 
Cc: pgsql-hackers@lists.postgresql.org 
Subject: Re: Extension development

Hi Yonatan,

You can follow this blog for creating your own extension in PostgreSQL..

https://www.highgo.ca/2019/10/01/a-guide-to-create-user-defined-extension-modules-to-postgres/

-- Ahsan

On Tue, Nov 12, 2019 at 11:54 AM Yonatan Misgan 
mailto:yona...@dtu.edu.et>> wrote:

I am developed my own PostgreSQL extension for learning purpose and it is 
working correctly but I want to know to which components of the database is my 
own extension components communicate. For example I have c code, make file sql 
script, and control file after compiling the make file to which components of 
the database are each of my extension components to communicate. Thanks for 
your response.



Regards,



Yonathan Misgan

Assistant Lecturer, @ Debre Tabor University

Faculty of Technology

Department of Computer Science

Studying MSc in Computer Science (in Data and Web Engineering)

@ Addis Ababa University

E-mail: yona...@dtu.edu.et<mailto:yona...@dtu.edu.et>

yonathanmisga...@gmail.com<mailto:yonathanmisga...@gmail.com>

Tel:   (+251)-911180185 (mob)




--
Highgo Software (Canada/China/Pakistan)
URL : http://www.highgo.ca<http://www.highgo.ca/>
ADDR: 10318 WHALLEY BLVD, Surrey, BC
EMAIL: mailto: ahsan.h...@highgo.ca