Thread: timestamp arithmetics in C function
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
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
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
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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