From: | CSN <cool_screen_name90001(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: update time zone in timestamps |
Date: | 2003-12-05 23:40:59 |
Message-ID: | 20031205234059.6865.qmail@web40613.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > CSN <cool_screen_name90001(at)yahoo(dot)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/
From | Date | Subject | |
---|---|---|---|
Next Message | pg | 2003-12-06 01:45:10 | Re: last update time of a table |
Previous Message | CSN | 2003-12-05 23:36:20 | Re: update time zone in timestamps |