Re: Method Question

#1

On Wed, 28 Jan 2004, Ben Burkhart wrote:

Quote:

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;

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#2

"Ben Burkhart" <poutine (AT) mudportal (DOT) com> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Method Question

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Method Question - 01-31-2004 , 09:50 AM

Re: Method Question - 01-31-2004 , 11:06 AM