Обсуждение: 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