Asia/Jakarta Timezone problem - Mailing list pgsql-bugs

Hi,all

Our customer use the pg in the Asia/Jakarta Zone. We encounter the following
problem:

1. Change the system time to Asia/Jakarta in suse12 sp2.

# cp /usr/share/zoneinfo/Asia/Jakarta /etc/localtime
# date
Thu May  3 13:25:32 WIB 2018
date -R
Thu, 03 May 2018 13:25:34 +0700

Check system time zone:
# cat /etc/SuSE-release
SUSE Linux Enterprise Server 12 (x86_64)
VERSION = 12
PATCHLEVEL = 2
# This file is deprecated and will be removed in a future service pack or
release.
# Please check /etc/os-release for details about this release.

2. Install pg10 version database.

./configure
make clean && make –sj && make install
initdb
pg_ctl start

psql -d postgres -p 25432
psql (10.0)
Type "help" for help.

postgres=# select version();
                                      version
------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5,
64-bit
(1 row)

postgres=# show timezone;
   TimeZone
--------------
 Asia/Jakarta
(1 row)

postgres=# select current_timestamp;
       current_timestamp
-------------------------------
 2018-05-03 13:31:05.121978+07

3.  Create table, insert timestamp data with WIB or WIT,* failed*.

execute the following SQL

create table tab_dcn_svo_securtity_group(id varchar(128) not null,
create_time timestamp with time zone default current_timestamp);
insert into  tab_dcn_svo_securtity_group values('abcwithoutzone','Thu May  3
10:10:10 WIB 2018');

The error information is:

2018-05-03 13:33:29.744 WIB [89872] ERROR:  invalid input syntax for type
timestamp with time zone: "Thu May  3 10:10:10 WIB 2018" at character 66
2018-05-03 13:33:29.744 WIB [89872] STATEMENT:  insert into
tab_dcn_svo_securtity_group values('abcwithoutzone','Thu May  3 10:10:10 WIB
2018');
ERROR:  invalid input syntax for type timestamp with time zone: "Thu May  3
10:10:10 WIB 2018"
LINE 1: ...b_dcn_svo_securtity_group values('abcwithoutzone','Thu May

4. Successful insertion of time data without time zone or data with
Asia/Jakarta

postgres=# select * from  tab_dcn_svo_securtity_group;
       id        |      create_time
-----------------+------------------------
 abcwithoutzone1 | 2018-05-03 11:10:10+07
(1 row)

postgres=# insert into tab_dcn_svo_securtity_group
values('abcwithoutzone','Thu May 3 11:10:10 2018');
INSERT 0 1
postgres=# select * from  tab_dcn_svo_securtity_group;
       id        |      create_time
-----------------+------------------------
 abcwithoutzone1 | 2018-05-03 11:10:10+07
 abcwithoutzone  | 2018-05-03 11:10:10+07

5. We check some other timezone, some is OK, and some is falied

 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+9 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-6 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+6 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+12 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+2 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-2 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 GMT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-3 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+3 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 UTC
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-1 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-4 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+5 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-10 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-8 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-14 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+1 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-9 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+4 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-11 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-7 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-12 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+8 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+7 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 UCT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+11 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-13 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT-5 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GMT+10 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
WEST 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 EET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MSK
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
IRDT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 EDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 EST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
AKDT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 HST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 SST
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
HADT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
CEST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CLT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
DAVT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 WST
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
SYOT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
MIST 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
VOST 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
DDUT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
NZST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
MAWT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
ROTT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 AST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 GYT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 AMT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
MeST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 ART
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 BRT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 SRT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 NDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 ADT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 UYT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PYT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 GFT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 FNT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 VET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 COT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
EGST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
PMDT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 ECT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
WARST 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
WGST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 BOT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 HKT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 JST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 BST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 WET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CAT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
SAST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 EAT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 WAT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 IST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MHT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 IDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
AZOST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
FKST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 GST
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CVT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PKT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 UZT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MYT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
YEKT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 EIT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 NPT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
ORAT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
ANAT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TMT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 ICT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 KST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
HOVT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 WIT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TJT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
PETT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 BTT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
VLAT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
CHOT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
NOVT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CIT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 BNT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
AZST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 BDT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TLT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 AFT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
MAGT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
AQTT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 GET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
IRKT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 KGT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MMT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
YAKT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
KRAT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
QYZT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
ULAT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
OMST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PHT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 SGT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
SAKT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
ALMT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 IOT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CCT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 SCT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MUT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TFT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 RET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CXT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 MVT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
CWST 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
LHST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
PONT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
ChST 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
KOST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PWT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
CHAST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 NFT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
PHOT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TVT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
LINT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TKT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 TOT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 NRT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 VUT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
CHUT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PST
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 FJT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 WFT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GALT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 NUT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
MART 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 CKT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
WAKT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
TAHT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 NCT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
EAST 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GILT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 PGT
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
GAMT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 SBT
2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
SAMT 2018');--failed
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22 FET
2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
VOLT 2018');
 insert into tab_dcn_svo_securtity_group values('1','Thu May  3 11:25:22
MEST 2018');






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html