diff options
| -rw-r--r-- | src/backend/utils/adt/formatting.c | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/timestamptz.out | 51 | ||||
| -rw-r--r-- | src/test/regress/sql/timestamptz.sql | 17 |
3 files changed, 75 insertions, 4 deletions
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 2b5622a9ee0..c90f48d00a6 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -2506,12 +2506,15 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col break; case DCH_OF: INVALID_FOR_INTERVAL; - sprintf(s, "%+0*d", S_FM(n->suffix) ? 0 : (tm->tm_gmtoff >= 0) ? 3 : 4, - (int) tm->tm_gmtoff / SECS_PER_HOUR); + sprintf(s, "%c%0*d", + (tm->tm_gmtoff >= 0) ? '+' : '-', + S_FM(n->suffix) ? 0 : 2, + abs((int) tm->tm_gmtoff) / SECS_PER_HOUR); s += strlen(s); - if ((int) tm->tm_gmtoff % SECS_PER_HOUR != 0) + if (abs((int) tm->tm_gmtoff) % SECS_PER_HOUR != 0) { - sprintf(s, ":%02d", abs((int) tm->tm_gmtoff % SECS_PER_HOUR) / SECS_PER_MINUTE); + sprintf(s, ":%02d", + (abs((int) tm->tm_gmtoff) % SECS_PER_HOUR) / SECS_PER_MINUTE); s += strlen(s); } break; diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index fffcaf4bf8d..271873d326d 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1699,6 +1699,57 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') | 2001 1 1 1 1 1 1 (66 rows) +-- Check OF with various zone offsets, particularly fractional hours +SET timezone = '00:00'; +SELECT to_char(now(), 'OF'); + to_char +--------- + +00 +(1 row) + +SET timezone = '+02:00'; +SELECT to_char(now(), 'OF'); + to_char +--------- + -02 +(1 row) + +SET timezone = '-13:00'; +SELECT to_char(now(), 'OF'); + to_char +--------- + +13 +(1 row) + +SET timezone = '-00:30'; +SELECT to_char(now(), 'OF'); + to_char +--------- + +00:30 +(1 row) + +SET timezone = '00:30'; +SELECT to_char(now(), 'OF'); + to_char +--------- + -00:30 +(1 row) + +SET timezone = '-04:30'; +SELECT to_char(now(), 'OF'); + to_char +--------- + +04:30 +(1 row) + +SET timezone = '04:30'; +SELECT to_char(now(), 'OF'); + to_char +--------- + -04:30 +(1 row) + +RESET timezone; CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); -- Test year field value with len > 4 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST'); diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index 03dbc05aab7..5ec92e55c8f 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -248,6 +248,23 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID') SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') FROM TIMESTAMPTZ_TBL; +-- Check OF with various zone offsets, particularly fractional hours +SET timezone = '00:00'; +SELECT to_char(now(), 'OF'); +SET timezone = '+02:00'; +SELECT to_char(now(), 'OF'); +SET timezone = '-13:00'; +SELECT to_char(now(), 'OF'); +SET timezone = '-00:30'; +SELECT to_char(now(), 'OF'); +SET timezone = '00:30'; +SELECT to_char(now(), 'OF'); +SET timezone = '-04:30'; +SELECT to_char(now(), 'OF'); +SET timezone = '04:30'; +SELECT to_char(now(), 'OF'); +RESET timezone; + CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); -- Test year field value with len > 4 |