Forum OpenACS Q&A: UTC EST, we all TZ for UTC

Posted by Jerry Asher on
There are these people. They live in a world that measures time according to where their primary sun is in the sky. I have a server whose job it is to notify them when it is time to head back underground for the night until they are ready for the next day's labors of restocking shelves for the nearby wal-mart.

It is very important for the war effort that the inventory be maintained!

I have associated each clan with a "time zone", a group of rules that determines the elevation of the primary sun relative to a fixed reference longitudinal zero, a "coordinated universal time" if you will. (snicker.)

Each clan also has their own end-of-day, eod, which is a local time we have agreed that they will end their day.

I would like to notify each clan, using clan instant messenger protocol (a series of sharp laser blasts from an orbiting satellite) when it is time to head back underground.

I can do this in postgresql 7.2.4, but it ain't pretty. I figured out how to increase the laser output, but I can't quite figure out how to make postgresql's date/time functions behave.

Can you help me?

create table clan (
  clan_id   integer primary key,
  eod       time default '16:00',
  tz_id     integer references timezone_rules
The following query is used within a scheduled proc that once every five minutes runs to determine which clans need reminders according to their local time.

do_foreach put_clans_down {
    select current_timestamp,
           eod from (select cl.clan_id,
                            (current_date || ' ' || eod ||
                             to_char((gmt_offset)::float / 3600, 'SG09'))::timestamp as eod
                       from golflinkster_clubs cl,
                            timezone_rules r
                      where cl.tz_id = r.tz_id
                        and current_timestamp between r.utc_start and r.utc_end) t
     where t.eod
   between (current_timestamp - '60 minute'::interval)
       and (current_timestamp + '5 minute'::interval);
} {
    aim_laser  $clan_id
What you may notice is that timezone_rules, from the ref-timezones package is a record that contains a gmt_offset as a large, positive or negative number, representing in seconds, the offset from UTC.

Where I cringe is how I avoid all those wonderful time functions and numbers and treat these things as strings, chopping them up, smacking them together and then finally interpreting them as a timestamp.

Is there a cleaner way to do this with the date/time functions provided by pg?

Posted by Randy O'Meara on

I don't know the answer...but you're always and entertaining read! Thanks for the chuckles.

Posted by Darren Ferguson on
You could try looking at the to_char functionality provided by postgresql. You can remove all that crap by doing the following: select to_char(current_timestamp,'mm-dd-yyyy hh24:mi:ss')::timestamp

This will give you it without all the UTC and offsets.

You can also format it the way you wish it does not have to be the way i have done it.


Posted by Jerry Asher on
Thanks Darren,

I believe I am doing the reverse. current_timestamp is local to where the server is based, or what the sysadmin has set the server's timestamp to be.

I have a process that performs work for the user based on the user's local time, taking daylight savings time into account.

So it appears I have to cons up a string representing the user local time and then tack on a string representing the gmt offset to convert it to a server based time.

An additional hardship is that pg doesn't implement the full set of timezones around the world, It implements some of them. Jon Griffin surged ahead and found rules for 496 different time zones some for instance with 15 minute offsets. So it seems like I can't really use much of pg 7.2.4's timezone support (which isn't much and hard to figure out anyway.)