How to calculate a duration ?

C

Camille

New Member
Hi everybody, I try to learn to work with Fabrik to make a little application wich allows me to fill the schedules of my employees. I made 3 lists : first contains the list of the employees, second contains the list of works and the third is the form I must fill in with by choice of the employee and choice of the work. And there a 2 field : date-start and date-end, with the hour of startijng and the hour of end. The date is stored as Local time with the format %d-%m-%Y %H-%M because I'm in France.

Then I created an element "duration" and in Options => Calculation I get :
Code: 
$debut = JRequest::getVar('planning__date_debut');
$fin = JRequest::getVar('planning__date_fin');
$duree = $fin - $debut;
return $duree;

But the respons in the field duration is 0.

I cann't find the method.
Thanks for your help !
 
Thanks a lot for your reply. But I already tried it ... and nothing in this element.
What is wrong ?
I created an element "duree" with the plug-in calc.
I get the code into "Calculation" in the Options tab.
Calculation only on save is No.
Is it good ?

I had already tried with the code in "Difference in days between 2 dates in PHP". Just I want to have the difference in hours and minutes and I don't know how I must change datediff / 86400. Perhaps it's 86400*24 ?

As I'm with J!2.5 and PHP 5.3, I tried with new DateTime. But it doesn't work too. In the form it displays only 00 in the field "duree"

Code: 
$debut = '{planning__date_debut}';
$debut = new DateTime($debut);
$fin = '{planning__date_fin}';
$fin = new DateTime($fin);
$diff = $debut->diff($fin);
return $diff->format('%h%i');

I red a lot of page about PHP, I tried a lot of code .. but nothing works ...
I need this because, in a second time, I must make statistics for each employee (how many time he works effectivly by day, by week ...)

Thanks for your help because I left a lot of hairs ...
 
I tried with this :
Code: 
$debut = strtotime('{planning__date_debut}');
$fin = strtotime('{planning__date_fin}');
$diff = ($fin - $debut);
return $diff->format(%h%i);
but the result is empty.


Whith the code using new DateTime, the result is 0h0m but I cann't add news data in the list because it returns an error : DateTime::__construct() [datetime.--construct]: Failed to parse time string ({planning__date_debut}) at position 0 ({): Unexpected character
Perhaps Fabrik isn't compatible with that ?

