Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
ronallensmith

Using Current_Timestamp And Datetime

Recommended Posts

Hi,

 

I'm new to Yii and did Larry's series "Learning the Yii Framework" and I'm having an issue with CURRENT_TIMESTAMP and probably DATETIME not displaying in the 'protected.../...employee/create' view "_form.php" when doing the INSERTs.

 

If I do the INSERTs to the "employee" table using phpMyAdmin, the CURRENT_TIMESTAMP variable displays, and MySQL works from the console:

 

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2013-07-30 23:46:30 |
+---------------------+
1 row in set (0.00 sec)

 

Is this a "Windows" thing?

 

Windows XP

Apache 2.4.4

MySQL 5.6.12

PHP 5.4.16

WampServer 2.4

 

Share this post


Link to post
Share on other sites

I'm really not following you, but it's not a Windows thing. The selecting the current timestamp using the mysql client that you show isn't an INSERT at all and has nothing to do with Yii.

Share this post


Link to post
Share on other sites

SOLVED: The following was added to myModelController.php

 

public function actionCreate()
{
        $model=new TblContacts; // default code
        
        // added to insert startTime into _form
        $model->startTime=date('Y-m-d H:i:s', strtotime('-7 Hours'));

 

       // default code following

}

Share this post


Link to post
Share on other sites

SOLVED: The following was added to myModel.php

 

// added to insert endTime upon record INSERT
    public function behaviors()
    {
        return array(
            'CTimestampBehavior' => array(
            'class' => 'zii.behaviors.CTimestampBehavior',
            'createAttribute' => 'endTime',
            'updateAttribute' => NULL,
            )
        );
    }

Share this post


Link to post
Share on other sites

Thanks for the reply Larry,

 

As you can see, I've solved 66% of my problem. Now I need to insert the difference between the above two timestamps into a field called 'duration' in H:i:s format. I've looked all over the Yii docs and the Forum where I came upon afterSave(), and I bought the Yii Book where afterSave() is mentioned. My problem it that I'm seeing different situations where this is used. I think I'm on the right track, but need the final nail in the coffin regarding this problem. I've tried several variations of the following code, but the following post is the only version not producing an error--but it doesn't do anyrhing either.

 

//added the following to myModel.php

 

        protected function afterSave()
        {
            parent::afterSave();
            if ($this->isNewRecord)
            {
                $this->duration = new CDbExpression('NOW()');
            }
        }

 

People have posted me back elsewhere, and I'm still confused on the missing piece. BTW - CDbExpression('NOW()') is just being used to test saving the date. Of course the goal is to include the methods that actually extract the difference in timestamps.

Share this post


Link to post
Share on other sites

I'm sorry, it's not clear what you're trying to do. Could you explain it again? For example, I can't tell if the duration is being saved in the database or not.

Share this post


Link to post
Share on other sites

Hi Larry,

 

Sure. My app keeps track of what was said during phone calls. It tracks the time the call started and the time it ended. However I'm stuck on how to INSERT the duration of the call. I've worked out the start and end times--shown above, but don't quite know how to implement the afterSave(). I see a few examples, but it seems I'm not experienced enough with Yii yet to put it all together properly. The following code does not produce errors, but it doesn't INSERT the correct values either.  I just get 0000-00-00 00:00:00 in the "duration" column.

 

        // added to ...protected\models\TblContacts.php to INSERT difference between startTime and endTime
        protected function afterSave()
        {
            return parent::afterSave();
            if ($this->isNewRecord)
            {
                $this->duration = new CDbExpression('NOW()'); // thinking of using TIMEDIFF() here instead
                // update();
                // update($duration);
                // $duration->update();
                $duration->save();
            }
        }

Share this post


Link to post
Share on other sites

All you need to do is

protected function afterSave()
	{
        if ($this->isNewRecord) 
		{
		$this->duration = new CDbExpression('NOW()');
		} 
	return parent::afterSave();	
	}
}

Or you can add to the rules() array above in your model(), which is actually more tidy, then it could depend on your situation, possibly afterSave is the better way.

array('duration', 'default', 'value'=>new CDbExpression('NOW()'), 'on'=>'insert'),

If this does not work you need to check your data types you have in your database setting duration to a DATETIME may make a difference that's if its currently a Timestamp. I had the same kind of issue as you, although my code was correct i had to make changes to the database in order to get things running.

Share this post


Link to post
Share on other sites

Thanks Edward, the following worked:

 

array('duration', 'default', 'value'=>new CDbExpression('NOW()'), 'on'=>'insert'),

 

The NOW() call is just a simple test to see if the insert works at all. Now I'll replace that with the math expression that subtracts "startTime" from "endTime"--see above posts marked "SOLVED:". I'm thinking of using MySQL's TIMEDIFF() for the math unless you have a better suggestion using a Yii class. The 'afterSave()' example you gave still did not work and if memory serves me well I tried that one before myself, with the same result--nill.

 

Thanks a bunch. This is a breakthrough ...been trying to solve this one for several days.

  • Upvote 1

Share this post


Link to post
Share on other sites

Okay i am glad that has worked out for you, i like the default method personally in the rules() array. Just looking at that again I think we should be using beforeSave() and not afterSave(), i thought yesterday you misplaced the return parent::afterSave(). Do try before save it must work if its not that it must be something to do with SQL data type used for the duration attribute in the table.

Share this post


Link to post
Share on other sites

Thanks again. I'll try beforeSave() as well. My table structure as far as "duration" is concerned is:

 

