Calculating the number of hours
Calculating the number of hours
I would like to know the suggested SQL Query to go in the "Query" window for a calculated field, where I have a "start time" field, "finish time" field and a "total hours" field. All fields are in the same table called "timesheet". I had a look on Google and found the following but it does not work.
TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)
As you will note I do not know anything about SQL Query Language. If anyone can help me that would be good. Thanks.
TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)
As you will note I do not know anything about SQL Query Language. If anyone can help me that would be good. Thanks.
Re: Calculating the number of hours
Hi,
can you post your complete query, and what are you getting (and expecting) as result?
can you post your complete query, and what are you getting (and expecting) as result?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
This is the total query I have typed in.
I would like to see the total hours and minutes between the start time and finish time placed in the calculated field
I would like to see the total hours and minutes between the start time and finish time placed in the calculated field
Re: Calculating the number of hours
I and not getting anything display at all at present
Re: Calculating the number of hours
Hi,
you need to put in a sql query.
So try
assuming that day_1_from is your start time and day_1_to is you finish time.
Also assuming that your primary key of timesheet is called id
you need to put in a sql query.
So try
Code: Select all
SELECT TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
Also assuming that your primary key of timesheet is called id
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
Thank you - It works.
However, I am getting a negative result (e.g. -04.00.00) in the calculated field in the table view.
The calculated result is not showing at data entry time in the detail data input view. It does show though on the detail view when looking at an existing record. i.e. the calculated field is not calculating at data entry time it is calculating on saving the data to the table. Is there a way to make the calc perform in the data entry view so the user knows the hours that are calculated? Thank you again, I much appreciate your help here.
However, I am getting a negative result (e.g. -04.00.00) in the calculated field in the table view.
The calculated result is not showing at data entry time in the detail data input view. It does show though on the detail view when looking at an existing record. i.e. the calculated field is not calculating at data entry time it is calculating on saving the data to the table. Is there a way to make the calc perform in the data entry view so the user knows the hours that are calculated? Thank you again, I much appreciate your help here.
Re: Calculating the number of hours
Hi,
glad it works.
can you show a sample of that data you are using for the calculation. Also the default behaviour for the calculated fields is as you described. Calculation happens after saving the record. If you want to do some calculation at data entry you can do that via Javascript.
register on change for your date fields and if both are filled you calculate the diff and display it on the calculated field.
glad it works.
can you show a sample of that data you are using for the calculation. Also the default behaviour for the calculated fields is as you described. Calculation happens after saving the record. If you want to do some calculation at data entry you can do that via Javascript.
register on change for your date fields and if both are filled you calculate the diff and display it on the calculated field.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
I can see at 66 Years old I'm gong to have to register for coding classes as the local college. I'm trying to help my daughter get some admin functionality on her business web site. I have no understanding of Java script. This is why I thought Appgini would be useful in helping me to do that. Even with Appgini though there is quite a lot of coding you have sort for yourself.
Anyway that aside, If I understand you correctly the data input to the data input fields looks like e.g. "12:30" start time and "15:30" end time - the calculated field now displays -03.00.00.0000. I'm only interested in the hours and minutes worked by the subcontractor teachers so we have a quick record to check the subcontractor teachers invoices. Have I given you the details you required?
Anyway that aside, If I understand you correctly the data input to the data input fields looks like e.g. "12:30" start time and "15:30" end time - the calculated field now displays -03.00.00.0000. I'm only interested in the hours and minutes worked by the subcontractor teachers so we have a quick record to check the subcontractor teachers invoices. Have I given you the details you required?
Re: Calculating the number of hours
Hi,
no worries, AppGini gives you a lot out of the box, and a lot of possbilities, but if you want to do more than what is provided out of the box, you need to get onto coding a little bit.
for your question.
Try
and switch the from and to (as in the written code now, assuming that these are your fields).
no worries, AppGini gives you a lot out of the box, and a lot of possbilities, but if you want to do more than what is provided out of the box, you need to get onto coding a little bit.
for your question.
Try
Code: Select all
SELECT time_format(TIMEDIFF(timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H:%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
Thank you yet again.
I have tried the new code but there is no result in the Calculated field of the table now
I have tried the new code but there is no result in the Calculated field of the table now
Re: Calculating the number of hours
Can you post your "old" code that gives results and the new one you that you put in.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
Hi -
I'm feeling guilty now - you've my been on my query a while.
New Code you gave me:-
SELECT time_format(TIMEDIFF(timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H:%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
First code you gave me:-
SELECT TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
I'm feeling guilty now - you've my been on my query a while.
New Code you gave me:-
SELECT time_format(TIMEDIFF(timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H:%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
First code you gave me:-
SELECT TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
Re: Calculating the number of hours
Hi,
can you try:
What type of field is your calculated field? What codepage are you using?
can you try:
Code: Select all
SELECT time_format(TIMEDIFF(timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H.%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
The calculated field is a Date field
I don't understand "code page"
I don't understand "code page"
Re: Calculating the number of hours
I have tried changing the field type to integer and text - the new code did not show a result in either and does not in Date type field
either.
either.
Re: Calculating the number of hours
Sorry,
can you try
there was a missing backtick.
can you try
Code: Select all
SELECT time_format(TIMEDIFF(`timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H.%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Calculating the number of hours
Hi
That's cracked it. Thank you so much for your help. See Attached image of table.
Regards
Trevor
That's cracked it. Thank you so much for your help. See Attached image of table.
Regards
Trevor
- Attachments
-
- Timesheet table.png (25.79 KiB) Viewed 3784 times