Date_debut and date_fin are differents only on the hours. The difference between both may be only a few minutes.
I really don't understand why it doesn't work :-(
 
Then I would suggest to use date element only for dates (or birthday element instead as it uses naturally DATE, not DATETIME and doesn't have timezone related issues ;)
and create time elemenets (e.g heure_debut and heure_fin). Field type behind time element is TIME. And I think it's easier to use MySQL to calculate the difference :)

You can try then:
PHP: 
$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;
 
$debut = strtotime('{planning__date_debut}');
$fin = strtotime('{planning__date_fin}');
Click to expand...
I didn't check the code but there must be 3 underscores in your placeholders (best is to copy/paste from element list)
'{planning___date_debut}'
 
Jaanus, thanks for your reply but I'm sorry I don't understand what you suggest exactly ! Fields "heure_debut" and "heure_fin" are DATE elements (with the plugin date). Should I do it again with an other plugin ?


troester : thank you very very much !!! I looked up into the night ... just for a missing underscore !! But I didn't find the syntax for Fabrik.
It works now with the code using new DateTime. But not with the other with strtotime.

But if I show the time selector, I've this error when I try to save a new entry with the form :
ateTime::__construct() [datetime.--construct]: Failed to parse time string (2013-05-06 09:00:00,09:00) at position 20 (0): Double time specification

Effectivly, even if I change the date format for the form (without h and m) hours and minutes are saved in the field. If "Show time selector" is Yes, I can fill the time box but when I wish to save a new entry with the form it sees 2x the hour.

I'm sorry, I'm french and it'sn't very easy to explain ... I hope you understand what I wish to say...

Fabrik is a very great tool ! I don't know if an other can make what I want ! But it's not easy to find tutorials for other than simple forms to manage users ...
I'm really glad to learn it !!
I wish to know if it's possible to make calc on the field "duree" wich has TEXT type to know how many time each employee works effectivly.
I found how to count the number of each task, and filter it by name. But how to do the total of hours and minutes, by name, and by period (by day, by week ...) ...

And how to give different color into the vizualisation for each employee ...
As you can see ... I've a lot to learn ... and probably a lot of questions -))
 
Camille said:
Jaanus, thanks for your reply but I'm sorry I don't understand what you suggest exactly ! Fields "heure_debut" and "heure_fin" are DATE elements (with the plugin date). Should I do it again with an other plugin ?
Click to expand...

I meant use of "time" element plugin (this is included now in main installation package, you should have it), as the database field behind this plugin is TIME field. So I suggested to create additional elements & db fields. As you already have
planning___date_debut & planning___date_fin
the additional elements would be
planning___heure_debut & planning___heure_fin

BTW - I didn't understand what database field is behind your date_debut and date_fin ? DATETIME (this is the native field type behind "date" element) or DATE? If just DATE then you have nothing to do with the time selector because this data is never saved in db. Then you need separate TIME fields in database anyway and "time" element plugin is created for this purpose.

IMO - using separate fields for date and time seems to be the best way to building event-related applications. It allows easily separate the events with start and end time and the "all-day" events.
 
Hi Jaanus and a big thanks for your explanations ! In the database it's DATETIME behind the fields date_debut and date_fin. I tried to change it with DATE in the database. When I saved the element, a message says me it want to do a change of the field data in de DB. It's seems if I save, the structure is changed. But if I get "cancel" the structure is always DATE. It's good ?
Then I created 2 news elements heure_debut and heure_fin with the plugin TIME. It's ok to get data in the list.
But with "show the data" there're 2 lines of error for one line of datas :

Notice: Undefined offset: 2 in C:\wamp\www\cds-informatik\plugins\fabrik_element\time\time.php on line 426

Notice: Undefined offset: 2 in C:\wamp\www\cds-informatik\plugins\fabrik_element\time\time.php on line 426

In the database, the structure of these fields is TEXT. May be that can be a problem to do calculations on the hours, no ?
Perhaps it's better I change it to TIME ?
Because I wish calculate the during of each task. And make the sum for each employee.

A last thing (before other ;-) ) I wish to get a error if the heure_fin is before the heure_debut.
Where can I do that ?? In the view Options of the element (button Y or No for Validation Option) or in the tab Validations ? I tried with this one with the action isgreaterorlessthan but it doesn't work (no message error if I get a second hour before the first.).
Perhaps because I didn't get anything in Condition ?

I'm sorry for my english !
[edit] I left my computer during the time to eat. And when I came back the 2 notices about Undefined offset disappeared !! [/edit]
 
Yes, change them to TIME. I don't know why TEXT field was created initially, it's a bug.
Suggest also to set a structure change option in list admin to "Only create new fields" or something like that it was written, I don't remember exactly. This allows to create new fields with new elements but doesn't force to change field structure when you edit element and save.
 
Ok, I found "Only add new fields". Thanks !!

Then, the fields heure_debut and heure_fin are TIME in the database and made with the plugin time in Fabrik.
The list displays the element " duree" in hours and minute with this code (but in the database, the field duree contains NULL):

Code: 
$debut = '{planning___heure_debut}';
$debut = new DateTime($debut);
$fin = '{planning___heure_fin}';
$fin = new DateTime($fin);
$diff = $debut->diff($fin);
return $diff->format('%hh:%m');

But when I try to add a new entry with 08h11m as heure_debut I have this error :
DateTime::__construct() [datetime.--construct]: Failed to parse time string (08,11) at position 0 (0): Unexpected character

I don't understand where is the unexpected character ...

I'm sorry ...
 
Don't use anything that contains dateTime :)
I suppose you are using calc element for this? So try this code instead (that I suggested above):
PHP: 
$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;
 
Yeah !!! it works without error !!! Thousand thanks !!! But ... if I can ... because I'm an annoying woman ;-)) is it possible to give a format at the result ? I tried return $duration->format(%hh%im); but the result is empty.
I wish to have only hours and minutes ... and after to make the sum.

thanks for your patience !!
 
Maybe some trim or str_replace function would help as you just have to remove :00 ?
Rather no as you intend to calculate some total. Not sure. Anyone else can help?
 
I tryed this and it displays only hours and minutes in the list :
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;
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 ?

To calculate the sum of this field duree, can I write in Calcs=> Custom Counter => Custom Request this code:
Code: 
$db = JFactory::getDbo();
$query = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC('{planning___duree}') ))";
$db->setQuery($query);
$total = $db->loadResult();
return $total;
Jaanus, I made good progress thanks you !!
Thousand thanks !
 
