Yii Framework Forum: [Solved] ask calculate difference datetime ? - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

[Solved] ask calculate difference datetime ? I wanna ask about how to calculate difference datetime Rate Topic: -----

#1 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 25 April 2012 - 05:05 AM

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 B)?

Thank you for your help
I'm nothing without Your support. ;)

www.dedyisn.net
0

#2 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 25 April 2012 - 05:34 AM

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)',
        ),
    ),
));

0

#3 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 26 April 2012 - 04:45 AM

View Postbennouna, 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';
I'm nothing without Your support. ;)

www.dedyisn.net
0

#4 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 26 April 2012 - 05:45 AM

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


Hope it helps
0

#5 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 26 April 2012 - 07:50 PM

View Postbennouna, 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
I'm nothing without Your support. ;)

www.dedyisn.net
0

#6 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 27 April 2012 - 01:39 AM

Can you post your code?
0

#7 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 27 April 2012 - 04:39 AM

View Postbennouna, 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)'
		),
..........................................


I'm nothing without Your support. ;)

www.dedyisn.net
0

#8 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 27 April 2012 - 05:30 AM

Have you added in your model:
public $timeDifference;


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

#9 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 28 April 2012 - 01:50 AM

View Postbennouna, 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?
I'm nothing without Your support. ;)

www.dedyisn.net
0

#10 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 28 April 2012 - 05:58 AM

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

0

#11 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 28 April 2012 - 07:14 AM

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.. :D
I'm nothing without Your support. ;)

www.dedyisn.net
0

#12 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 28 April 2012 - 10:53 AM

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
0

#13 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 29 April 2012 - 04:10 AM

View Postbennouna, 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
I'm nothing without Your support. ;)

www.dedyisn.net
0

#14 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 29 April 2012 - 05:25 AM

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();
}

0

#15 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 29 April 2012 - 05:40 AM

...
    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 ;D )
0

#16 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 29 April 2012 - 05:52 AM

View Postsoftark, 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" :lol:

Quote

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


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

#17 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 29 April 2012 - 07:43 PM

View Postbennouna, 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" :lol:



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


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)'
		),

...
	),
)); ?>

I'm nothing without Your support. ;)

www.dedyisn.net
0

#18 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 29 April 2012 - 08:17 PM

Have you added in your model:
public $timeDifference;


If yes, please try only with
…
  'columns'=>array(
     'timeDifference'
  ),
…


What do you get?
0

#19 User is offline   Dedy Isnandar 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 06-April 12

Posted 29 April 2012 - 10:33 PM

View Postbennouna, 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
I'm nothing without Your support. ;)

www.dedyisn.net
0

#20 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,168
  • Joined: 05-January 12
  • Location:Morocco

Posted 30 April 2012 - 04:14 AM

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…
0

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users