How about just using a trigger to check if endtime is < starttime+10 and updating if it fires
Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 11 July 2018 at 17:09, David Raymond <david.raym...@tomtom.com> wrote: > For a minimum of 10 minutes it'd be something like > > update TimeEvents > set EndTime = max( > current_timestamp, > datetime(StartTime, '+10 minutes') > ) > where > EventID = ? > and (EndTime is null or EndTime = ''); > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Stephen Chrzanowski > Sent: Wednesday, July 11, 2018 10:25 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Minimum Delta Time > > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
