Re: timestamp arithmetics in C function - Mailing list pgsql-general

Hello all,

unfortunately, I have so far not received a reply to my question 
below. I am well aware that no one has an obligation to reply; I was 
just wondering whether I phrased my question badly or whether there 
is anything else I could do to improve it.

Thanks for your help and best wishes,
Lutz


On Friday, 10.08.2018 09:05:40 Lutz Gehlen wrote:
> Hello all,
> 
> I am trying to implement a C function that accepts a date ("date"
> in the sense of a type of information, not a postgres datatype)
> as parameter (among others) and returns a certain point in time.
> (The background is to calculate the time of dawn and dusk at the
> given date.) Ideally, I would like to accept a timestamp value
> and return another timestamp as result. I have implemented the
> function, but I would like to ask advice on whether my
> implementation is the recommended way to achieve this.
> 
> To get started - since this is my first attempt at a C function in
> postgres - I implemented a function that accepts the date as
> three separate int32 values for year, month, and day and returns
> the time of dawn as a float8 for the minutes since midnight (this
> is what the implemented algorithm internally returns, anyway):
> 
> ----
> PG_FUNCTION_INFO_V1(dawn_utc);
> 
> Datum dawn_utc(PG_FUNCTION_ARGS) {
>   float8 lat              = PG_GETARG_FLOAT8(0);
>   float8 lon              = PG_GETARG_FLOAT8(1);
>   int32  year             = PG_GETARG_INT32(2);
>   int32  month            = PG_GETARG_INT32(3);
>   int32  day              = PG_GETARG_INT32(4);
>   float8 solar_depression = PG_GETARG_FLOAT8(5);
> 
>   // postgres-independent computation goes here
>   float8 dawn_utc = calc_dawn_utc
>     (lat, lon, year, month, day, solar_depression);
> 
>   PG_RETURN_FLOAT8(dawn_utc);
> }
> ----
> 
> This works fine. However, it would be more convenient if the
> function would accept a date or timestamp value and return a
> timestamp. So I modified the first part of the function like
> this, based on code snippets I found in the postgres source code:
> 
> ----
> PG_FUNCTION_INFO_V1(dawn_utc);
> 
> Datum dawn_utc(PG_FUNCTION_ARGS) {
>   float8 lat              = PG_GETARG_FLOAT8(0);
>   float8 lon              = PG_GETARG_FLOAT8(1);
>   Timestamp timestamp     = PG_GETARG_TIMESTAMP(2);
>   float8 solar_depression = PG_GETARG_FLOAT8(3);
> 
>   struct pg_tm tt;
>   struct pg_tm *tm = &tt;
>   fsec_t       fsec;
> 
>   if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
>     ereport(ERROR,
>             (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
>              errmsg("timestamp out of range")));
> 
>   // postgres-independent computation goes here
>   float8 dawn_utc = calc_dawn_utc
>     (lat, lon, tm->tm_year, tm->tm_mon, tm->tm_mday,
> solar_depression;
> ----
> 
> For the second part of the function, I now have to add the
> calculated number of minutes to the date portion of the timestamp
> variable. One has to be aware that depending on the geographic
> location dawn_utc can possibly be negative or larger than 1440
> (i.e. 24h). I am not sure whether I should construct an interval
> value from the number of minutes and add that to the timestamp. I
> have not figured out how to do this, but decided to calculate a
> new timestamp in a more fundamental way:
> 
> ----
>   tm->tm_sec  = 0;
>   tm->tm_min  = 0;
>   tm->tm_hour = 0;
>   Timestamp result;
>   if (tm2timestamp(tm, 0, NULL, &result) != 0)
>     ereport(ERROR,
>             (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
>              errmsg("timestamp out of range")));
> 
> #ifdef HAVE_INT64_TIMESTAMP
>   /* timestamp is microseconds since 2000 */
>   result += dawn_utc * USECS_PER_MINUTE;
> #else
>   /* timestamp is seconds since 2000 */
>   result += dawn_utc * (double) SECS_PER_MINUTE;
> #endif
> 
>   PG_RETURN_TIMESTAMP(result);
> ----
> 
> Again this code is based on what I found in the source code. It
> seems to work correctly (at least on my development machine), but
> I am wondering whether this is a safe and recommended way to
> achieve this result or whether it is considered bad practice to
> manipulate a timestamp on such fundamental level.
> 
> Thank you for your advice and best wishes,
> Lutz