Content-Length: 741982 | pFad | http://github.com/postgrespro/postgres/commit/c50838533bf4cca813e0a6f60808041f551ce606

87 Fix AT TIME ZONE (in all three variants) so that we first try to inte… · postgrespro/postgres@c508385 · GitHub
Skip to content

Commit c508385

Browse files
tglsfdctglsfdc
committed
Fix AT TIME ZONE (in all three variants) so that we first try to interpret
the timezone argument as a timezone abbreviation, and only try it as a full timezone name if that fails. The zic database has four zones (CET, EET, MET, WET) that are full daylight-savings zones and yet have names that are the same as their abbreviations for standard time, resulting in ambiguity. In the timestamp input functions we resolve the ambiguity by preferring the abbreviation, and AT TIME ZONE should work the same way. (No functionality is lost because the zic database also has other names for these zones, eg Europe/Zurich.) Per gripe from Jaromir Talir. Backpatch to 8.1. Older releases did not have the issue because AT TIME ZONE only accepted abbreviations not zone names. (Thus, this patch also arguably fixes a compatibility botch introduced at 8.1: in ambiguous cases we now behave the same as 8.0 did.)
1 parent 76c3c59 commit c508385

File tree

2 files changed

+100
-89
lines changed

2 files changed

+100
-89
lines changed

‎src/backend/utils/adt/date.c

Lines changed: 26 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.141 2008/03/25 22:42:43 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.142 2008/07/07 18:09:46 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2441,37 +2441,40 @@ timetz_zone(PG_FUNCTION_ARGS)
24412441
TimeTzADT *result;
24422442
int tz;
24432443
char tzname[TZ_STRLEN_MAX + 1];
2444+
char *lowzone;
2445+
int type,
2446+
val;
24442447
pg_tz *tzp;
24452448

24462449
/*
2447-
* Look up the requested timezone. First we look in the timezone database
2448-
* (to handle cases like "America/New_York"), and if that fails, we look
2449-
* in the date token table (to handle cases like "EST").
2450+
* Look up the requested timezone. First we look in the date token table
2451+
* (to handle cases like "EST"), and if that fails, we look in the
2452+
* timezone database (to handle cases like "America/New_York"). (This
2453+
* matches the order in which timestamp input checks the cases; it's
2454+
* important because the timezone database unwisely uses a few zone names
2455+
* that are identical to offset abbreviations.)
24502456
*/
24512457
text_to_cstring_buffer(zone, tzname, sizeof(tzname));
2452-
tzp = pg_tzset(tzname);
2453-
if (tzp)
2454-
{
2455-
/* Get the offset-from-GMT that is valid today for the selected zone */
2456-
pg_time_t now = (pg_time_t) time(NULL);
2457-
struct pg_tm *tm;
2458+
lowzone = downcase_truncate_identifier(tzname,
2459+
strlen(tzname),
2460+
false);
24582461

2459-
tm = pg_localtime(&now, tzp);
2460-
tz = -tm->tm_gmtoff;
2461-
}
2462+
type = DecodeSpecial(0, lowzone, &val);
2463+
2464+
if (type == TZ || type == DTZ)
2465+
tz = val * 60;
24622466
else
24632467
{
2464-
char *lowzone;
2465-
int type,
2466-
val;
2467-
2468-
lowzone = downcase_truncate_identifier(tzname,
2469-
strlen(tzname),
2470-
false);
2471-
type = DecodeSpecial(0, lowzone, &val);
2468+
tzp = pg_tzset(tzname);
2469+
if (tzp)
2470+
{
2471+
/* Get the offset-from-GMT that is valid today for the zone */
2472+
pg_time_t now = (pg_time_t) time(NULL);
2473+
struct pg_tm *tm;
24722474

2473-
if (type == TZ || type == DTZ)
2474-
tz = val * 60;
2475+
tm = pg_localtime(&now, tzp);
2476+
tz = -tm->tm_gmtoff;
2477+
}
24752478
else
24762479
{
24772480
ereport(ERROR,

‎src/backend/utils/adt/timestamp.c

Lines changed: 74 additions & 66 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.189 2008/05/04 23:19:23 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.190 2008/07/07 18:09:46 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -4410,58 +4410,62 @@ timestamp_zone(PG_FUNCTION_ARGS)
44104410
Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
44114411
TimestampTz result;
44124412
int tz;
4413-
pg_tz *tzp;
44144413
char tzname[TZ_STRLEN_MAX + 1];
4414+
char *lowzone;
4415+
int type,
4416+
val;
4417+
pg_tz *tzp;
44154418

44164419
if (TIMESTAMP_NOT_FINITE(timestamp))
44174420
PG_RETURN_TIMESTAMPTZ(timestamp);
44184421

44194422
/*
4420-
* Look up the requested timezone. First we look in the timezone database
4421-
* (to handle cases like "America/New_York"), and if that fails, we look
4422-
* in the date token table (to handle cases like "EST").
4423+
* Look up the requested timezone. First we look in the date token table
4424+
* (to handle cases like "EST"), and if that fails, we look in the
4425+
* timezone database (to handle cases like "America/New_York"). (This
4426+
* matches the order in which timestamp input checks the cases; it's
4427+
* important because the timezone database unwisely uses a few zone names
4428+
* that are identical to offset abbreviations.)
44234429
*/
44244430
text_to_cstring_buffer(zone, tzname, sizeof(tzname));
4425-
tzp = pg_tzset(tzname);
4426-
if (tzp)
4427-
{
4428-
/* Apply the timezone change */
4429-
struct pg_tm tm;
4430-
fsec_t fsec;
4431+
lowzone = downcase_truncate_identifier(tzname,
4432+
strlen(tzname),
4433+
false);
44314434

4432-
if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
4433-
ereport(ERROR,
4434-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4435-
errmsg("timestamp out of range")));
4436-
tz = DetermineTimeZoneOffset(&tm, tzp);
4437-
if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
4438-
ereport(ERROR,
4439-
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4440-
errmsg("could not convert to time zone \"%s\"",
4441-
tzname)));
4435+
type = DecodeSpecial(0, lowzone, &val);
4436+
4437+
if (type == TZ || type == DTZ)
4438+
{
4439+
tz = -(val * 60);
4440+
result = dt2local(timestamp, tz);
44424441
}
44434442
else
44444443
{
4445-
char *lowzone;
4446-
int type,
4447-
val;
4448-
4449-
lowzone = downcase_truncate_identifier(tzname,
4450-
strlen(tzname),
4451-
false);
4452-
type = DecodeSpecial(0, lowzone, &val);
4444+
tzp = pg_tzset(tzname);
4445+
if (tzp)
4446+
{
4447+
/* Apply the timezone change */
4448+
struct pg_tm tm;
4449+
fsec_t fsec;
44534450

4454-
if (type == TZ || type == DTZ)
4455-
tz = -(val * 60);
4451+
if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
4452+
ereport(ERROR,
4453+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4454+
errmsg("timestamp out of range")));
4455+
tz = DetermineTimeZoneOffset(&tm, tzp);
4456+
if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
4457+
ereport(ERROR,
4458+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4459+
errmsg("could not convert to time zone \"%s\"",
4460+
tzname)));
4461+
}
44564462
else
44574463
{
44584464
ereport(ERROR,
44594465
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
44604466
errmsg("time zone \"%s\" not recognized", tzname)));
4461-
tz = 0; /* keep compiler quiet */
4467+
result = 0; /* keep compiler quiet */
44624468
}
4463-
4464-
result = dt2local(timestamp, tz);
44654469
}
44664470

