I've got an application that I've written that keeps track of time spent on
particular tasks. I do many things throughout the day, going between
different tasks, and tasks I've already worked on for today, so this tool
is incredibly helpful to help justify my warming a chair.
I'd prefer the SQL code to handle this particular procedure I'm about to
describe. Obviously, in the application, I can make an additional trip to
the database to yank out the start time, add the 10 minutes, then do the
update, but I'd rather the database deal with this particular business rule
since the handling of the actual values is done at the database level.
At the bottom of this email is the Delphi code and the table schema I'm
interested in.
What I have is a button on the UI that toggles start/stop work times on a
particular task. The Delphi Code below shows the toggling methodology. My
interest is modifying the Stop part so that at a minimum, there is a 10
minute delta between the start and end time. So if I start a timer at
11:00, then stop at 11:01, I want the database to update the end time to
11:10.
I suspect a SQLite CASE statement may be of help, but I'm not sure how to
check for the 10 minute delta then update the row with the altered time or
the real time.
*Delphi Code:*
tbl:=db.GetTable('select EndTime from TimeEvents where EventID=? order by
StartTime desc',[EventID]);
// If this task doesn't have a previous timer, or, this task has no
currently running timers, make a new timer
// otherwise, stop the currently running timer
if (tbl.RowCount=0)or(tbl.FieldByName['EndTime']<>'') then begin
db.ExecSQL('insert into TimeEvents (EventID) values (?)',[EventID]);
end else begin
db.ExecSQL('update TimeEvents set EndTime=current_timestamp where
EventID=? and (EndTime is null or EndTime="")',[EventID]);
end;
*Table Schema*
CREATE TABLE [TimeEvents](
[EventNumber] INTEGER PRIMARY KEY AUTOINCREMENT,
[EventID] integer NOT NULL REFERENCES [tEvents]([EventID]) ON DELETE
CASCADE,
[StartTime] DATETIME(10) NOT NULL DEFAULT CURRENT_TIMESTAMP,
[EndTime] DATETIME);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
