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

Hi Adrian,

thanks for the encouragement. If anyone can comment on the code, I'd 
still be very interested.

Cheers,
Lutz


On Thursday, 06.09.2018 06:27:14 Adrian Klaver wrote:
> On 09/03/2018 09:11 AM, Lutz Gehlen wrote:
> > 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.
> 
> Caveat, I am not a C programmer so I cannot comment on the
> correctness of the code. The question and it's phrasing look
> alright to me though. Your most recent post landed on a
> holiday(Labor Day) here in the States and therefore may have got
> lost in the return to work on Tuesday. Hopefully someone more
> knowledgeable then I will see this and comment on the C portion
> of your post.
> 
> > 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