Re: Method Question

"Ben Burkhart" <poutine(at)mudportal(dot)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