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

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

Reply via email to