[Dedy Isnandar]

Hi everyone, I'm still just learning in Yii
I want to ask How do I calculate the difference in time and display it in the Grid View?

For example, I've got the data Column A: 25/04/2012 15:30

Data Column B: 04/25/2012 17:10

How to display data in grid view such a Column C: 100 Minutes or 1 Hours 40 Minutes (from calculations of data in columns A and ?

Thank you for your help

[bennouna]

Well since you'll be using a CActiveDataProvider, here's what I did in a previous project, provided my two columns are of datetime field type.

In my model

…
public $timeDifference;
…
public function search() {
    $criteria=new CDbCriteria;
    …
    $criteria->select = 'TIME_TO_SEC(TIMEDIFF(columnB, columnA)) AS timeDifference';
    …
}

That gives the difference in seconds.

Now, in my view with the CGridView:

function toTimeDifference($seconds)
{
    $d = (int)($seconds / 3600 / 24);
    $h = (int)(($seconds - $d*24*3600) / 3600);
    $m = (int)(($seconds - $d*24*3600 - $h*3600) / 60);
    $s = (int)($seconds - $d*24*3600 - $h*3600 - $m*60);
    return ($d ? $d . ' day' . ($d > 1 ? 's' : '') . ' ' : '') . ($h ? $h . ' hour' . ($h > 1 ? 's' : '') . ' ' : '') . ($m ? $m . ' minute' . ($m > 1 ? 's' : '') . ' ' : '') . ($s ? $s . ' second' . ($s > 1 ? 's' : '') . ' ' : '');
}
…
$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'some-grid',
    'dataProvider'=>$model->search(),
    …
    'columns'=>array(
        array(
            'value'=>'toTimeDifference($data->timeDifference)',
        ),
    ),
));

[Dedy Isnandar]

bennouna, on 25 April 2012 - 05:34 AM, said:

Well since you'll be using a CActiveDataProvider, here's what I did in a previous project, provided my two columns are of datetime field type.

In my model

…
public $timeDifference;
…
public function search() {
    $criteria=new CDbCriteria;
    …
    $criteria->select = 'TIME_TO_SEC(TIMEDIFF(columnB, columnA)) AS timeDifference';
    …
}

That gives the difference in seconds.

Now, in my view with the CGridView:

function toTimeDifference($seconds)
{
    $d = (int)($seconds / 3600 / 24);
    $h = (int)(($seconds - $d*24*3600) / 3600);
    $m = (int)(($seconds - $d*24*3600 - $h*3600) / 60);
    $s = (int)($seconds - $d*24*3600 - $h*3600 - $m*60);
    return ($d ? $d . ' day' . ($d > 1 ? 's' : '') . ' ' : '') . ($h ? $h . ' hour' . ($h > 1 ? 's' : '') . ' ' : '') . ($m ? $m . ' minute' . ($m > 1 ? 's' : '') . ' ' : '') . ($s ? $s . ' second' . ($s > 1 ? 's' : '') . ' ' : '');
}
…
$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'some-grid',
    'dataProvider'=>$model->search(),
    …
    'columns'=>array(
        array(
            'value'=>'toTimeDifference($data->timeDifference)',
        ),
    ),
));

Thank you.. How to replace ColumnA and ColumnB to data from database , example in ColumnA i use database with column name = startdate, and ColumnB with column name = stopdate ?

$criteria->select = 'TIME_TO_SEC(TIMEDIFF(columnB, columnA)) AS timeDifference';

[bennouna]

$criteria->select = 'TIME_TO_SEC(TIMEDIFF(stopdate, startdate)) AS timeDifference';

Hope it helps

[Dedy Isnandar]

bennouna, on 26 April 2012 - 05:45 AM, said:

$criteria->select = 'TIME_TO_SEC(TIMEDIFF(stopdate, startdate)) AS timeDifference';

Hope it helps

Error appear

Trying to get property of non-object

[bennouna]

Can you post your code?

[Dedy Isnandar]

bennouna, on 27 April 2012 - 01:39 AM, said:

Can you post your code?

In Models

public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria;

		
		$criteria->compare('id',$this->id);
		$criteria->compare('user_id',$this->user_id);
		$criteria->compare('sites_id',$this->sites_id);
		$criteria->compare('degon',$this->degon,true);
		$criteria->compare('degoff',$this->degoff,true);
		$criteria->compare('keterangan',$this->keterangan,true);
		$criteria->compare('genoperasi',$this->genoperasi);
		$criteria->select = 'TIME_TO_SEC(TIMEDIFF(degon, degoff)) AS timeDifference';
		

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}

in views/operator/admin

