How to calculate a duration ?

[Camille]

I'm sorry Jaanus. Read fully is one thing ... all understand is an other ;-) I didn't understand where I should put the code that you gave me. Yes, I executed it with PHP MyAdmin and now the field "duree" contains the good time. But if I add a new data into the list, it's always the same : in the list duree is ok, but in the database, the new entry is 00:00:06 (!). Then, I made again the UPDATE in the database and it corrected.
But it would be automatic .. Is it possible to add this code to the one you gave me for the calculation ?

For the custom query, it's a shame it doesn't seem to have more documentation about it. %s replace the nomtable.nomchamp ?

Thanks for your help and your patience !!!

 

[Camille]

I tried this with PHPMyAdmin and it works :

UPDATE planning SET planning.duree = (SELECT SEC_TO_TIME(TIME_TO_SEC(planning.heure_fin) - TIME_TO_SEC(planning.heure_debut)))

Then I tried in the calc options in Fabrik :

$db = JFactory::getDbo();
$query = "UPDATE planning SET planning___duree = (SELECT SEC_TO_TIME(TIME_TO_SEC(planning___heure_fin) - TIME_TO_SEC(planning___heure_debut))) ";
$db->setQuery($query);
$duration = $db->loadResult();
return $duration;

No error but no result ...
What is wrong ?

Thank for your help !

 

[Jaanus]

You don't have such field in database table named as planning___duree - this is only an element full name that contains also a table name. And that's not all.
If you are talking about the same calc element duree just use there the code that I originally suggested. The returned value will be saved in your db table when you add new or edit existing record.

But I have no ideas how to calculate the sum of durations. Would be useful to know for me as well

 

[Jaanus]

Sorry, I fully forgot this your post:

I tryed this and it displays only hours and minutes in the list :

$db = JFactory::getDbo();
$query = "SELECT TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC('{planning___heure_fin}') - TIME_TO_SEC('{planning___heure_debut}')), '%Hh:%im')";
$db->setQuery($query);
$duration = $db->loadResult();
return $duration;

But in the database the field "duree" contains 00:00:00.
I think it's a problem to do the sum of this, isn't it ?

Well, you are before a choice:
1) keep the "beautiful formatting" that means use this code, change db field type from TIME to TEXT or VARCHAR and forget forever the calculation of the sum of duration

2) try to find good method to calculate duration sum, set db field type to TIME and forget forever any "beautiful formatting"

3) create another calc field so that you have 2 calc fields:
one uses code

$db = JFactory::getDbo();
$query = "SELECT SEC_TO_TIME(TIME_TO_SEC('{planning___heure_fin}') - TIME_TO_SEC('{planning___heure_debut}'))";
$db->setQuery($query);
$duration = $db->loadResult();
return $duration;

and will be good for calculation of the sum of duration if you will find the right method. db field type - set it to TIME

Another calc field uses this code

$db = JFactory::getDbo();
$query = "SELECT TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC('{planning___heure_fin}') - TIME_TO_SEC('{planning___heure_debut}')), '%Hh:%im')";
$db->setQuery($query);
$duration = $db->loadResult();
return $duration;

and the db field behind it is TEXT (or VARCHAR). The data will be saved exactly in format 14h:45m.

 

[Camille]

You're really nice ! But not Fabrik ;-)
Because the field duree into the database didn't display the good time while it was good into the list in Fabrik, I deleted the element duree into the component to rebuild it.

Here's the code

Here's the list. It's ok, the element duree displays the result of the calc.

Here's the MySQL table (duree with the type TEXT). The field duree displays not the result ..)

I deleted again the element duree, cleaned the trash and accepted to delete the field into the daabase (because I didn't see before if only the element is get into the trash, it's always into the db and I understood not why the field is always there ...).
I rebuild the element with this code above , type of field in the db is TEXT ... and the result in the db is NULL (while it's ok into the list in Fabrik)

If the type of thefield is TIME, it displays 00:00:00
If I make an UPDATE with PHPMyAdmin, it's ok the field displays the same things that into the list

The first problem is the fact that without an UPDATE into PHPMyAdmin the field "duree" into the db is empty. Regardless the type of the field (TEXT or TIME). As if the calc result is only posted into the list but not injected into the db.

Beautiful formating and duration sum are the following problems but I wish first to understand why the db field is always empty ...

I tried with an INSERT but I don't know if the syntax is good .. because the new data in the field "duree" is 00:00:00 again in the db. And in the list, the column is completly empty while the first record was updated in the db

$db = JFactory::getDbo();
$query = "SELECT TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC('{planning___heure_fin}') - TIME_TO_SEC('{planning___heure_debut}')), '%Hh:%im')";
$db->setQuery($query);
$duration = $db->loadResult();
$sql = "INSERT INTO `planning` (`Duree`) VALUES  ('$duration1');
 
return $duration;

something is missing in the code for the data to be stored in the database but I can not find what ...

 

[Jaanus]

No insert into in calc field! The first code is right. The data doesn't appear in db without saving the form.

What happens, if you
1) change field in db (phpmyadmin) to TIME, then
2) open both records in form view and resave them?

 

