Обсуждение: Method Question

I'm making a layout for a timecard, if a user could clock in/out just once
per day, it wouldn't be a problem, but I need to assume no limits, I
currently have it setup


CREATE TABLE timecard ('id' SERIAL,'employee' varchar(100),'time'
timestamp, inorout varchar(5));

 id | emp  |        time         | outorin
----+------+---------------------+---------
  4 | 9826 | 2004-01-27 06:08:52 | i
  5 | 9826 | 2004-01-27 06:19:54 | o
  6 | 9826 | 2004-01-27 06:20:05 | i
  7 | 9826 | 2004-01-27 08:15:13 | o


Using that as example of the data, how could I tell how many hours have
been in between each i and o? This question stumpeth me. Please help.

On Wed, 28 Jan 2004, Ben Burkhart wrote:

> I'm making a layout for a timecard, if a user could clock in/out just once
> per day, it wouldn't be a problem, but I need to assume no limits, I
> currently have it setup
>
>
> CREATE TABLE timecard ('id' SERIAL,'employee' varchar(100),'time'
> timestamp, inorout varchar(5));
>
>  id | emp  |        time         | outorin
> ----+------+---------------------+---------
>   4 | 9826 | 2004-01-27 06:08:52 | i
>   5 | 9826 | 2004-01-27 06:19:54 | o
>   6 | 9826 | 2004-01-27 06:20:05 | i
>   7 | 9826 | 2004-01-27 08:15:13 | o
>
>
> Using that as example of the data, how could I tell how many hours have
> been in between each i and o? This question stumpeth me. Please help.

Well, you'd probably be best off writing a function to go over the rows or
doing this in a front end.

However... This uses a PostgreSQL extension (DISTINCT ON) and probably
could be simpler and probably handles at least some cases incorrectly:

select distinct on (starttime, startemp) employee, starttime,
time-starttime as diff from timecard,(select id as startid, employee as
startemp,time as starttime from timecard where inorout='i') foo where
employee=startemp and time>starttime order by starttime, startemp, time;

"Ben Burkhart" <poutine@mudportal.com> writes:
> I'm making a layout for a timecard, if a user could clock in/out just once
> per day, it wouldn't be a problem, but I need to assume no limits, I
> currently have it setup

> CREATE TABLE timecard ('id' SERIAL,'employee' varchar(100),'time'
> timestamp, inorout varchar(5));

>  id | emp  |        time         | outorin
> ----+------+---------------------+---------
>   4 | 9826 | 2004-01-27 06:08:52 | i
>   5 | 9826 | 2004-01-27 06:19:54 | o
>   6 | 9826 | 2004-01-27 06:20:05 | i
>   7 | 9826 | 2004-01-27 08:15:13 | o

That's gonna be a real pain in the neck to process in SQL.  Is it too
late to reconsider your data design?  I'd suggest

CREATE TABLE timecard (
    id SERIAL NOT NULL,
    employee varchar(100) NOT NULL,
    time_in timestamp NOT NULL
    time_out timestamp );

Clocking in is implemented by inserting a row with time_in set to
current time and time_out set to NULL.  Clocking out requires updating
the existing row with the right employee ID and time_out NULL to have
non-null time_out.  Now you can easily calculate the elapsed time
represented by any one completed entry, and a simple SUM() across rows
takes care of finding total time worked.

This representation assumes that a worker can't be in two places at
once, but I trust that's okay ...

            regards, tom lane