Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA94119 for ; Thu, 19 Aug 1999 15:19:08 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id PAA08237; Thu, 19 Aug 1999 15:17:45 -0400 (EDT) To: Nathan Gelbard cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] searching time arrays In-reply-to: Your message of Thu, 19 Aug 1999 11:37:37 -0700 (PDT) Date: Thu, 19 Aug 1999 15:17:45 -0400 Message-ID: <8235.935090265@sss.pgh.pa.us> From: Tom Lane Nathan Gelbard writes: > given the table below, how would i determine which name is > avail on a givin day at a givin time? the time arrays > contain an a list of hours a support person is available; > '{09:00:00,10:00:00,11:00:00,12:00:00}' etc. > any thoughts? > CREATE TABLE support_sched > ( > name varchar(30), > monday time[], > tuesday time[], > wednesday time[], > thursday time[], > friday time[], > saturday time[], > sunday time[] > ); I wouldn't design the table that way --- you've made it really hard to answer that sort of query, rather than exploiting SQL's strengths. Instead, consider a table that has one record per availability-window, say name text, dayofweek int2, -- 1-7 = Sun-Sat, or some such encoding starttime time, stoptime time and you just make as many of these as there are contiguous time- windows in each support person's schedule. There might be twenty or so records per support person, but so what. Now your query looks like SELECT name FROM support_sched WHERE dayofweek = appropriatevalue AND now >= starttime AND now <= stoptime; (you could also reduce the last two clauses to a BETWEEN if you happen to like syntactic sugar). If you're concerned about storing twenty or so copies of a person's name, make that column be an integer ID code instead that you can look up in a separate table of support people. I dunno if I'd bother with that if the data is *only* a person's name, but as soon as you start adding phone number, home address, mother's maiden name, yadda yadda you will want the separate table... regards, tom lane