calculation of time from row above?

[tusa]

I'm trying to reproduce this in database view

The first column is time for specefic element.
In the first row, the second coloumn is just the time (that's not a problem)

$thisDur = new DateTime('{nemmanus_elementer___elementer_duration}');
$thisDurFormat = $thisDur->format('H:i:s');
return $thisDurFormat;

The third column is a calculation of a time in another table + the second column
How to do that?

And in the second row, in the second column I need to calculate the first column + the second colomn at the row above.
How to do that?

Best regards Tue.

 

[troester]

http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select

Fabrik is working with database tables, not with spredsheets. There's no "row above". You may use "the row with the max id < the current one" or "the row with max creation date < current row" or e.g some sequence element or...

 

[tusa]

http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select

Fabrik is working with database tables, not with spredsheets. There's no "row above". You may use "the row with the max id < the current one" or "the row with max creation date < current row" or e.g some sequence element or...

I got this now, looks almost as my example


How can I then make the calculation like in my spredsheet?

 

[troester]

As I said: http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select
Fetch the time you need via php from the database table.

 

[tusa]

Then I think this is right one

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$myQuery
    ->select('elementer_nr')
    ->from('nemmanus_elementer')
    ->where('elementer_nr = ' . $myDb->quote("2"));

$myDb->setQuery($myQuery);
$fieldA = $myDb->loadResult();

return $fieldA;

But I only get

500 Fabrik has generated an incorrect query for the list Elementer: <br />

If I am at elementer_nr 2 in the list, i need the field elementer_duration and plus with elementer_akk in the elementer_nr 1
Does that make sense at all?

But how to, I can’t figure that out?

 

[troester]

Use the database select examples.

 

[tusa]

Then I think this is right one

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$myQuery
    ->select('elementer_nr')
    ->from('nemmanus_elementer')
    ->where('elementer_nr = ' . $myDb->quote("2"));

$myDb->setQuery($myQuery);
$fieldA = $myDb->loadResult();

return $fieldA;

But I only get


If I am at elementer_nr 2 in the list, i need the field elementer_duration and plus with elementer_akk in the elementer_nr 1
Does that make sense at all?

But how to, I can’t figure that out?

There was an error in the database, I wrote a wrong name for the field... now I got something working!
I think i'm on the right track now.

 

[tusa]

I try to calculate summing 2 fields wich is time elements.

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$myQuery
    ->select('manus_date')
    ->from('nemmanus_manus')
    ->where('manus_titel = ' . $myDb->quote('{nemmanus_elementer___elementer_manus_id}'));

$myDb->setQuery($myQuery);
$myDbManusDate = $myDb->loadResult();
$manusDate = new DateTime($myDbManusDate);
$manusDateFormat = $manusDate->format('H:i:s');

$elementDur = new DateTime('{nemmanus_elementer___elementer_duration}');
$elementDurFormat = $elementDur->format('H:i:s');

return $manusDateFormat + $elementDurFormat;

the manus_date field is a datetime field (2022-07-24 17:30:00)
the elementer_duration field is a time field (00:02:30)

When I sum these 2 fields i get 17 in result.

I know that Calc element is a text field, but how can i get the right result (17:32:30)?

Best regards
Tue.

 

[lousyfool]

There's more than one way to do this. I would convert everything to seconds, add them up, then convert back to database format. So, take a look at this:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$myQuery
    ->select('manus_date')
    ->from('nemmanus_manus')
    ->where('manus_titel = ' . $myDb->quote('{nemmanus_elementer___elementer_manus_id}'));

$myDb->setQuery($myQuery);
$myDbManusDate = $myDb->loadResult();

$manusDateFormat = strtotime($myDbManusDate); // convert manus_date to Unix Epoch Time in seconds
$elementDur = strtotime("1970-01-01 '{nemmanus_elementer___elementer_duration}'"); // convert elementer_duration to seconds
$resultSecs = $manusDateFormat + $elementDur; // add them up

return date("Y-m-d H:i:s", $resultSecs); // convert Unix Epoch Time back to database format

 

[tusa]

I've got it working now.
But one thing..

When I use this

return date("H:i:s"), $resultSecs;

The return is an hour minus, howcome?

 

[lousyfool]

First, date("H:i:s") alone will return the current time, no matter your code before. The following comma and $resultSecs may result in an error, or do nothing or whatever... not tested. So, if you have it exactly like this, you basically have a syntax error... compare with my post above.

It it's only a typo here in your post but correct and working in your code, then the likely reason is different timezones. While in the database it should always be saved in UTC, your global J! or user account setting may be different, etc etc. Can't be said from the distance, so take a closer look at all your relevant settings versus what's stored in the DB, and eventually you'll need to work an offset into your code.

 

[tusa]

First, date("H:i:s") alone will return the current time, no matter your code before. The following comma and $resultSecs may result in an error, or do nothing or whatever... not tested. So, if you have it exactly like this, you basically have a syntax error... compare with my post above.

It it's only a typo here in your post but correct and working in your code, then the likely reason is different timezones. While in the database it should always be saved in UTC, your global J! or user account setting may be different, etc etc. Can't be said from the distance, so take a closer look at all your relevant settings versus what's stored in the DB, and eventually you'll need to work an offset into your code.

Sorry my bad, the right code is:

return date("H:i:s", $resultSecs);

But my problem is now that it returns 3 hours difference.

 

[lousyfool]

But my problem is now that it returns 3 hours difference.

Well, if $elementDur is indeed the correct number of seconds, any difference will come from "manus_date" (and hence $myDbManusDate) being 3 hours off. Note: I'm referring to the variables in my code suggestion.

As already explained here, it's hard to tell from the distance why... could be due to timezone differences. Best check what date & time is stored in the DB for "manus_date", and eventually amend your code with a line to offset (= adding or subtracting those hours, depending on which way it's "off").

 

[tusa]

One of the fields is a TIME field and the other is a CALC field, could that explain something?

 

[lousyfool]

Guess one can only keep repeating...

... take a closer look at all your relevant settings versus what's stored in the DB, and eventually you'll need to work an offset into your code.

Best check what date & time is stored in the DB for "manus_date", and eventually amend your code with a line to offset (= adding or subtracting those hours, depending on which way it's "off").