Thread: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

RESTORING A DATABASE WITH DIFFERENT TIMEZONES

From
"Rodrigo Sakai"
Date:

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!

Re: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

From
"Brandon Aiken"
Date:
'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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rodrigo Sakai
Sent: Thursday, December 21, 2006 12:33 PM
To: pgsql-general@postgresql.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!

RES: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

From
"Rodrigo Sakai"
Date:
  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@winemantech.com]
Enviada em: quinta-feira, 21 de dezembro de 2006 14:00
Para: rodrigo.sakai@zanthus.com.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rodrigo Sakai
Sent: Thursday, December 21, 2006 12:33 PM
To: pgsql-general@postgresql.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!


Re: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

From
Michael Glaesemann
Date:
On Dec 21, 2006, at 11:32 , Rodrigo Sakai wrote:
> But I have to restore it and maintain the same value of datetime!
> How can I do it?????

You should have no problem. A timestamp with time zone marks an
absolute instant in time: the different time zones are different
representations of the same instant.

Michael Glaesemann
grzm seespotcode net



Re: RES: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

From
Richard Huxton
Date:
Rodrigo Sakai wrote:
>   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???


icondirect=> SET TimeZone = 'America/New_York';
SET
icondirect=> SELECT now();
               now
-------------------------------
  2006-12-21 13:03:44.334581-05
(1 row)

icondirect=> SET TimeZone = 'Europe/London';
SET
icondirect=> SELECT now();
               now
-------------------------------
  2006-12-21 18:03:52.141592+00

icondirect=> ALTER DATABASE SET TimeZone = 'America/New_York';

You can also do ALTER USER ... (both take effect when you log in).

--
   Richard Huxton
   Archonet Ltd

Re: RESTORING A DATABASE WITH DIFFERENT TIMEZONES

From
"Brandon Aiken"
Date:
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.sakai@zanthus.com.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@winemantech.com]
Enviada em: quinta-feira, 21 de dezembro de 2006 14:00
Para: rodrigo.sakai@zanthus.com.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rodrigo Sakai
Sent: Thursday, December 21, 2006 12:33 PM
To: pgsql-general@postgresql.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!