Thread: Bad timestamp external representation
After reading the mailing list, I still can't get the answer to this:
ERROR: copy: line 1, Bad timestamp external representation '28/04/2002
18:15:37.00 SGT'
lost synchronization with server, resetting connection
I'm using pg_dump from 7.1 to 7.2. When in postgresql, I run "select
current_timestamp;" and it came out:
timestamptz
--------------------------------
27/11/2002 21:40:46.602827 SGT
(1 row)
This is due to running "export PGDATESTYLE='european,sql' before postmaster.
So, I guess SGT is being recognised. However, when I changed the SGT to +08
in the dumped file, it works.
Can anyone help me?
Jim Worke <jimworke@inbox.lv> writes:
> I'm using pg_dump from 7.1 to 7.2. When in postgresql, I run "select
> current_timestamp;" and it came out:
> timestamptz
> --------------------------------
> 27/11/2002 21:40:46.602827 SGT
> (1 row)
> This is due to running "export PGDATESTYLE='european,sql' before postmaster.
> So, I guess SGT is being recognised.
Depressingly enough, the appearance of SGT in a displayed timestamp
doesn't necessarily mean we'll accept it on input :-(.
IIRC, what you see in the displayed output is whatever the system
reports as its local timezone name, but what can be recognized on input
is what's listed in the table in src/backend/utils/adt/datetime.c.
And there's only a commented-out entry for SGT --- apparently Thomas
either couldn't find a definition for it, or found multiple conflicting
definitions.
What do you think SGT is?
As far as getting your dump to work is concerned, I'd suggest setting
export PGTZ=GMT
before running pg_dump; that should persuade it to dump everything in
GMT.
regards, tom lane
On Thursday 28 November 2002 8:35 am, Tom Lane wrote: > Jim Worke <jimworke@inbox.lv> writes: > > I'm using pg_dump from 7.1 to 7.2. When in postgresql, I run "select > > current_timestamp;" and it came out: > > timestamptz > > -------------------------------- > > 27/11/2002 21:40:46.602827 SGT > > (1 row) > > > > This is due to running "export PGDATESTYLE='european,sql' before > > postmaster. So, I guess SGT is being recognised. > > Depressingly enough, the appearance of SGT in a displayed timestamp > doesn't necessarily mean we'll accept it on input :-(. > > IIRC, what you see in the displayed output is whatever the system > reports as its local timezone name, but what can be recognized on input > is what's listed in the table in src/backend/utils/adt/datetime.c. > And there's only a commented-out entry for SGT --- apparently Thomas > either couldn't find a definition for it, or found multiple conflicting > definitions. > > What do you think SGT is? > > As far as getting your dump to work is concerned, I'd suggest setting > export PGTZ=GMT > before running pg_dump; that should persuade it to dump everything in > GMT. > > regards, tom lane I believe SGT is for Singapore time, no? Anyway, that aside, I've successfully migrated the data using your method, i.e. using export PGTZ=GMT. The displayed time is now correct, just that it displays the SGT (in 7.2). So, does SGT being recognised then? For example, in the dump is: 22/01/2002 12:02:16.00 GMT and in the database is (using select statement): 22/01/2002 20:02:16.00 SGT Correct me if I'm wrong: postgresql doesn't know about SGT. When my dump in GMT is imported and displayed, it will be displayed using the default system's timezone, which would be SGT? Is that correct? If not, where can I read about this? Thank you
Jim Worke <jimworke@inbox.lv> writes:
> On Thursday 28 November 2002 8:35 am, Tom Lane wrote:
>> What do you think SGT is?
> I believe SGT is for Singapore time, no?
Okay, but what exactly is the offset from GMT? And is there any
daylight-savings convention there?
Looking in the zic database, I find
# Singapore
# Zone NAME GMTOFF RULES FORMAT [UNTIL]
Zone Asia/Singapore 6:55:24 - LMT 1880
6:55:24 - SMT 1905 Jun # Singapore Mean Time
7:00 - MALT 1933 # Malaya Time
7:20 - MALT 1942 Feb 15
9:00 - JST 1945 Sep 2
7:20 - MALT 1950
7:30 - MALT 1965 Aug 9 # independence
7:30 - SGT 1982 May # Singapore Time
8:00 - SGT
and no other entries defining "SGT". So it seems it'd be reasonable to
define SGT as GMT+8, although strictly speaking this'd be incorrect for
years before 1982.
Comments?
regards, tom lane