Inserting A Difference In Timestamps

ronallensmith · September 8, 2013, 4:25am

Hi,

I need to INSERT the difference in two separate timestamps.

The Model Controller has:




	public function actionCreate()

	{

		...

		

		// added to insert start time into _form

		$model->startTime=date('Y-m-d H:i:s', strtotime('-7 Hours'));


		...

	}

and the Model has:




	// added to insert end time upon record INSERT

	public function behaviors()

	{

		return array(

			'CTimestampBehavior' => array(

			'class' => 'zii.behaviors.CTimestampBehavior',

			'createAttribute' => 'endTime',

			'updateAttribute' => NULL,

			)

		);

	}

I have another attribute “duration” looking to receive the difference between these two timestamps in ‘H:i:s’. All I can find in the MySQL docs is how to do this after the record has been created. I looked through the Yii Class references, but couldn’t find anything that will do the math on the fly as the last thing before the record INSERT. Maybe I’m overlooking something. Can anyone help with this one?

nineinchnick · September 8, 2013, 8:10am

The CTimestampBehavior behavior fills those attributes in the beforeSave() method. It’s rather simple so you could do it on your own and add filling the difference value.

If you would use a default value for the ‘endTime’ attribute in the database you would have to use the afterSave() method and issue an ‘update’ right after ‘insert’.

ronallensmith · September 8, 2013, 9:56pm

Yep,

endTime does the INSERT perfectly. The only missing piece is where/how to include the math/code needed to INSERT the difference between the two times into the table last. I’ll look into afterSave().

ronallensmith · September 8, 2013, 10:31pm

Quick question. Looks like afterSave() is what to use. I couldn’t get a clear picture of where/how to use it in my particular situation though. My best guess is to use it in the Model, right?

ronallensmith · September 9, 2013, 3:16am

I used the following in the Model:




		// added to INSERT difference between startTime and endTime

		protected function afterSave()

		{

			parent::afterSave();

				$this->duration = new CDbExpression('NOW()');

		}

Although this is just a test to get things going, nothing saves to the table’s duration field, which is typed as datetime. The rest of the data INSERTs without error. Can anyone clarify?

yugenekr · September 9, 2013, 10:13am

You need to save your model to db now (update it). afterSave() just sets attribute value without storing it persistently.

nineinchnick · September 9, 2013, 4:38pm

But if you’d call save() from afterSave() in same model you would create a loop. So check if duration has already been set (is not null).

ronallensmith · September 10, 2013, 8:33am

The following is my latest effort; just getting syntax errors now:




		// added to INSERT difference between startTime and endTime

		protected function afterSave()

		{

			parent::afterSave();

			if ($this->isNewRecord)

			{

				$this->duration = new CDbExpression('NOW()');

				duration->update(); // unexpected '->' (T_OBJECT_OPERATOR)

				// update(array $attributes=duration); // unexpected '$attributes' (T_VARIABLE), expecting '('

			}	

		}

Can anyone point me in the right direction?

Ankit_Modi · September 10, 2013, 9:02am

try this

i think parent::afterSave() before the if loop


 protected function afterSave()

                {

                        

                        if ($this->isNewRecord)

                        {

                                $this->duration = new CDbExpression('NOW()');

                                duration->update(); // unexpected '->' (T_OBJECT_OPERATOR)

                                // update(array $attributes=duration); // unexpected '$attributes' (T_VARIABLE), expecting '('

parent::afterSave();

                        }       

                }

second method


 public function afterFind()

            {

                $this->created_at = strtotime($this->duration);

                $this->duration = date('m/d/Y', $this->duration);

               parent::afterFind ();

            }

ronallensmith · September 16, 2013, 5:35am

Hey ‘nineinchnick’,

I checked my table structure and found that ‘duration’ was set to NULL as the default. So, when I unchecked the NULL box and set the default to NONE in phpMyAdmin, I got 0000-00-00 00:00:00 after clicking the “Create” button on the tblContacts/create form with the following code:

Maggie Q, I moved ‘return parent::afterSave();’ as suggested.

Yugene, I’m not sure if I’m doing the update correctly.




// added to INSERT difference between startTime and endTime

protected function afterSave()

{

	return parent::afterSave();

	if ($this->isNewRecord)

	{

		$this->duration = new CDbExpression('NOW()');

		// update();

		// update($duration);

		$duration->update();

	}

}

My table structure is:

mysql> show create table tblcontacts;

±------------±--------------------------------------------

| Table | Create Table

±------------±--------------------------------------------

| tblcontacts | CREATE TABLE tblcontacts (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

contactType enum(‘Inbound Voice-Message’,

startTime datetime NOT NULL,

endTime datetime DEFAULT NULL,

duration datetime NOT NULL,

±------------±--------------------------------------------

The INSERT produces:

ID 122

Contact Type Inbound Voice-Message

Start Time 2013-09-15 21:34:09

End Time 2013-09-15 21:34:32

Duration 0000-00-00 00:00:00

No errors now, but something is still missing or wrong. Any additional suggestions?

ronallensmith · September 22, 2013, 2:28am

SOLVED: The following was added to myModel.php




// added to INSERT (endTime - startTime) into "duration"

public function beforeSave()

{

        if ($this->isNewRecord)

        {

            $start = new DateTime($this->startTime);

            $end = new DateTime();

            $endMinus7 = $end->sub(new DateInterval('PT7H')); // correct for 7-hour time difference

            $dateIntervalObj = $endMinus7->diff($start); // compute the difference in times

            $this->duration = $dateIntervalObj->format('%H%I%S');

        }

return parent::beforeSave();

}

Thanks to everyone who posted with input regarding this problem.

ronallensmith · September 24, 2013, 5:48am

I’m adding a heads-up here! When I uploaded my app, the math on the time changed because the hosting server was in a different timezone. So… Arghh!! I commented-out the ‘public function behaviors()’ code and changed the ‘public function beforeSave()’ code to:

// added to INSERT &quot;endTime&quot; and &quot;duration&quot;


    public function beforeSave()


    {


            if (&#036;this-&gt;isNewRecord)


            {


            &#036;this-&gt;endTime = date('Y-m-d H:i:s', strtotime('-2 Hours')); // set the endTime


            &#036;start = new DateTime(&#036;this-&gt;startTime);


            &#036;end = new DateTime(&#036;this-&gt;endTime);


            &#036;dateIntervalObj = &#036;end-&gt;diff(&#036;start); // compute the difference in times


            &#036;this-&gt;duration = &#036;dateIntervalObj-&gt;format('%H%I%S'); // format and set the difference in time


            }


    return parent::beforeSave();


    }

I hope this helps someone who runs accross the same problem.

nineinchnick · September 24, 2013, 2:07pm

Don’t mix DateTime with date(), time() and strtotime() functions. DateTime doesn’t use timezones if not specified in parsed string. Older functions assume a default timezone configured in PHP if none was specified in parsed string.

I use date(‘Y-m-d’, strtotime($input)) to reformat $input, but I avoid passing anything calculated by that set of functions to DateTime and vice-versa.