Thread: Question about 'copy' and datetime

Question about 'copy' and datetime

From
Carolyn Lu Wong
Date:
I'm using V6.5.

I find that when I try to restore a backup database (done by pg_dump),
the datetime value seem inconsistent. I've checked the dumped file,
the data seems alright. However, the imported date is +16hr of the
date in the data file.

The system uses EST. The data dumped also has EST postfix. Is this a
bug or there are other options I need to set?

fyi, the command used in the backup script
pg_dump -c -u -f $BACKUP_FILE $DATABASE

and the command used in the restore script
psql -e -u -f $BACKUP_FILE  $DATABASE


Re: Question about 'copy' and datetime

From
Tom Lane
Date:
Carolyn Lu Wong <carolyn@greatpacific.com.au> writes:
> I find that when I try to restore a backup database (done by pg_dump),
> the datetime value seem inconsistent. I've checked the dumped file,
> the data seems alright. However, the imported date is +16hr of the
> date in the data file.

> The system uses EST. The data dumped also has EST postfix. Is this a
> bug or there are other options I need to set?

Given your return address, I'm betting this is a conflict between USA
and Aussie interpretations of "EST".  You probably need to recompile
with USE_AUSTRALIAN_RULES #defined.  See
http://www.postgresql.org/users-lounge/docs/7.0/postgres/datetime-appendix.htm
        regards, tom lane