tblcontacts | CREATE TABLE `tblcontacts` (
`id` int(10) unsigned NOT NULL AUTO_INCREM
`contactType` enum('Inbound Voice-Message'
`startTime` datetime NOT NULL,
`endTime` datetime DEFAULT NULL,
`duration` datetime NOT NULL,

Share this post


Link to post
Share on other sites

Edward,

 

Your suggestion to revisit using 'beforeSave()' worked also. It's funny because I saw both these approached in the Yii book--DOH! but I went with 'afterSave()' instead. I was all over the web too with this problem. After a while I just got snowed under by an avalanche of info and samples. ...takes fresh eyes some times. :) Now, I need to replace CDbExpression() with whatever can do (endTime - startTime) and INSERT into "duration".

 

// added to INSERT (endTime - startTime) into "duration, if possible"
public function beforeSave()
{
        if ($this->isNewRecord)
        {
            $this->duration = new CDbExpression('NOW()'); // This works, but I need to replace it with something like endTime - startTime
        }
return parent::beforeSave();
}

Share this post


Link to post
Share on other sites

Thanks for taking the time Edward,

 

I;m now using:

 

    // added to INSERT (endTime - startTime) into "duration"
    public function beforeSave()
    {
            if ($this->isNewRecord)
            {
                $start = new DateTime($this->startTime);
                $end = new DateTime();
                $diffObj = $end->diff($start);
                $this->duration = $diffObj->format('%H%I%S');
            }
    return parent::beforeSave();
    }

 

and getting:

 

Start Time 2013-09-18 17:31:48 End Time 2013-09-18 17:32:08 Duration 2007-00-20 00:00:00

 

Looks like something's wrong with the way I'm formatting.

Share this post


Link to post
Share on other sites

Hmm, there are a several ways of doing this. Actually thinking about this more closely you don't actually need the duration time saved in your database, you could just use the function DateTime code you have used above and calculate the duration time on the fly. Another way would be to only save start time and end time and use the SQL DATEDIFF() to calculate the duration later. This would actually be faster than the DateTime code above, all though the advantage of using the DateTime interval object is that does allow special date formatting.

 

Otherwise you could use one of the following methods in this thread:

 

http://stackoverflow.com/questions/2622774/find-difference-between-two-dates-in-php-or-mysql

 

I would just try keeping things simple for now, when performance becomes more an issue you can go round looking for parts of code to readily fine tune.

Share this post


Link to post
Share on other sites

Thanks again,

 

The reason for INSERTing the "duration" is because I'll be giving others the ability to login to the site and check the saved records. One item of interest will be "duration"--the time it took for me to complete a task. I'll continue to hammer on the DateTime interval object, but I'll check the others you mentioned.

Share this post


Link to post
Share on other sites

Right i understand what you are saying, i was thinking about it this morning. Another thing you need to look into is the correct data type to use in SQL for saving that duration time.

 

Can you explain to me exactly what units you want the duration time to consist of? May be i can help you with the final solution.

Share this post


Link to post
Share on other sites

Thanks Edward,

 

I took a look at the way the table itself is constructed and went at the problem from the "MySQL command-line" standpoint and discovered I could not manually INSERT the time difference into the "duration" field with:

 

SELECT TIMEDIFF('2013-09-20 13:17:20','2013-09-20 13:08:48');

UPDATE tblcontacts SET duration = '00:08:32' where id = 204;

 

as an example.  But, this was only happening for some of the records and not others.  So, when I looked at that issue, I noticed for some records, "duration" was NULL after the INSERT--displaying as "Not set" in Yii.  All the records that were set this way would not even allow me to do the INSERT manually.  So, I de-selected NULL in phpMyAdmin and was able to manually do the INSERTs using the above "SELECT TIMEDIFF()/UPDATE" commads.

 

Here's what's curious now.  After manually UPDATEing all the NULL records, I'm able to do the manual INSERTs, from the command-line,  even on records that have "duration" set to NULL.  So, now I'm back on the Yii side testing your previous suggestions.

Share this post


Link to post
Share on other sites

I Just tested the following:

 

    // added to INSERT (endTime - startTime) into "duration"
    public function beforeSave()
    {
            if ($this->isNewRecord)
            {
                $start = new DateTime($this->startTime);
                $end = new DateTime();
                $dateIntervalObj = $end->diff($start);
                $this->duration = $dateIntervalObj->format('%H%I%S');
            }
    return parent::beforeSave();
    }
 

which yielded the follwing two records on two tests:

 

View TblContacts #210
ID    210
Contact Type    Inbound Voice-Message
Start Time    2013-09-21 13:17:25
End Time    2013-09-21 13:17:50
Duration    07:00:25

View TblContacts #211
ID    211
Contact Type    Inbound Voice-Message
Start Time    2013-09-21 13:27:51
End Time    2013-09-21 13:31:18
Duration    07:03:27

 

As you can see "Duration" is at least being written too.  I don't know what the problem was before, because this is essentially the same code I was using before--but wasn't working.  I de-selected NULL for "duration", manally cleaned up the table and SET "duration" back to NULL; now the math and INSERTs work.  The only thing left is to adjust for the timezone.

  • Upvote 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

A hearty thanks to you Edward for the leads you gave in working on this solution, and if you or Larry have a better more concise/cleaner way of doing the same thing, or a way the same thing could have been done using the Yii classes, please let me know.

  • Upvote 1

Share this post


Link to post
Share on other sites

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 "endTime" and "duration"
        public function beforeSave()
        {
                if ($this->isNewRecord)
                {
                $this->endTime = date('Y-m-d H:i:s', strtotime('-2 Hours')); // set the endTime
                $start = new DateTime($this->startTime);
                $end = new DateTime($this->endTime);
                $dateIntervalObj = $end->diff($start); // compute the difference in times
                $this->duration = $dateIntervalObj->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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...