Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit172ba45

Browse files
committed
Fix some odd behaviors when using a SQL-style simple GMT offset timezone.
Formerly, when using a SQL-spec timezone setting with a fixed GMT offset(called a "brute force" timezone in the code), the session_timezonevariable was not updated to match the nominal timezone; rather, all codewas expected to ignore session_timezone if HasCTZSet was true. This isof course obviously fragile, though a search of the code finds onlytimeofday() failing to honor the rule. A bigger problem was thatDetermineTimeZoneOffset() supposed that if its pg_tz parameter waspointer-equal to session_timezone, then HasCTZSet should override theparameter. This would cause datetime input containing an explicit zonename to be treated as referencing the brute-force zone instead, if thezone name happened to match the session timezone that had prevailedbefore installing the brute-force zone setting (as reported in bug #8572).The same malady could affect AT TIME ZONE operators.To fix, set up session_timezone so that it matches the brute-force zonespecification, which we can do using the POSIX timezone definition syntax"<abbrev>offset", and get rid of the bogus lookaside check inDetermineTimeZoneOffset(). Aside from fixing the erroneous behavior indatetime parsing and AT TIME ZONE, this will cause the timeofday() functionto print its result in the user-requested time zone rather than somepreviously-set zone. It might also affect results in third-partyextensions, if there are any that make use of session_timezone withoutconsidering HasCTZSet, but in all cases the new behavior should be sanerthan before.Back-patch to all supported branches.
1 parent4bf70c0 commit172ba45

File tree

6 files changed

+90
-6
lines changed

6 files changed

+90
-6
lines changed

‎src/backend/commands/variable.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -342,6 +342,7 @@ check_timezone(char **newval, void **extra, GucSource source)
342342
#else
343343
myextra.CTimeZone=-interval->time;
344344
#endif
345+
myextra.session_timezone=pg_tzset_offset(myextra.CTimeZone);
345346
myextra.HasCTZSet= true;
346347

347348
pfree(interval);
@@ -356,6 +357,7 @@ check_timezone(char **newval, void **extra, GucSource source)
356357
{
357358
/* Here we change from SQL to Unix sign convention */
358359
myextra.CTimeZone=-hours*SECS_PER_HOUR;
360+
myextra.session_timezone=pg_tzset_offset(myextra.CTimeZone);
359361
myextra.HasCTZSet= true;
360362
}
361363
else

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

Lines changed: 0 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1447,12 +1447,6 @@ DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp)
14471447
after_isdst;
14481448
intres;
14491449

1450-
if (tzp==session_timezone&&HasCTZSet)
1451-
{
1452-
tm->tm_isdst=0;/* for lack of a better idea */
1453-
returnCTimeZone;
1454-
}
1455-
14561450
/*
14571451
* First, generate the pg_time_t value corresponding to the given
14581452
* y/m/d/h/m/s taken as GMT time. If this overflows, punt and decide the

‎src/include/pgtime.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,8 @@ extern size_t pg_strftime(char *s, size_t max, const char *format,
5555
externvoidpg_timezone_pre_initialize(void);
5656
externvoidpg_timezone_initialize(void);
5757
externpg_tz*pg_tzset(constchar*tzname);
58+
externpg_tz*pg_tzset_offset(longgmtoffset);
59+
5860
externbooltz_acceptable(pg_tz*tz);
5961
externboolpg_get_timezone_offset(constpg_tz*tz,longint*gmtoff);
6062
externconstchar*pg_get_timezone_name(pg_tz*tz);

‎src/test/regress/expected/horology.out

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2899,3 +2899,33 @@ DETAIL: Value must be an integer.
28992899
SELECT to_timestamp('10000000000', 'FMYYYY');
29002900
ERROR: value for "YYYY" in source string is out of range
29012901
DETAIL: Value must be in the range -2147483648 to 2147483647.
2902+
--
2903+
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
2904+
--
2905+
SET TIME ZONE 'America/New_York';
2906+
SET TIME ZONE '-1.5';
2907+
SHOW TIME ZONE;
2908+
TimeZone
2909+
----------------------
2910+
@ 1 hour 30 mins ago
2911+
(1 row)
2912+
2913+
SELECT '2012-12-12 12:00'::timestamptz;
2914+
timestamptz
2915+
---------------------------------
2916+
Wed Dec 12 12:00:00 2012 -01:30
2917+
(1 row)
2918+
2919+
SELECT '2012-12-12 12:00 America/New_York'::timestamptz;
2920+
timestamptz
2921+
---------------------------------
2922+
Wed Dec 12 15:30:00 2012 -01:30
2923+
(1 row)
2924+
2925+
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
2926+
to_char
2927+
----------------------
2928+
2012-12-12 12:00:00
2929+
(1 row)
2930+
2931+
RESET TIME ZONE;

‎src/test/regress/sql/horology.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -455,3 +455,19 @@ SELECT to_timestamp('199711xy', 'YYYYMMDD');
455455

456456
-- Input that doesn't fit in an int:
457457
SELECT to_timestamp('10000000000','FMYYYY');
458+
459+
--
460+
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
461+
--
462+
463+
SETTIME ZONE'America/New_York';
464+
SETTIME ZONE'-1.5';
465+
466+
SHOWTIME ZONE;
467+
468+
SELECT'2012-12-12 12:00'::timestamptz;
469+
SELECT'2012-12-12 12:00 America/New_York'::timestamptz;
470+
471+
SELECT to_char('2012-12-12 12:00'::timestamptz,'YYYY-MM-DD HH:MI:SS TZ');
472+
473+
RESETTIME ZONE;

‎src/timezone/pgtz.c

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1325,6 +1325,46 @@ pg_tzset(const char *name)
13251325
return&tzp->tz;
13261326
}
13271327

1328+
/*
1329+
* Load a fixed-GMT-offset timezone.
1330+
* This is used for SQL-spec SET TIME ZONE INTERVAL 'foo' cases.
1331+
* It's otherwise equivalent to pg_tzset().
1332+
*
1333+
* The GMT offset is specified in seconds, positive values meaning west of
1334+
* Greenwich (ie, POSIX not ISO sign convention). However, we use ISO
1335+
* sign convention in the displayable abbreviation for the zone.
1336+
*/
1337+
pg_tz*
1338+
pg_tzset_offset(longgmtoffset)
1339+
{
1340+
longabsoffset= (gmtoffset<0) ?-gmtoffset :gmtoffset;
1341+
charoffsetstr[64];
1342+
chartzname[128];
1343+
1344+
snprintf(offsetstr,sizeof(offsetstr),
1345+
"%02ld",absoffset /SECSPERHOUR);
1346+
absoffset %=SECSPERHOUR;
1347+
if (absoffset!=0)
1348+
{
1349+
snprintf(offsetstr+strlen(offsetstr),
1350+
sizeof(offsetstr)-strlen(offsetstr),
1351+
":%02ld",absoffset /SECSPERMIN);
1352+
absoffset %=SECSPERMIN;
1353+
if (absoffset!=0)
1354+
snprintf(offsetstr+strlen(offsetstr),
1355+
sizeof(offsetstr)-strlen(offsetstr),
1356+
":%02ld",absoffset);
1357+
}
1358+
if (gmtoffset>0)
1359+
snprintf(tzname,sizeof(tzname),"<-%s>+%s",
1360+
offsetstr,offsetstr);
1361+
else
1362+
snprintf(tzname,sizeof(tzname),"<+%s>-%s",
1363+
offsetstr,offsetstr);
1364+
1365+
returnpg_tzset(tzname);
1366+
}
1367+
13281368

13291369
/*
13301370
* Check whether timezone is acceptable.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp