Forum OpenACS Q&A: PostgreSQL: time zones and daylight savings

With
bcuni=# select substr(now() at time zone interval '-05:00',1,19);
in time zone EST I get
       substr        
---------------------
 2002-08-30 22:27:24
(1 row)
and not:
bcuni=# q
[simon@rex ~]$ date
Fri Aug 30 22:06:29 EDT 2002
(i.e. due to daylight savings I am in EDT and not EST.)

Okay. Yeah. This is the code:

select substr(now() at time zone interval '$timezone',1,19);
where
$timezone
is selected from table
timezones
holding all the users' GMT settings from -12:00 up to +12:00. But the system has no way of changing to daylight savings, or has it?

How do I implement the system to automatically change to daylight savings?

Thanks,

Collapse
2: Seems to work here... (response to 1)
Posted by Andrei Popov on
Pardon my ignorance, I guess I don't follow you, really, but here I get:
andrei:~> date
Sat Aug 31 15:46:09 CEST 2002
and
andrei=# select substr(now() at time zone interval '-05:00',1,19);
       substr        
---------------------
 2002-08-31 08:46:12
(1 row)
Is that's what you're looking for? This is on FreeBSD 4.6-STABLE with PostgreSQL 7.2.3.
Let me clarify with an example.

A user from Florida visits my web service and sets her time zone to be "-05:00". The system works fine until it reaches the date of Daylight Saving Times and suddenly the system serves the time one hour behind.

Unfortunately it isn't as easy as letting a proc substract one hour during Daylight Saving Times since certain places (for example one U.S. state, don't remember which one) don't make use of DST.

Perhaps I should store two times and add an extra column to my data model:

create table timezones (
timezone_id
timezone -- i.e. "-05:00"
timezone_dst -- "-04:00"
description "EST"
);
And then let the script do the SQL query using either timezone or timezone_dst according to the date. For places with no DST the 2 times would just be the same. I guess what I'm really asking is, "Is there way to get around this using an SQL query, one that will return the time correctly even during DST?"
Collapse
Posted by Jon Griffin on
Why aren't you using ref-timezones? This is already in there.
Collapse
Posted by Janine Ohmer on
Becuase he's working with 3.2.5.

I figured ref-timezones must handle this and I looked at it briefly for something Simon could pull out and use in his code, but nothing jumped out at me.  Can you point us at the right proc(s)?

Collapse
Posted by Jon Griffin on
ref-timezones doesn't have any procs. It is just the data, but timezone_rules has the dates and offsets for all the known timezones.

You could load ref-timezones (or trim it down if you only need some of it) and easily calculate what offset is in effect from UTC/GMT. Then the user can just be presented with an option to their timezone, and never worry about offsets as the query will take care of it.

I hope I didn't misunderstand the problem. Also, there may be some procs in acs-datetime that can be used, I didn't write that package and haven't really looked at it.

Collapse
Posted by Janine Ohmer on
Simon, I hope you don't mind my jumping in here but I'm curious about this too...

The problem is this:  his user says she wants a reminder e-mailed to her at 3:00 pm.  She's currently in EST, so that's the timezone she chooses, and the system can calculate the time to send it to her with "GMT - 5".

The problem comes in when the user goes on Daylight Savings Time; the calculation has to be "GMT - 4" instead (for my example).  His code needs some way of knowing which offset to use at a particular moment in time.

I'm sure there are rules about when DST starts and stops, but we were hoping there would be something already written to handle this.

Collapse
Posted by Jon Griffin on
BTW, I forgot that there are some plsql procs, which allow conversion to utc and get offset.

If she chooses EST5EDT or US/Eastern then all the calculations can be made.

It is linked by tz_id of 509 into timezone_rules.
Daylight time goes from April 7 at 7AM (GMT) until Oct 27 5:59:59 GMT.
Therefore you can calculate from there. Note that local times are in the table also.

Timezones are not an easy issue. I am on my way to a picnic so I can't write much more but if you still have a problem I can write up a little doc tomorrow.

Collapse
Posted by Jon Griffin on
Forgot to mention, All my servers are set to UTC (like all good *NIX servers should be). This is why I use the UTC and the proc exists.
Note that DST starts and stops on different days in different regions --should this be scoped for international localizations.  I wonder if there is some general mapping or function somewhere that handles the conversion based on region and UTC?
Collapse
Posted by Jon Griffin on
Not everyone uses DST. In fact much of the non-western world doesn't along with Arizona, and some other states.

You need to map a time zone with the time_zonerules. You are trying to make this much harder than it is. Everything you need for timezones is there. It is already internationalized (other than language which may be difficult as there is really no one source for the information.).

But how do I do it on an OpenACS 3.2.5 installation?

I'm not sure I'm getting this. Should I just use the 4.5 data/code? How hard is that to implement on OpenACS 3.2.5?

Collapse
Posted by Jon Griffin on
You will have to use the 4.5 stuff. It is easy as it is all SQL and some psql procs. Just download the package and run the scripts. I don't think anything is PG >7.1 specific.
Collapse
Posted by Andrei Popov on
This reminds me of a cron discussion on freebsd-{current|stable} list about half a year ago -- what/when should cron do if a job is scheduled in the timespan during the DST change?  One of recommendations (and indeed the simplest of workarounds) was not to schedule any jobs at such times :)