Paul; I can't use a trigger without having to create it, do the update, then destroy the trigger. There are form elements that can change these times (I submit at time start at 3pm, but I actually started at 2pm). If the time spent is less than 5 minutes, then
Advertising
David; I like that. Didn't think of max. On Wed, Jul 11, 2018 at 12:21 PM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
