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