Re: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

So you want to change the time? I don't see why that's what you'd want
to do, but:

UPDATE mytable SET mydate = mydate + INTERVAL '1 hour';

That's the only way to make '2006-12-20 00:00:00-02' show up as
'2006-12-20 00:00:00-03' if GMT-03 is the timezone your server is in,
because those are completely different times.

You should not be storing the timezone information if you just want the
relative time of day and not the absolute time.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: Rodrigo Sakai [mailto:rodrigo(dot)sakai(at)zanthus(dot)com(dot)br]
Sent: Thursday, December 21, 2006 1:37 PM
To: Brandon Aiken; 'Postgres general mailing list'
Subject: RES: [GENERAL] RESTORING A DATABASE WITH DIFFERENT TIMEZONES

Actually I want the server to behave in the time zone 'BRST' (this is
already configured). But the machine where the dump was done was with a
time
zone that is -2 from GMT! So I need to restore this dump in my server,
but
maintain the 00:00:00 at the hour part!

Is there a way???

-----Mensagem original-----
De: Brandon Aiken [mailto:BAiken(at)winemantech(dot)com]
Enviada em: quinta-feira, 21 de dezembro de 2006 14:00
Para: rodrigo(dot)sakai(at)zanthus(dot)com(dot)br; Postgres general mailing list
Assunto: RE: [GENERAL] RESTORING A DATABASE WITH DIFFERENT TIMEZONES

'2006-12-20 00:00:00-02' and '2006-12-19 23:00:00-03' *are* the same
time. You *did* preserve it. Is your application unaware of timezone?

If you want the server to behave like it's in a different time zone that
where it actually is, configure the locale in postgresql.conf.

http://www.postgresql.org/docs/8.2/interactive/runtime-config-client.htm
l#GUC-TIMEZONE

--
Brandon Aiken
CS/IT Systems Engineer
________________________________________
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rodrigo Sakai
Sent: Thursday, December 21, 2006 12:33 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] RESTORING A DATABASE WITH DIFFERENT TIMEZONES

Hi all,

I'm having some troubles with time zones! I have a database dump file
that have the date fields stored as '2006-12-20 00:00:00-02'!
And I have to restore it in a database that has the time zone configured
as 'BRST' (-3 from GMT). So, when it is restored the value becomes
'2006-12-19 23:00:00-03'. Ok this is logic because the difference of
time zones.

But I have to restore it and maintain the same value of datetime! How
can I do it?????

Thanks in advance!