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
