Thanks Jim - that is exactly what I need, I was not aware of that as a datatype.
On 20/09/2012, at 9:08 AM, Jim Mlodgenski <jimm...@gmail.com> wrote: > On Wed, Sep 19, 2012 at 6:27 PM, Adam Wells <a...@alandstreet.com> wrote: >> Hi All, >> >> I am in the process of writing an application that involves keeping track of >> a large number of calendar appointments - things that have start and end >> dates. It is important to make sure that appointments for a given person do >> not overlap, which will involve lots of date comparisons in db queries. > > If you can use 9.2, the new Range Type feature does what you are looking for. > http://www.postgresql.org/docs/9.2/interactive/rangetypes.html > >> >> Now I might be smoking crack, but I had the following idea, and would value >> feedback: >> >> I could represent an appointment as a line in a geometry column - the X axis >> would be time (as integer values), and the Y axis would be the ID of the >> user. >> >> user id 222 >> start time 1348095600000 (09/20/2012 09:00:00) >> end time 1348099200000 (09/20/2012 10:00:00) >> >> The WKT for the geometry would be LINESTRING(1348095600000 222, >> 1348099200000 222) >> >> If all appointments had such geometries stored, then I could use spatial >> queries to find all appointments within a given range by looking for >> ST_Overlaps etc. >> >> Is the spatial index efficient at this sort of thing? More so than doing >> date range checking in a traditional index? >> >> Is this a fundamentally lame approach, or it it worth going to the trouble >> of benchmarking? >> >> Cheers, >> >> Adam >> _______________________________________________ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
