Calculating the number of hours

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Calculating the number of hours

Post by trevorm » 2019-12-07 17:17

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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-07 17:46

Hi,

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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-07 18:38

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

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-07 18:38

I and not getting anything display at all at present

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-07 19:44

Hi,

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%'
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
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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 10:54

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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-08 10:59

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.
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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 12:16

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?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-08 15:51

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

Code: Select all

SELECT time_format(TIMEDIFF(timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H:%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'
and switch the from and to (as in the written code now, assuming that these are your fields).
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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 16:13

Thank you yet again.

I have tried the new code but there is no result in the Calculated field of the table now

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-08 17:49

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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 18:53

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%'

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-08 19:20

Hi,
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%'
What type of field is your calculated field? What codepage are you using?
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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 20:43

The calculated field is a Date field
I don't understand "code page"

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 20:50

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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculating the number of hours

Post by pbottcher » 2019-12-08 21:20

Sorry,

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%'
there was a missing backtick.
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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Calculating the number of hours

Post by trevorm » 2019-12-08 22:49

Hi

That's cracked it. Thank you so much for your help. See Attached image of table.

Regards
Trevor
Attachments
Timesheet table.png
Timesheet table.png (25.79 KiB) Viewed 3784 times

Post Reply