blue_cowdawg has asked for the wisdom of the Perl Monks concerning the following question:

The problem

OK... I'm feeling rather foolish right now, but I can't seem to figure out a good way to solve this seemingly simple problem.

I have an application that is looking up values from a time schedule. Depending on when the lookup is run it is possible to wrap around from say 23:00 past midnight in the list of desired values. I want to keep the rows sorted by relative time with the wrapped around (does that term make sense?) rows kept as being chronologically after the pre midnight rows.

I've come up with a brute force method, anymonk out there have an elegant way of doing this?

The data is stored in a PostgreSQL database with a schema that looks sorta like this (confidential info has been obfuscated). 

create sequence event_id_seq;
create table schedule (
event_id     integer not null 
      default nextval('event_id_seq'),
event_start  time not null,
event_end    time not null,
event_descriptor varchar(50) not null,
primary key (event_id)
);

[download]

The application in one mode looks for events that happen within a window starting at an arbitrary time plus 3 hours and in the other mode looks for the next "n" events from an arbitrary time.

These are daily scheduled events where the schedule might change every 3 months or so. The end application schedules other events to happen in lead of the events retrieved. IE if an event as happening at 23:10 the other event has to happen at 22:50 (30 minutes prior) and is used to schedule activities of some employees.

Anybody have ideas?

Peter L. Berghold -- Unix Professional
Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

Replies are listed 'Best First'.
Re: Midnight wraparound.
by Corion (Patriarch) on Aug 12, 2005 at 20:15 UTC

    I didn't think this through totally, but if you encode all your start/end times as minutes of the day, the following SQL statement should give you all events within a 3 hour window:

    select event_id 
from schedule 
where 
   (event_start between $current_time and ($current_time+3 *60)) 
or (event_start between 0 and ($current_time + 3*60)-24*60)

    [download]

    I'm not sure an all-SQL solution is what you want, but the translation into a grep expression isn't too hard either :-)

[reply]
[d/l]
Re: Midnight wraparound.
by Transient (Hermit) on Aug 12, 2005 at 20:56 UTC
    Given that I don't have PostGres, I tried it in Oracle. ... I hate Oracle's timestamp/datestamp stuff (probably because I haven't used it enough) - but here goes: 
    select id, event_time
from   table
order by to_char(sysdate+(to_timestamp(to_char(event_time,'MM/DD/YYYY'
+)||' '||to_char(systimestamp,'HH24:MI'), 'MM/DD/YYYY HH24:MI')
+1-event_time),'HH24:MI') desc;

    [download]
    Basically if you get through all the conversion crap, it should look like this: 
    select id, event_time
from   table
order by to_char(systimestamp+1-event_time,'HH24:MI') desc;

    [download]
    Which is basically saying, add a day to wherever you are now. Then subtract the time only portion (event_time) from that, and sort in descending order (largest to smallest). The idea is that anything that is the farthest away from tomorrow at this time, will be the closest thing to today at this time, because the window is 24 hours and your event_time only has a range of 24 hours.

    (e.g. if it's 5PM right now and you have a job that runs at 4PM and one at 6PM and one at 1AM... tomorrow at 5PM it will be 14 hours difference between 1 AM, 23 hours for the 6PM and 1 hour for the 4PM - so , the order is 23, 14, 1 or 5PM, 1AM, 4PM - Capice? =)
[reply]
[d/l]
[select]
Re: Midnight wraparound.
by kwaping (Priest) on Aug 12, 2005 at 21:01 UTC
    I'm thinking Date::Calc's Delta_YMDHMS or Add_Delta_YMDHMS methods might be of use here.
[reply]
[d/l]
[select]
Re: Midnight wraparound.
by Anonymous Monk on Aug 12, 2005 at 20:41 UTC

    How about keeping track of seconds from the Epoch, and using Time::HiRes to convert into human-readable forms?

[reply]
[d/l]
Re: Midnight wraparound.
by JamesNC (Chaplain) on Aug 13, 2005 at 13:29 UTC
    Got indexes?
    A clustered index on the columns you specify will keep them in sorted order by whatever columns you specify and in what order you specify them.
    create clustered index end_start_idx on schedule ( end_time, start_tim
+e, event_id )

    [download]

    Make creating indexes part of every create table you do and you will be happy you did.
    JamesNC
[reply]
[d/l]
Re: Midnight wraparound.
by Anonymous Monk on Aug 14, 2005 at 04:02 UTC
    DateTime is made for exactly this sort of thing. For instance, if you want to filter a list of events to find just the ones that occur in the next three hours from now, it goes something like this... 
      my $starttime = DateTime->now();
  my $endtime = DateTime->now()->add( hours => 3);
  my @between = grep {
     DateTime->compare($_->when(), $starttime) >= 0
   and
     DateTime->compare($endtime, $_->when) >= 0
  } @eventtimes;

    [download]

    Well, something like that. The ->when() method may not be how you get a DateTime object for one of your events, but you can adjust that part to match your setup. Also, the >=0 condition may not be right, if you want open endpoints for instance. (The return value of compare is the same as that of the cmp and <=> operators would be if the comparison were string or numeric, respectively.)

[reply]
[d/l]

Back to Seekers of Perl Wisdom