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