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
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 |