Thread: 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!
'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!
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!
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
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
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!