Skip site navigation (1) Skip section navigation (2)

Re: Selecting time periods

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Selecting time periods
Date: 2009-07-11 12:15:47
Message-ID: 4A588273.9040506@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
Peter Jackson wrote:
>  table shift_times ( shift_id pk, shift text, start time, finish time, 
> days varchar[])
> 
>  1 A 7:00 19:00 {0,1,2}
>  2 B 19:00 7:00 {0,1,2,3}
>  3 C 7:00 19:00 {3,4,5}
>  4 D 19:00 7:00 {3,4,5,6}
> 
>  I've tried SELECT shift from shift_times where '17:00' between start 
> AND finish;
>  Which works for A and C but if I change the 17:00 to 19:30 it returns 
> no rows.


The problem with "B" and "D" is that start > end so "between start and 
finish" is never true.  You need something like:

SELECT shift from shift_times where CASE WHEN start < finish THEN 
'19:30' between start AND finish ELSE '19:30' between start and '23:59' 
or '19:30' between '00:00' and finish END;

I have an application (payroll time sheets) with a similar reporting 
requirement.  In my tables; start and end of attendance record cannot 
cross midnight; when this happens, two entries are made into tables - 
one for each day.  This simple restriction on data entry makes most of 
the reporting *much* easier.

In response to

Responses

pgsql-novice by date

Next:From: Michael WoodDate: 2009-07-11 13:07:31
Subject: Re: Selecting time periods
Previous:From: Jasen BettsDate: 2009-07-11 11:07:57
Subject: Re: cannot locate where dataase files are stored

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group