Re: [sqlite] Minimum Delta Time
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 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
Re: [sqlite] Minimum Delta Time
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 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
Re: [sqlite] Minimum Delta Time
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
Re: [sqlite] Minimum Delta Time
On 11 Jul 2018, at 3:25pm, Stephen Chrzanowski wrote: > 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. To round a timestamp to ten minutes ... Get the timestamp as a string using datetime(now) --> "-MM-DD HH:MM:SS" Get the first 15 characters of it using substr(X,1,15) --> "-MM-DD HH:M" Append "0:00" using the || operator --> "-MM-DD HH:M0:00" All together now: substr(datetime(now),1,15) || "0:00" Then convert the string back into whatever form you want to store your dates in. If you do this to two times close together, you may end up with two results which are the same. In other words, your timestamp field cannot have a UNIQUE requirement. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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
