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

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,
           clan_id,
           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
    fire_laser
}

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?

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.

HTH
Darren