timestamp arithmetics in C function

Поиск
Список
Период
Сортировка
От Lutz Gehlen
Тема timestamp arithmetics in C function
Дата
Msg-id 2270498.s9YACoBxua@fresco
обсуждение исходный текст
Ответы Re: timestamp arithmetics in C function  (Lutz Gehlen <lrg_ml@gmx.net>)
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Do we need yet another IDE (SQL development assistant) forPostgreSQL?
Следующее
От: Adrien NAYRAT
Дата:
Сообщение: Re: Postgres - search for value throughout many tables?