Problem to save calc result in the database (Hugues ?)

[Camille]

Hi everybody, I post a new post after extensive research done with the help of Jaanus wich allowed us to identifiy the problem but not to found the solution (http://fabrikar.com/forums/index.php?threads/how-to-calculate-a-duration.33989/page-2)

I created an element "duree" wich must calculate the difference between 2 times (element heure_debut and element heure_fin).
The result is ok in the list. For example, if the "heure_debut" is 08h10 and "heure_fin" is 09h11" the element "duree" displays 01h01. (= 01:01:00 without TIME_FORMAT).

The result must be saved into the database. But in this one, the field "duree" displays 00:00:01.
If the difference = 02:33:00 in the database the field displays 00:00:02.
As if only the hour is saved.

Who can help me, please ? I need it to make after a sum.

Thanks a lot !

 

[troester]

Can you please post the settings of your calc element (your recent code, "only calc on save"=yes/no)?
Did you change the database column type of the calc element?

 

[Camille]

Hello troester, thanks for your attention !
"Only calc on save" is No (but I tried also with Y because I tried all possibility).
I changed the database column type for the element "duree". I tried with TEXT or TIME in the db but the result is the same.

Here is the list of the elements of the form

Here is the settings of the element "duree". Time_debut and time_fin use time plugin.

Here is the result in the list

Here is the result in the database where the field "duree" have the type TEXT (but it's the same if the type is TIME)

If you want to try on my server, I can give you the code.

I 've found a page wich spoke about the element with databasejoin wich can make problem to save into the db. Then I changed it to try but it doesn't resolve the problem.

Perhaps can I test a simple calc but I don't know how. For example "4+5" and verify in the db if the result is saved but I don't know how is the formulation.

 

[troester]

It's calculation the string you are expecting (because it's correct in list view).

The values in your database table will change only if you are re-saving the record you are looking at.
So if you once had saved the record with an other calc code, this old calculated value will stay in the database until you are saving the record again with the new calc code.
Because "only calc on save"= NO the list view will calculate on the fly with the new code, showing NOT the value from the database.

The column type of the calc element in the database must be TEXT because calc is returning a string.
Try to save one record again and check the value of this record in the database.

 

[Camille]

Yes, it's good in the list view.

Yes, Jaanus said me to save again the record but no success (is it good the button submit ?)
I know that in the list view the record must be saved again to display the result. But in the database nothing changes.

When this calc will display the good result into the database, I wish to make a sum of the field in hours and minutes. Is it not better if the type of the field is TIME ?
The purpose of this form is to calculate the numbers of hours effectivly worked by each employee.

 

[Camille]

I tried with a new install of Joomla 2.5 and Fabrik 3.0.7 (from the package).
First : I must install a lot of plugin manually. All plugins as calc and time are not installed by default.
2) The type of the elements heure1 and heure2 (plugin time) are TEXT in the database. Then I changed it manually.

3) it's always the same : the result of the calc is good into the list. I'd first make an error in the calc : heure1 - heure2 then the result was negatif. Then I changed the calc, in the list the result is immediatly good but I must save the record again to display the good result in the form. Ok, I understood it's normal

But in the database, there is no change. If heure1 = 08:09:00 and heure2 = 14:17:100 the result of the calc in the list is correctly 06:08:00. But the field in the database display 00:00:06. If heure1 = 01:04:00 and heure2 = 16:16:00 the result is correctly 15:12:00 but in the database the field displays 00:00:15 (the type of the field duree is TEXT)

Are there SEC_TO_TIME and TIME_TO_SEC which cause this problem ?? Because only the hours is saved and at the place of the seconds.

 

[troester]

I just tested and can confirm your issue!

my code:
$query = "SELECT SEC_TO_TIME(TIME_TO_SEC('{search___time_bis}')-TIME_TO_SEC('{search___time_von}'))";

ony calc on save = yes + var_dump($query,$duration);exit;
is showing
string(67) "SELECT SEC_TO_TIME(TIME_TO_SEC('13,10,10')-TIME_TO_SEC('03,05,10'))" string(8) "00:00:10"

times are 13:10:10 and 03:05:10
so the delimiter inside the placeholders is changed somewhere before the calculation is done again to save into DB

only calc on save = no
is showing the expected
string(67) "SELECT SEC_TO_TIME(TIME_TO_SEC('13:10:10')-TIME_TO_SEC('03:05:10'))" string(8) "10:05:00"

time_von, time_bis are time elements, all default settings (delimiter : )

 

[Camille]

I'm sorry, I'm french and my english isn't very good. If I understand, you've the same problem ?

I tested an other thing : I deleted SEC_TO_TIME then the string is "SELECT (TIME_TO_SEC('{planning___heure2}') - TIME_TO_SEC('{planning___heure1}'))";

If heure1 = 05:05:00 and heure_2 = 10:05:00 the result in the list is 18000 (normal it's in seconds and 18000 seconds = 5 hours).
In the database where the type of the field duree is TEXT the result is ... 5 !!!
To be sure, I created a new record with heure1 = 03:00:00 and heure2= 11:00:00. The result in the list is 28800. And the result in the database is ... yeah !! 8 !

But if I get minutes in heure1 and heures2 the result in the database is only the number of the hours.

Perhaps that can give you a path ? How is it possible that the result is in seconds into the list and in hours into the database ?

 

[Jaanus]

OK, Camille, il me semble vraiment que tu n'as pas compris -
troester a justement decouvert (ou presque) en d?tails le sujet de cette probl?me. On n'a plus besoin d'exp?rimenter, c'est maintenant le tour des developeurs. Ce que nous ne savons pas encore, c'est pourquoi le calc fait comme ?a ('13,10,10') ?tant en mode ony calc on save = yes

So take it easy for now, let someone find the solution (and let's try to continue in English )

 

[ritesh123]

convert the date to Julian
Then do subtract Julian Date1 - Julian Date2 which will be number format
Save calculated data in Julian

Then convert calculated Julian back to date

 

[Jaanus]

Sorry, this is the time format without any date part

 

[Camille]

Oui, je n'avais pas tout ? fait bien compris, merci et bravo pour ton bon fran?ais Jaanus!!

 

[Jaanus]

The column type of the calc element in the database must be TEXT because calc is returning a string.

As the string that we return in this case is in exact time format 'HH:MM:SS' we can well use here also TIME field, The data that we submit to the db fields are strings anyway in the moment of submission, isn't it? Only if we use some specific datatype (as in this case) then the string should be in correct format.

BTW - for submission to TIME field is also correct string like i.e. '123'. Then the data will be 00:01:23 in db (for 12:30:00 we need '123000' etc). But it can't be used in this Camille's case as we want both correct display and saving (+calculating the sum of the same column, if possible). So we need exact 00:01:23

 

[Camille]

A little up to say you hello and "please, don't forget me" ;-)
The column type of the calc element in the database could be TEXT, the result is the same. For exemple, if the result is 04:10:00, the result is displayed correctly in the list but not in the database where it's 00:00:04. The hours are at the place of the minutes, and the minutes aren't displayed.
I saw in calc.php there is already a SEC_to_Time. Perhaps it's the problem ???

Thanks for your help

 

[Jaanus]

hmmm... don't know

 

[cheesegrits]

The SEC_TO_TIME() in the calc.php code is only used when applying a SUM column calculation to it. When doing the summing, we check to see if the field type is TIME, and if so, do SEC_TO_TIME(SUM(TIME_TO_SEC(yourfield)), so it correctly sums the seconds.

It won't have any effect except when doing column calculations.

-- hugh