8000 Fix assorted breakage in to_char()'s OF format option. · prmdeveloper/postgres@d0a585c · GitHub
[go: up one dir, main page]
Skip to content

Commit d0a585c

Browse files
tglsfdctglsfdc
committed
Fix assorted breakage in to_char()'s OF format option.
In HEAD, fix incorrect field width for hours part of OF when tm_gmtoff is negative. This was introduced by commit 2d87eed as a result of falsely applying a pattern that's correct when + signs are omitted, which is not the case for OF. In 9.4, fix missing abs() call that allowed a sign to be attached to the minutes part of OF. This was fixed in 9.5 by 9b43d73, but for inscrutable reasons not back-patched. In all three versions, ensure that the sign of tm_gmtoff is correctly reported even when the GMT offset is less than 1 hour. Add regression tests, which evidently we desperately need here. Thomas Munro and Tom Lane, per report from David Fetter
1 parent 8ee2e5f commit d0a585c

File tree

3 files changed

+75
-3
lines changed

3 files changed

+75
-3
lines changed

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

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2503,11 +2503,15 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
25032503
break;
25042504
case DCH_OF:
25052505
INVALID_FOR_INTERVAL;
2506-
sprintf(s, "%+0*d", S_FM(n->suffix) ? 0 : 3, (int) tm->tm_gmtoff / SECS_PER_HOUR);
2506+
sprintf(s, "%c%0*d",
2507+
(tm->tm_gmtoff >= 0) ? '+' : '-',
2508+
S_FM(n->suffix) ? 0 : 2,
2509+
abs((int) tm->tm_gmtoff) / SECS_PER_HOUR);
25072510
s += strlen(s);
2508-
if ((int) tm->tm_gmtoff % SECS_PER_HOUR != 0)
2511+
if (abs((int) tm->tm_gmtoff) % SECS_PER_HOUR != 0)
25092512
{
2510-
sprintf(s, ":%02d", abs((int) tm->tm_gmtoff % SECS_PER_HOUR) / SECS_PER_MINUTE);
2513+
sprintf(s, ":%02d",
2514+
(abs((int) tm->tm_gmtoff) % SECS_PER_HOUR) / SECS_PER_MINUTE);
25112515
s += strlen(s);
25122516
}
25132517
break;

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

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1675,6 +1675,57 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
16751675
| 2001 1 1 1 1 1 1
16761676
(66 rows)
16771677

1678+
-- Check OF with various zone offsets, particularly fractional hours
1679+
SET timezone = '00:00';
1680+
SELECT to_char(now(), 'OF');
1681+
to_char
1682+
---------
1683+
+00
1684+
(1 row)
1685+
1686+
SET timezone = '+02:00';
1687+
SELECT to_char(now(), 'OF');
1688+
to_char
1689+
---------
1690+
-02
1691+
(1 row)
1692+
1693+
SET timezone = '-13:00';
1694+
SELECT to_char(now(), 'OF');
1695+
to_char
1696+
---------
1697+
+13
1698+
(1 row)
1699+
1700+
SET timezone = '-00:30';
1701+
SELECT to_char(now(), 'OF');
1702+
to_char
1703+
---------
1704+
+00:30
1705+
(1 row)
1706+
1707+
SET timezone = '00:30';
1708+
SELECT to_char(now(), 'OF');
1709+
to_char
1710+
---------
1711+
-00:30
1712+
(1 row)
1713+
1714+
SET timezone = '-04:30';
1715+
SELECT to_char(now(), 'OF');
1716+
to_char
1717+
---------
1718+
+04:30
1719+
(1 row)
1720+
1721+
SET timezone = '04:30';
1722+
SELECT to_char(now(), 'OF');
1723+
to_char
1724+
---------
1725+
-04:30
1726+
(1 row)
1727+
1728+
RESET timezone;
16781729
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
16791730
-- Test year field value with len > 4
16801731
INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');

‎src/test/regress/sql/timestamptz.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,6 +241,23 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
241241
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
242242
FROM TIMESTAMPTZ_TBL;
243243

244+
-- Check OF with various zone offsets, particularly fractional hours
245+
SET timezone = '00:00';
246+
SELECT to_char(now(), 'OF');
247+
SET timezone = '+02:00';
248+
SELECT to_char(now(), 'OF');
249+
SET timezone = '-13:00';
250+
SELECT to_char(now(), 'OF');
251+
SET timezone = '-00:30';
252+
SELECT to_char(now(), 'OF');
253+
SET timezone = '00:30';
254+
SELECT to_char(now(), 'OF');
255+
SET timezone = '-04:30';
256+
SELECT to_char(now(), 'OF');
257+
SET timezone = '04:30';
258+
SELECT to_char(now(), 'OF');
259+
RESET timezone;
260+
244261
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
245262

246263
-- Test year field value with len > 4

0 commit comments

Comments
 (0)
0