Thread: Urgent PST time changing tonight

On Sun, Mar 8, 2009 at 12:58 AM, DM <dm.aeqa@gmail.com> wrote:
> Hi All,
> PST time changing tonight, I am not sure if this is going to affect the
> database.
> Please throw some light if the time change affect on our production system.
> Briefly - We have 2 production database servers with postgresql 8.3.3 and
> pgpool 2.1 version running.

pg includes its own tz data files, so running 8.3.3 might be some
issue if you handle odd timezones, but generally the dst offset change
is no big deal.  all the timestamps are stored internally in gmt.

On Sun, Mar 8, 2009 at 1:30 AM, DM <dm.aeqa@gmail.com> wrote:
> Thanks Scott for the quick response, do I need to do anything at my end to
> solve this issue.
>
> People access our servers from Pacific, Central, Mountain and East Time
> zones.
> Please let me know if i have to do any settings/configuration changes to the
> configuration files.
> thanks
> Deepak Murthy
> On Sun, Mar 8, 2009 at 12:23 AM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:

I don't think there's been any serious tz changes since 8.3.3, but it
would be a good idea to schedule a few minutes of downtime to update
to 8.3.6, the latest version of 8.3.

On Sun, Mar 8, 2009 at 1:53 AM, DM <dm.aeqa@gmail.com> wrote:
> Thanks Scott,
> If i leave the system as is without any upgrade, what issues i might see any
> idea?
> do postgres has any patches for the changes. i dont want to install a new
> databae at this time, i have 1 hr to go..
> Thanks

Assuming that some update to DST got missed, then you're output will
all be off by an hour from what they should be when retrieved for that
timezone that has it wrong.  I remember there being something a while
back about moving one of the timezones a few days.  You can always
create and retrieve timestamps ahead of time (i.e. a timestamp in the
future) and see what offset it has to see if it's coming out right.

The real problem gets created when timestamps are inserted now for the
future.  If there's a rule that we fall back 1 week off from what your
TZ data says, and you insert it today, with the old TZ on your
machine, it will be inserted as the wrong time, with the wrong offset
to get it to GMT.  This cropped up a year ago with some change our
windows machines at work didn't have, and all the schedules created by
those machines were off by an hour until the newly created events
(created with the right timezone offset values) started showing up and
we got past the window where time actually did match up again.

So, leaving these to stew can be a real problem, if you're not keeping
up with the tz info.

DM wrote:
> Hi All,
>
> PST time changing tonight, I am not sure if this is going to affect
> the database....
IF you are up-to-date on your TZ files the database itself should be
fine. But external processing is another matter - especially if you have
processes that assume conventions from previous PG versions. For example:

PG 7.4 (one day = 24 hours):
select now();

              now
-------------------------------
 2009-03-09 12:05:42.533139-07

 select now()-'2 days'::interval;
           ?column?
-------------------------------
 2009-03-07 11:05:43.963526-08


But in 8.3 (day interval is DST aware):
select now();

              now
-------------------------------
 2009-03-09 12:04:21.813654-07

select now()-'2 days'::interval;
           ?column?
-------------------------------
 2009-03-07 12:04:47.693384-08

That's just in the database itself and without cross-country processing
(i.e.: if you have somewhat synchronized processes that assume that New
York is 3 hours ahead of San Francisco you can have problems if you
don't consider that New York changed to DST 3 hours ahead of San Francisco).

Then there are the scripts that calculate dates externally for feeding
into your database-backed processes (for fun, try "-d yesterday" and "-d
tomorrow" in most versions of the "date" command in the vicinity of DST
changes).

Cheers,
Steve

On Mon, Mar 9, 2009 at 1:14 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> Then there are the scripts that calculate dates externally for feeding into
> your database-backed processes (for fun, try "-d yesterday" and "-d
> tomorrow" in most versions of the "date" command in the vicinity of DST
> changes).

I ran into an issue with this a while back.  We were running fairly
modern distros at work, but older ones in production, and a script I'd
written and tested on my workstation failed miserably if you ran it
between midnight and 2/3am the sunday of the time shift on the
production machines.

On Mon, Mar 9, 2009 at 1:14 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> IF you are up-to-date on your TZ files the database itself should be fine.
> But external processing is another matter - especially if you have processes
> that assume conventions from previous PG versions. For example:

Note that pgsql uses its own internal TZ data.  So your OS being up to
date means little if your database is not.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> Note that pgsql uses its own internal TZ data.  So your OS being up to
> date means little if your database is not.

Well, it depends.  I think practically all distro-supplied versions
are using --with-system-tzdata (or equivalent patches in older PG
releases) so that they don't need to turn PG when updating the TZ
database.  You really only want to use the built-in tzdata if you're
on a system that doesn't receive software updates in some fairly
automated manner.

But the whole discussion only applies to people who live in places
where the DST laws change without reasonable advance notice.

            regards, tom lane