[Camille]

The field is in TIME type. I opened again the records and try with the submit button. The "duree" is ok in the form view. But nothing in the db : the field "duree" contains always 00:00:00.

I searched into http://69.167.181.22/wiki/index.php/Common_Joomla_PHP_tasks
Should I add $db=&JFactory::getDBO();

 

[Jaanus]

pfff.... this could be some kind of calc issue, but...

Just in case look what will happen if you add _raw to element placeholders
par exemple {planning___heure_fin_raw}

Should I add $db=&JFactory::getDBO();
You already have one written in code. And you don't need & - you referred to Fabrik2 common php tasks, here we talk about Fabrik 3

 

[Camille]

I don't understand, I answered 2 times but my message don't appear. I try again !

I made what you say but the result is the same : the good data in the columl "duree" in the list. But 00:00:00 in the db field "Duree" (with TIME type)

$db = JFactory::getDbo();
$query = "SELECT TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC('{planning___heure_fin_raw}') - TIME_TO_SEC('{planning___heure_debut_raw}')), '%Hh:%im')";
$db->setQuery($query);
$duration = $db->loadResult();
return $duration;

I tried with "Calc only on save" on Yes. The result in the list is 00:00:00 like in the db. I don't understand why because I saved the form with the SUBMIT button. I opened again the record and save it again. Nothing change. Is it a problem with this button ? Or is there an other button to save ?


Do you already used this plugin calc ?

I wish to become creative with Fabrik ... but it'snt easy !!

 

[Jaanus]

Do you use EXACTLY this code as in your latest post - with TIME_FORMAT?
If yes, then please remove it - that means remove
TIME_FORMAT( from beginning
, '%Hh:%im') from the end
Then try again with saving data.

 

[Camille]

I tried with exactly this code and without TIME_FORMAT. It was exactly the same.

AAAhhh ! I see just anything : in fact the hour is saved ! I didn't see it before because I always tested with only a difference between the minutes (for example : heure_debut = 8h10m , heure_fin = 8h35m).
But if I get a difference between the hours like heure_debut = 08h04 and heure_fin = 09h04, the list in Fabrik displays 01:00:00 (or 01h00m if the code TIME_FORMAT is used) but in the bd table it's 0:00:01 (field is TIME or TEXT, it's the same).
With heure_debut = 08h09 and heure_fin = 14h49, the list displays 06:40:00 (the good result) and the bd field displays 00:00:06.
I made several trys and it's always the same : only the hour is saved and at the place of the seconds (I suppose the format time is HH:MM:SS ?)

I also saw in forum that databasejoin plugin can cause problem, then I deleted the elements employee and tache_employee but it was the same.

 

[Jaanus]

Strange bug, nothing else to say. Can anyone help?
It seems like the calc element when being in list view parses everything normally but when saving and the value is occasionally a time string HH:MM:SS then it's stripping away all : and zeros. So if it is expected to return time and the duration (end time - start time) is 6 hours (06:00:00) then it posts only muber 6. That means we see in db TIME field a value 00:00:06.

 

[Camille]

Yes, very strange ! Thanks a lot for your patience, Jaanus ! I hope others will see also this post or perhaps is it better if I recreate a new post specifying the problem ?

 

[Jaanus]

mentioned about it to Hugh (cheesegrits). You can also bump this thread time by time (once-twice per week) when still no answer

 

[Camille]

I don't understand what is cheesegrits but I'll try with a new post.
Thanks a lot !

 

[Jaanus]

I don't understand what is cheesegrits

He is one of the developers. cheesegrits is he's user name here

 

[Camille]

oh I'm confused ! I had not made the connection !