Thread: update time zone in timestamps
Is it possible to update the timezone part of timestamp fields in a single query? I have a bunch of values that are -06 I need changed to -07. BTW, better to use 'timestamp without time zone' or 'timestamp with time zone'? __________________________________ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/
CSN <cool_screen_name90001@yahoo.com> writes:
> Is it possible to update the timezone part of
> timestamp fields in a single query? I have a bunch of
> values that are -06 I need changed to -07.
I suspect that you have a fundamental conceptual error.
You cannot "update the timezone" because the timezone is not part of the
stored value; it is part of the display operation. Stored values for
timestamptz columns are always effectively in UTC. When the value is
converted to a string for display, it is adjusted to your current local
timezone (per SET TIME ZONE) and that timezone is what's put on the
output.
So the basic answer is you don't change the data, you change your
TIME ZONE setting from -6 to -7 if that's what you want to see.
You might have an additional problem that the data was entered
incorrectly, and is one hour off from reality because you were
confused about time zones when you put it in. In that case you'd
fix it with something like
UPDATE tab SET col = col + '1 hour'::interval;
regards, tom lane
Does Postgres use the system's timezone (/etc/localzone) at all? Or does "SET TIME ZONE 'MST'" need to be placed in postgres.conf to make it always used (I set it via psql, but it appears to only be set for that connection)? Thanks, CSN --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > CSN <cool_screen_name90001@yahoo.com> writes: > > Is it possible to update the timezone part of > > timestamp fields in a single query? I have a bunch > of > > values that are -06 I need changed to -07. > > I suspect that you have a fundamental conceptual > error. > > You cannot "update the timezone" because the > timezone is not part of the > stored value; it is part of the display operation. > Stored values for > timestamptz columns are always effectively in UTC. > When the value is > converted to a string for display, it is adjusted to > your current local > timezone (per SET TIME ZONE) and that timezone is > what's put on the > output. > > So the basic answer is you don't change the data, > you change your > TIME ZONE setting from -6 to -7 if that's what you > want to see. > > You might have an additional problem that the data > was entered > incorrectly, and is one hour off from reality > because you were > confused about time zones when you put it in. In > that case you'd > fix it with something like > UPDATE tab SET col = col + '1 hour'::interval; > > regards, tom lane __________________________________ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/
SHOW ALL shows TimeZone is 'unknown'. postgres.conf says 'TZ' is used for default. Where should TZ be set (it doesn't appear to be set on my RH9 system)? Thanks, CSN --- CSN <cool_screen_name90001@yahoo.com> wrote: > > Does Postgres use the system's timezone > (/etc/localzone) at all? Or does "SET TIME ZONE > 'MST'" > need to be placed in postgres.conf to make it always > used (I set it via psql, but it appears to only be > set > for that connection)? > > Thanks, > CSN > > > --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > > CSN <cool_screen_name90001@yahoo.com> writes: > > > Is it possible to update the timezone part of > > > timestamp fields in a single query? I have a > bunch > > of > > > values that are -06 I need changed to -07. > > > > I suspect that you have a fundamental conceptual > > error. > > > > You cannot "update the timezone" because the > > timezone is not part of the > > stored value; it is part of the display operation. > > > Stored values for > > timestamptz columns are always effectively in UTC. > > > When the value is > > converted to a string for display, it is adjusted > to > > your current local > > timezone (per SET TIME ZONE) and that timezone is > > what's put on the > > output. > > > > So the basic answer is you don't change the data, > > you change your > > TIME ZONE setting from -6 to -7 if that's what you > > want to see. > > > > You might have an additional problem that the data > > was entered > > incorrectly, and is one hour off from reality > > because you were > > confused about time zones when you put it in. In > > that case you'd > > fix it with something like > > UPDATE tab SET col = col + '1 hour'::interval; > > > > regards, tom lane > > > __________________________________ > Do you Yahoo!? > Free Pop-Up Blocker - Get it now > http://companion.yahoo.com/ > __________________________________ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/
CSN wrote: > > SHOW ALL shows TimeZone is 'unknown'. postgres.conf > says 'TZ' is used for default. Where should TZ be set > (it doesn't appear to be set on my RH9 system)? The postmaster process should have TZ in its environment. I think that gives clients a default timezone. You can also use PGTZ on the client to control it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073