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

Reply via email to