<? function toTimeDifference($seconds)
{
    $d = (int)($seconds / 3600 / 24);
    $h = (int)(($seconds - $d*24*3600) / 3600);
    $m = (int)(($seconds - $d*24*3600 - $h*3600) / 60);
    $s = (int)($seconds - $d*24*3600 - $h*3600 - $m*60);
    return ($d ? $d . ' day' . ($d > 1 ? 's' : '') . ' ' : '') . ($h ? $h . ' hour' . ($h > 1 ? 's' : '') . ' ' : '') . ($m ? $m . ' minute' . ($m > 1 ? 's' : '') . ' ' : '') . ($s ? $s . ' second' . ($s > 1 ? 's' : '') . ' ' : '');
};?>

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'operator-grid',
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	'columns'=>array(
........................................
                array(
			'header'=>'Lama DEG Operasi',
			'value'=>'toTimeDifference($data->timeDifference)'
		),
..........................................

[bennouna]

Have you added in your model:

public $timeDifference;

And make sure that degon and degoff are datetime fields in your db

[Dedy Isnandar]

bennouna, on 27 April 2012 - 05:30 AM, said:

Have you added in your model:

public $timeDifference;

And make sure that degon and degoff are datetime fields in your db

The Format of data that in database is : 25/04/2012 15:30

I don't know how to convert it to datetime , because i used Cjuidatetimepicker for the input form,
is there a way to make the data becom datetime format in database?

[bennouna]

If you don't store those 2 fields in the database, you won't be able to use SQL date functions. There are two scenarios:

• You can change your database structure. So change those 2 fields into datetime. And add some code to convert your dates strings to correct datetime. There are other ways more Yii and more MVC (e.g. http://www.yiiframew...CDateTimeParser and http://www.yiiframew...extension/time/), but to begin with, what I would do is add to my model:
  

  protected function beforeSave()
  {
      …
      list($c_date, $s, $c_time) = explode(' ', $this->degon);
      list($c_d, $c_m, $c_y) = explode('/', $c_date);
      list($c_h, $c_i, $c_s) = explode(':', $c_time);
      $this->degon = date('Y-m-d H:i:s', mktime($c_h, $c_i, $c_s, $c_m, $c_d, $c_y));
      list($c_date, $s, $c_time) = explode(' ', $this->degoff);
      list($c_d, $c_m, $c_y) = explode('/', $c_date);
      list($c_h, $c_i, $c_s) = explode(':', $c_time);
      $this->degoff = date('Y-m-d H:i:s', mktime($c_h, $c_i, $c_s, $c_m, $c_d, $c_y));
      …
      return parent::beforeSave();
  }

  
  
  And if you display degon and degoff in your views, you'd need also the following
  

  protected function afterFind()
  {
      …
      $this->degon = date('d/m/Y H:i', strtotime($this->degon));
      $this->degoff = date('d/m/Y H:i', strtotime($this->degoff));
      …
      return parent::afterFind();
  }

  
  
• You can't change your database structure. Then you leave them as strings, forget the SQL function, and convert the fields in PHP, (explode, then strtotime, then search over the Web, e.g. http://stackoverflow...dates-using-php and pay attention to your PHP version).

This post has been edited by bennouna: 28 April 2012 - 06:06 AM

[Dedy Isnandar]

Do i need to change the input form too ?

My form input is :

?php Yii::import('application.extensions.CJuiDateTimePicker.CJuiDateTimePicker');
    $this->widget('CJuiDateTimePicker',array(
        'model'=>$model,
        'attribute'=>'degon', 
        'mode'=>'datetime' 
    ));
?>

I already use function BeforeSave but when i try submit the data using my form, it say Undefined Offset : 2

For information when input date, the data is look like 28/402/2012 15:22

I'm sorry for this bad question..

[bennouna]

It seems your form input is ok. But you say you have an error with beforeSave, I think you should post your code, and the exact error output to help us help you

[Dedy Isnandar]

bennouna, on 28 April 2012 - 10:53 AM, said:

It seems your form input is ok. But you say you have an error with beforeSave, I think you should post your code, and the exact error output to help us help you

function before save is same with the code that you type before,

protected function beforeSave()
{
    list($c_date, $s, $c_time) = explode(' ', $this->degon);
    list($c_d, $c_m, $c_y) = explode('/', $c_date);
    list($c_h, $c_i, $c_s) = explode(':', $c_time);
    $this->degon = date('Y-m-d H:i:s', mktime($c_h, $c_i, $c_s, $c_m, $c_d, $c_y));
    list($c_date, $s, $c_time) = explode(' ', $this->degoff);
    list($c_d, $c_m, $c_y) = explode('/', $c_date);
    list($c_h, $c_i, $c_s) = explode(':', $c_time);
    $this->degoff = date('Y-m-d H:i:s', mktime($c_h, $c_i, $c_s, $c_m, $c_d, $c_y));
    return parent::beforeSave();
}

i try this command first, but get error offset 2
then i try another code to make the date format look like my data ( 28/02/2012 15:22 ) :

protected function beforeSave()
{
    list($c_date, $s, $c_time) = explode(' ', $this->degon);
    list($c_d, $c_m, $c_y) = explode('/', $c_date);
    list($c_h, $c_i, $c_s) = explode(':', $c_time);
    $this->degon = date('dd/MM/yyyy H:i:s', mktime($c_h, $c_i, $c_s, $c_m, $c_d, $c_y));
    list($c_date, $s, $c_time) = explode(' ', $this->degoff);
    list($c_d, $c_m, $c_y) = explode('/', $c_date);
    list($c_h, $c_i, $c_s) = explode(':', $c_time);
    $this->degoff = date('dd/MM/yyyy H:i:s', mktime($c_h, $c_i, $c_s, $c_m, $c_d, $c_y));
 return parent::beforeSave();
}

but still produce the same error

[bennouna]

What you've tried cannot work because datetime field must have YYYY-MM-DD HH:mm:ss format, which in PHP date function is Y-m-d H:i:s

On the other hand, with my code, I guess you get an error because you don't have seconds. Try this:

protected function beforeSave()
{
    list($c_date, $s, $c_time) = explode(' ', $this->degon);
    list($c_d, $c_m, $c_y) = explode('/', $c_date);
    list($c_h, $c_i) = explode(':', $c_time);
    $this->degon = date('Y-m-d H:i:s', mktime($c_h, $c_i, 0, $c_m, $c_d, $c_y));
    list($c_date, $s, $c_time) = explode(' ', $this->degoff);
    list($c_d, $c_m, $c_y) = explode('/', $c_date);
    list($c_h, $c_i) = explode(':', $c_time);
    $this->degoff = date('Y-m-d H:i:s', mktime($c_h, $c_i, 0, $c_m, $c_d, $c_y));
    return parent::beforeSave();
}

[softark]

...
    list($c_date, $c_time) = explode(' ', $this->degon);
...
    list($c_date, $c_time) = explode(' ', $this->degoff);
...

Without $s.
(It's a relief to know that even bennouna can make a simple mistake )

[bennouna]

softark, on 29 April 2012 - 05:40 AM, said:

...
    list($c_date, $c_time) = explode(' ', $this->degon);
...
    list($c_date, $c_time) = explode(' ', $this->degoff);
...

Without $s.

Yes you're totally right softark. I'm sorry, I used a code of mine where dates have a format like "25/04/2012 à 12:52:54"

Quote

(It's a relief to know that even bennouna can make a simple mistake )

You're teasing me, but we are all here to learn

[Dedy Isnandar]

bennouna, on 29 April 2012 - 05:52 AM, said:

Yes you're totally right softark. I'm sorry, I used a code of mine where dates have a format like "25/04/2012 à 12:52:54"



You're teasing me, but we are all here to learn

OK .. Thanks now it works when i input the form..
Now i try to use the first function (timediff ) to appear on my gridview, but after the process, ( i try to open admin ), it produce error : Trying to get property of non-object

<? function toTimeDifference($seconds)
{
    $d = (int)($seconds / 3600 / 24);
    $h = (int)(($seconds - $d*24*3600) / 3600);
    $m = (int)(($seconds - $d*24*3600 - $h*3600) / 60);
    $s = (int)($seconds - $d*24*3600 - $h*3600 - $m*60);
    return ($d ? $d . ' day' . ($d > 1 ? 's' : '') . ' ' : '') . ($h ? $h . ' hour' . ($h > 1 ? 's' : '') . ' ' : '') . ($m ? $m . ' minute' . ($m > 1 ? 's' : '') . ' ' : '') . ($s ? $s . ' second' . ($s > 1 ? 's' : '') . ' ' : '');
};?>
<?php $this->widget('zii.widgets.grid.CGridView', array(
..........
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	'columns'=>array(
		'id'
		'user_id',
		'sites_id',
		'degon',
		'degoff',
		array(
			'header'=>'Lama DEG Operasi',
			'value'=>'toTimeDifference($data->timeDifference)'
		),

...
	),
)); ?>

[bennouna]

Have you added in your model:

public $timeDifference;

If yes, please try only with

…
  'columns'=>array(
     'timeDifference'
  ),
…

What do you get?

[Dedy Isnandar]

bennouna, on 29 April 2012 - 08:17 PM, said:

Have you added in your model:

public $timeDifference;

If yes, please try only with

…
  'columns'=>array(
     'timeDifference'
  ),
…

What do you get?

Yes i already added

public $timeDifference;

i try this

…
  'columns'=>array(
     'timeDifference'
  ),
…

but produces "data.column.0 is not defined"

I try this :

…
  'columns'=>array(
     'value'=>'timeDifference'
  ),
…

But produces "Trying to get property of non-object"

Thank you

[bennouna]

Hi again

Can you check into your database and see what are the contents of degon and degoff fields?

THere's something going wrong somewhere…