processing time zone

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: processing time zone
Date: 2013-12-17 21:05:43
Message-ID: CAFj8pRAyWMsdNnpRz4RKrBa-erJHzwCbq9C7=r_s_SdtguP8oA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2013-12-17 21:26:09 Re: [PATCH] SQL assertions prototype
Previous Message Robert Haas 2013-12-17 21:00:14 Re: preserving forensic information when we freeze