pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
searching time arrays
2+ messages / 2 participants
[nested] [flat]

* searching time arrays
@ 1999-08-19 18:37  Nathan Gelbard <gelbardn@intus.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Nathan Gelbard @ 1999-08-19 18:37 UTC (permalink / raw)
  To: pgsql-sql


Greetings SQLians;

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?

thanx
	natez



CREATE TABLE support_sched
(
		name varchar(30),
		monday time[],
		tuesday time[],
		wednesday time[],
		thursday time[],
		friday time[],
		saturday time[],
		sunday time[]

);



Nate Gelbard		      	       office: 503.284.0369
Straylight Research		       fax   : 516.828.8533




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] searching time arrays
@ 1999-08-19 19:17  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nathan Gelbard <gelbardn@intus.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 1999-08-19 19:17 UTC (permalink / raw)
  To: Nathan Gelbard <gelbardn@intus.net>; +Cc: pgsql-sql

Nathan Gelbard <gelbardn@intus.net> 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-08-19 19:17 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-19 18:37 searching time arrays Nathan Gelbard <gelbardn@intus.net>
1999-08-19 19:17 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox