ronallensmith Posted July 31, 2013 Share Posted July 31, 2013 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 Link to comment Share on other sites More sharing options...
Larry Posted August 3, 2013 Share Posted August 3, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 12, 2013 Author Share Posted September 12, 2013 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 } Link to comment Share on other sites More sharing options...
ronallensmith Posted September 12, 2013 Author Share Posted September 12, 2013 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, ) ); } Link to comment Share on other sites More sharing options...
ronallensmith Posted September 12, 2013 Author Share Posted September 12, 2013 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. Link to comment Share on other sites More sharing options...
Larry Posted September 17, 2013 Share Posted September 17, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 17, 2013 Author Share Posted September 17, 2013 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(); } } Link to comment Share on other sites More sharing options...
Edward Posted September 17, 2013 Share Posted September 17, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 17, 2013 Author Share Posted September 17, 2013 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. 1 Link to comment Share on other sites More sharing options...
Edward Posted September 18, 2013 Share Posted September 18, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 18, 2013 Author Share Posted September 18, 2013 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, Link to comment Share on other sites More sharing options...
ronallensmith Posted September 18, 2013 Author Share Posted September 18, 2013 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();} Link to comment Share on other sites More sharing options...
Larry Posted September 18, 2013 Share Posted September 18, 2013 Thanks, Edward! 1 Link to comment Share on other sites More sharing options...
Edward Posted September 18, 2013 Share Posted September 18, 2013 I would suggest using the PHP DateTime Class to calculate the difference, http://www.php.net/manual/en/book.datetime.php $d1 = new DateTime($this->registration_date); $d2 = new DateTime(); $dateIntervalObj = $d2->diff($d1); Link to comment Share on other sites More sharing options...
ronallensmith Posted September 19, 2013 Author Share Posted September 19, 2013 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. Link to comment Share on other sites More sharing options...
Edward Posted September 19, 2013 Share Posted September 19, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 20, 2013 Author Share Posted September 20, 2013 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. Link to comment Share on other sites More sharing options...
Edward Posted September 20, 2013 Share Posted September 20, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 21, 2013 Author Share Posted September 21, 2013 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. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 21, 2013 Author Share Posted September 21, 2013 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 #210ID 210Contact Type Inbound Voice-MessageStart Time 2013-09-21 13:17:25End Time 2013-09-21 13:17:50Duration 07:00:25View TblContacts #211ID 211Contact Type Inbound Voice-MessageStart Time 2013-09-21 13:27:51End Time 2013-09-21 13:31:18Duration 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. 1 Link to comment Share on other sites More sharing options...
ronallensmith Posted September 21, 2013 Author Share Posted September 21, 2013 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();} Link to comment Share on other sites More sharing options...
ronallensmith Posted September 21, 2013 Author Share Posted September 21, 2013 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. 1 Link to comment Share on other sites More sharing options...
Edward Posted September 22, 2013 Share Posted September 22, 2013 Glad you managed to get that worked out thanks for sharing the solution. Link to comment Share on other sites More sharing options...
ronallensmith Posted September 24, 2013 Author Share Posted September 24, 2013 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. Link to comment Share on other sites More sharing options...
Larry Posted September 25, 2013 Share Posted September 25, 2013 Excellent. Thanks for sharing! Link to comment Share on other sites More sharing options...
Recommended Posts