44674471
PG_RETURN_TIMESTAMPTZ(result);
@@ -4580,57 +4584,61 @@ timestamptz_zone(PG_FUNCTION_ARGS)
45804584
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
45814585
Timestamp result;
45824586
int tz;
4583-
pg_tz *tzp;
45844587
char tzname[TZ_STRLEN_MAX + 1];
4588+
char *lowzone;
4589+
int type,
4590+
val;
4591+
pg_tz *tzp;
45854592

45864593
if (TIMESTAMP_NOT_FINITE(timestamp))
45874594
PG_RETURN_TIMESTAMP(timestamp);
45884595

45894596
/*
4590-
* Look up the requested timezone. First we look in the timezone database
4591-
* (to handle cases like "America/New_York"), and if that fails, we look
4592-
* in the date token table (to handle cases like "EST").
4597+
* Look up the requested timezone. First we look in the date token table
4598+
* (to handle cases like "EST"), and if that fails, we look in the
4599+
* timezone database (to handle cases like "America/New_York"). (This
4600+
* matches the order in which timestamp input checks the cases; it's
4601+
* important because the timezone database unwisely uses a few zone names
4602+
* that are identical to offset abbreviations.)
45934603
*/
45944604
text_to_cstring_buffer(zone, tzname, sizeof(tzname));
4595-
tzp = pg_tzset(tzname);
4596-
if (tzp)
4597-
{
4598-
/* Apply the timezone change */
4599-
struct pg_tm tm;
4600-
fsec_t fsec;
4605+
lowzone = downcase_truncate_identifier(tzname,
4606+
strlen(tzname),
4607+
false);
46014608

4602-
if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0)
4603-
ereport(ERROR,
4604-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4605-
errmsg("timestamp out of range")));
4606-
if (tm2timestamp(&tm, fsec, NULL, &result) != 0)
4607-
ereport(ERROR,
4608-
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4609-
errmsg("could not convert to time zone \"%s\"",
4610-
tzname)));
4609+
type = DecodeSpecial(0, lowzone, &val);
4610+
4611+
if (type == TZ || type == DTZ)
4612+
{
4613+
tz = val * 60;
4614+
result = dt2local(timestamp, tz);
46114615
}
46124616
else
46134617
{
4614-
char *lowzone;
4615-
int type,
4616-
val;
4617-
4618-
lowzone = downcase_truncate_identifier(tzname,
4619-
strlen(tzname),
4620-
false);
4621-
type = DecodeSpecial(0, lowzone, &val);
4618+
tzp = pg_tzset(tzname);
4619+
if (tzp)
4620+
{
4621+
/* Apply the timezone change */
4622+
struct pg_tm tm;
4623+
fsec_t fsec;
46224624

4623-
if (type == TZ || type == DTZ)
4624-
tz = val * 60;
4625+
if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0)
4626+
ereport(ERROR,
4627+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4628+
errmsg("timestamp out of range")));
4629+
if (tm2timestamp(&tm, fsec, NULL, &result) != 0)
4630+
ereport(ERROR,
4631+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4632+
errmsg("could not convert to time zone \"%s\"",
4633+
tzname)));
4634+
}
46254635
else
46264636
{
46274637
ereport(ERROR,
46284638
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
46294639
errmsg("time zone \"%s\" not recognized", tzname)));
4630-
tz = 0; /* keep compiler quiet */
4640+
result = 0; /* keep compiler quiet */
46314641
}
4632-
4633-
result = dt2local(timestamp, tz);
46344642
}
46354643

46364644
PG_RETURN_TIMESTAMP(result);

0 commit comments

Comments
 (0)








ApplySandwichStrip

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier!      Saves Data!


--- a PPN by Garber Painting Akron. With Image Size Reduction included!

Fetched URL: http://github.com/postgrespro/postgres/commit/c50838533bf4cca813e0a6f60808041f551ce606

Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy