Thread: Extension development

On Thu, Aug 15, 2019 at 06:58:07AM +0000, Yonatan Misgan wrote:
>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.
>

I think you might want to look at timestamptz_part() function, in
timestamp.c. That's what's behind date_part() SQL function, which seems
doing the sort of stuff you need.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 

On 08/15/19 02:58, Yonatan Misgan wrote:

> 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.

I find myself wondering whether getting only the year is sufficient to
do the conversion. There is already an Ethiopic calendar available for
Java (not included, but in org.threeten.extra[1]), and it seems to say
the years do not align precisely with Gregorian years (as I would not
have expected anyway):

"Dates are aligned such that 0001-01-01 (Ethiopic) is 0008-08-27 (ISO)."

So it seems more likely that you would need a calculation involving the
year, month, and day ... or even that the Julian day number already
stored in PostgreSQL could be the most convenient starting point for
the arithmetic you need.

It's possible you might want to crib some of the algorithm from the
threeten-extra Ethiopic date sources [2]. It would need adjustment for
the PostgreSQL epoch being Gregorian year 2000 rather than Java's 1970
(a simple constant offset), and for PostgreSQL using a Julian day number
rather than java.time's proleptic Gregorian (a difference changing by three
days every 400 years).

Another option would be to take advantage of PL/Java and directly use
the threeten-extra Ethiopic calendar.

Regards,
-Chap


[1]
https://www.threeten.org/threeten-extra/apidocs/org.threeten.extra/org/threeten/extra/chrono/EthiopicDate.html

[2]
https://github.com/ThreeTen/threeten-extra/blob/master/src/main/java/org/threeten/extra/chrono/EthiopicDate.java

On Fri, Aug 16, 2019 at 10:55 AM Chapman Flack <chap@anastigmatix.net> wrote:
> On 08/15/19 02:58, Yonatan Misgan wrote:
> > 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.
>
> I find myself wondering whether getting only the year is sufficient to
> do the conversion. There is already an Ethiopic calendar available for
> Java (not included, but in org.threeten.extra[1]), and it seems to say
> the years do not align precisely with Gregorian years (as I would not
> have expected anyway):

I can't think of a single reason not to use ICU for this.  It will
handle every kind of conversion you could need here, it's rock solid
and debugged and established, it'll handle 10+ different calendars
(not just Ethiopic), and it's already linked into PostgreSQL in most
deployments.

Obviously if Yonatan wants to write his own calendar logic that's
cool, but if we're talking about something that might eventually be
part of PostgreSQL core, or a contrib module shipped with PostgreSQL,
or even a widely used popular extension shipped separately, I would
bet on ICU rather than new hand rolled algorithms.

> "Dates are aligned such that 0001-01-01 (Ethiopic) is 0008-08-27 (ISO)."
>
> So it seems more likely that you would need a calculation involving the
> year, month, and day ... or even that the Julian day number already
> stored in PostgreSQL could be the most convenient starting point for
> the arithmetic you need.

Indeed.  I think you should convert between our internal day number
and date components (year, month, day) + various string formats
derived from them, and likewise for the internal microsecond number
that we use for timestamps.  I think it's probably a mistake to start
from the Gregorian y, m, d components and convert to Ethiopic y, m, d
(conversion algorithms that start from those components are more
suitable for humans; PostgreSQL already has days numbered sequentially
along one line, not y, m, d).  Or just let ICU do it.

-- 
Thomas Munro
https://enterprisedb.com