LiveCode Forums.

Posted: **Tue Jun 10, 2014 4:33 pm**

Currently I have a datagrid that shows me date/time/item/location. Each line item is added by a worker for inspection, is there a way to add another column that counts up the seconds/minutes/days that it has been on the list? The date and time I get using "put short date into var" and "put short time into var2" before adding the entry into MySQL database.

secondary question is I haven't figured out how to format date and time to use MySQL date and time specific columns.

Thank you,

Dave

Posted: **Tue Jun 10, 2014 4:51 pm**

DavJans wrote:Currently I have a datagrid that shows me date/time/item/location. Each line item is added by a worker for inspection, is there a way to add another column that counts up the seconds/minutes/days that it has been on the list? The date and time I get using "put short date into var" and "put short time into var2" before adding the entry into MySQL database.

secondary question is I haven't figured out how to format date and time to use MySQL date and time specific columns.

I'm not sure to understand.

1- do add date or time or timestamp values in a MySQL database... there is an easier way : ask the MySQL server to do the work.

for instance :

Code: Select all

update mytable set mydate=CURRENT_DATE where myrecord=1
insert into mytable (mydate) VALUES (CURRENT_TIMESTAMP)

2-it seems you want to know "how long" a line was created/added in your datagrid, right ? In this case, when you create the row, in one column ("start") :

Code: Select all

put the seconds

And then, when you "finish", put the seconds again
After that, you just have to subtract "start" from "finish"... you get the duration in seconds, a value you can easily convert after in minutes, hours, whatever.

3-last but not least... again you could ask MySQL do this work itself.
for instance :
(provided start_time is a timestamp column)

Code: Select all

update mytable set total_duration=CURRENT_TIMESTAMP-start_time where myrecord=1

4-to display a date DAY / MONTH / YEAR

Code: Select all

select date_format(mydate,'%d/%m/%y') from mytable where myrecord=1

Google MySQL date time... you'll find plenty of informations.

Posted: **Tue Jun 10, 2014 5:13 pm**

Thank you, very good information.

Posted: **Tue Jul 01, 2014 6:03 pm**

I've gotten this to work in a terrible way, I don't like it, I know there is a better way, but I'm not sure how do to it. I tried google'ing it but I cant seem to find it. What I'm trying to do is insert into table a bit of data not just the date / time so here is my terrible solution witch I'm sure can cause lots of problems.

Code: Select all

put "job, pcmk, bay, station" into qcFields
put "INSERT INTO  qcprioritylow (" & qcFields & ") VALUES (:1, :2, :3, :4)" into tSQL
revExecuteSQL gConnectionID, tSQL, "qcJob", "qcPcMk", "qcBay", "qcStation"
put "UPDATE qcprioritylow set adddate=CURRENT_DATE where adddate IS NULL" into tSQL
revExecuteSQL gConnectionID, tSQL
put "UPDATE qcprioritylow set addtime=CURRENT_TIME where addtime IS NULL" into tSQL
revExecuteSQL gConnectionID, tSQL

one thing I can do is

Code: Select all

put "job, pcmk, bay, station" into qcFields
put "INSERT INTO  qcprioritylow (" & qcFields & ") VALUES (:1, :2, :3, :4)" into tSQL
revExecuteSQL gConnectionID, tSQL, "qcJob", "qcPcMk", "qcBay", "qcStation"
put "SELECT id FROM table WHERE job='" & qcJob & "' and pcmk='" & qcPcMk & "' and bay='" & qcBay & "' and station='" & qcStation "'" into qcID
put "UPDATE qcprioritylow set adddate=CURRENT_DATE where id='" & qcID & "'" into tSQL
revExecuteSQL gConnectionID, tSQL
put "UPDATE qcprioritylow set addtime=CURRENT_TIME where id='" & qcID & "'" into tSQL
revExecuteSQL gConnectionID, tSQL

Posted: **Tue Jul 01, 2014 7:37 pm**

First you could do both update in one single query.

Code: Select all

put "UPDATE qcprioritylow set addtime=CURRENT_TIME,adddate=CURRENT_DATE where id='" & qcID & "'" into tSQL
revExecuteSQL gConnectionID, tSQL

You have other solutions :

- (if you use MySQL for instance) : set addtime and adddate columns with default values (current_timestamp). That way when you insert a record, those 2 columns will be automatically filled.

-or consider that you can merge addtime and adddate into one single column (with a timestamp format). After you can easily "extract" time and/or date from this single column, using function "date_format" (MySQL).
like :

Code: Select all

select date_format(addtimestamp,'%d/%m/%Y') from qcprioritylow

Posted: **Tue Jul 01, 2014 7:58 pm**

Thank you, I just figured out even a 3rd way.

Code: Select all

put "INSERT INTO  qcprioritylow (adddate, addtime, job, pcmk, bay, station) VALUES (now(), now(), " & qcJob & ", " & qcPcMk & ", " & qcBay & ", " & qcStation & " )" into tSQL

Someone tell me what the difference is because the following code seems the same to me but does not work

Code: Select all

put fld "qcJob" into qcJob
put fld "qcPcMk" into qcPcMk
put fld "qcBay" into qcBay
put fld "qcStation" into qcStation
put "now()" into qcDate
put "now()" into qcTime

put "adddate, addtime, job, pcmk, bay, station" into qcFields
put "INSERT INTO  qcprioritylow (" & qcFields & ") VALUES (:1, :2, :3, :4, :5, :6)" into tSQL
revExecuteSQL gConnectionID, tSQL, "qcDate", "qcTime", "qcJob", "qcPcMk", "qcBay", "qcStation"

or if it does work I was that close with a typo but was complaining about invalid data format

LiveCode Forums.

DataGrid/counting timer

DataGrid/counting timer

Re: DataGrid/counting timer

Re: DataGrid/counting timer

Re: DataGrid/counting timer

Re: DataGrid/counting timer

Re: DataGrid/counting timer