By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

update time zone in timestamps

P: n/a
CSN
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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
CSN <co*******************@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

P: n/a
CSN

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 <tg*@sss.pgh.pa.us> wrote:
CSN <co*******************@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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.