According to the way your code was formatted here, it seems you have 2 underscores rather than 3 underscores between the list and the element names. Just something to look at. :p

Camille said:
Thanks a lot for your reply. But I already tried it ... and nothing in this element.
What is wrong ?
I created an element "duree" with the plug-in calc.
I get the code into "Calculation" in the Options tab.
Calculation only on save is No.
Is it good ?

I had already tried with the code in "Difference in days between 2 dates in PHP". Just I want to have the difference in hours and minutes and I don't know how I must change datediff / 86400. Perhaps it's 86400*24 ?

As I'm with J!2.5 and PHP 5.3, I tried with new DateTime. But it doesn't work too. In the form it displays only 00 in the field "duree"

Code: 
$debut = '{planning__date_debut}';
$debut = new DateTime($debut);
$fin = '{planning__date_fin}';
$fin = new DateTime($fin);
$diff = $debut->diff($fin);
return $diff->format('%h%i');

I red a lot of page about PHP, I tried a lot of code .. but nothing works ...
I need this because, in a second time, I must make statistics for each employee (how many time he works effectivly by day, by week ...)

Thanks for your help because I left a lot of hairs ...
Click to expand...
 
Camille said:
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 ?
Click to expand...
Yes and you should resave each entry then the calc result will be saved in db field.
Or if you have already lot of records, do it in your database client (phpmyadmin or whatever):

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

Camille said:
To calculate the sum of this field duree, can I write in Calcs=> Custom Counter => Custom Request this code:
Code: 
$db = JFactory::getDbo();
$query = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC('{planning___duree}') ))";
$db->setQuery($query);
$total = $db->loadResult();
return $total;
Thousand thanks !
Click to expand...

No idea what is Calcs=> Custom Counter => Custom Request :)
But the query would then be best to do against the db table,
that means planning.duree instead of '{planning___duree}' and FROM planning (with possible WHERE clause) at the end of query
 
I'm sorry, I translated from french ;-)
"Custom query" in Custom calc" in "Calculations" here http://fabrikar.com/wiki/index.php/Image:Element_calculations.png

I try to understand "
  • Custom query - Query fragment to generate result using %s to represent the field name for this element (do NOT use the actual field name, you must use %s). So to generate the population standard deviation for this element, this would be "STDDEV_POP(%s)&quot (no quotes)."
and
"Split PHP - If using Split feature, you need to provide the PHP code which will return your calculation, given a set of data in the array $data. For example, to return the standard devation (assuming you have the PECL stats library installed) would be "return stats_standard_deviation($data);" (no quotes). No placeholder replacement is done on this code. You may use any valid PHP, which operates on $data and returns a single result."
I'm looking for an exemple because as it's in english I'm afraid misunderstood some things.
Then ...I wish to do the calc on the field "duree" wich is the difference between heure_debut and heure_fin. These fields have the structur TIME in the database. But the field "duree" is TEXT. In the list element, the difference is displayed. But in the database the valeur of the field "duree" is 00:00:00. I tried to get TIME behind "duree" in the database but then nothing is displayed in the list of element. If the contain of the field "duree" in the database isn't good, I suppose it's impossible to do the calc ...
About what you say me about the query, is it possible to get the code into custom query ?
thanks !
 
Change the dur?e field also to TIME.
Don't forget to read fully my post above (about 00:00:00 (UPDATE query etc).
the code into custom query?
I quess no. It seems to be just for query like SELECT SUM(tablename.fieldname) etc
But here it may be SELECT SUM(%s)
No more ideas. Never tested. You should hope that any other can help, because it's a Chinese for me as well.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 114 (members: 1, guests: 113)
    Back
    Top