I had to write obscure code for processing time zone and using it for
timestamptz

Datum
make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
{
        Timestamp timestamp;
        text       *zone;
        int             tz;
        char            tzname[TZ_STRLEN_MAX + 1];
        char       *lowzone;
        int                     type,
                                val;
        struct pg_tm tt,
                           *tm = &tt;
        fsec_t          fsec;
        TimestampTz result;
        int                     session_tz;

        timestamp = make_timestamp_internal(PG_GETARG_INT32(0), /* year */
                                         PG_GETARG_INT32(1),    /* month */
                                         PG_GETARG_INT32(2),    /* mday */
                                         PG_GETARG_INT32(3),    /* hour */
                                         PG_GETARG_INT32(4),    /* min */
                                         PG_GETARG_FLOAT8(5));  /* sec */

        if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
                ereport(ERROR,

(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                                 errmsg("timestamp out of range")));

        zone = PG_GETARG_TEXT_PP(6);

        text_to_cstring_buffer(zone, tzname, sizeof(tzname));

        if (DecodeTimezone(tzname, &tz) != 0)
        {
                lowzone = downcase_truncate_identifier(tzname,

strlen(tzname),

false);
                type = DecodeSpecial(0, lowzone, &val);


                if (type == TZ || type == DTZ)
                        tz = val * MINS_PER_HOUR;
                else
                {
                        pg_tz      *tzp;

                        tzp = pg_tzset(tzname);

                        if (tzp)
                                tz = DetermineTimeZoneOffset(tm, tzp);
                        else
                        {
                                ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                                                 errmsg("time zone \"%s\"
not recognized", tzname)));
                                tz =
0;                                         /* keep compiler quiet */
                        }
                }
        }

elog(NOTICE, "entry 0: %d", tz);

        session_tz = DetermineTimeZoneOffset(tm, session_timezone);

        PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz));
}

It works

postgres=# select make_timestamptz(2014,12,17,21,06,37.7,'Europe/Moscow') ;
     make_timestamptz
--------------------------
 2014-12-17 18:06:37.7+01
(1 row)

postgres=# select '2014-12-17 21:06:37.7 Europe/Moscow'::timestamptz;
       timestamptz
--------------------------
 2014-12-17 18:06:37.7+01
(1 row)

Is some better way, how to parse time zone?

Regards

Pavel Stehule

Reply via email to