PostgreSQL and timestamp

I’m an absolute beginner using the YII framework. I’m trying to learn from the ‘Agile web application development with Yii 1.1 and PHP5’. As my database engine I use PostgreSQL 9.1.

In one of the examples the MySQL data type ‘datetime’ is used. The PostgreSQL equivalent is ‘timestamp’. This works well when you actually fill in a date in the form. When you leave the date empty and you save, Yii receives an error:

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type timestamp with time zone: "". The SQL statement executed was: INSERT INTO "tbl_project" ("name", "description", "create_time", "create_user_id", "update_time", "update_user_id") VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5)

I googled for a bit and came at the a thread at code.google.com with Yii issue 2869 (sorry I’m not allowed to link to it).

In this thread the following is advised:

Can someone point me to an example on how to use the $timestampExpression and in what context?

The actual value inserted into the DB it’s a string with a value of “” (blank) which it’s clearly malformed.

You can implement a beforeSave method on your model and intercep dates which are blank and set them to null, this way psql will hand the timestamp automatically.

Also, as a sidenote, this is not an error, as long as the field it’s a timestamp it’s read-only for you, meaning you don’t need a form field to enter it’s value (just don’t render it in the form view, but do it in the view one).

Hope it helps, greets!

PD: Did you look into CDbExpression(‘NOW()’)? Link related: http://www.yiiframework.com/forum/index.php/topic/32149-application-wide-functions/

Sure. I adjusted my code the following way. I made one general class containing my timestamp rules (because these timestamps are used over several tables and Models):

<?php 

class NRules {

	/**

	 * @return array validation rules for model attributes.

	 */

	public static function timeRules()

	{

		return array(

			array('create_time','default',

			              'value'=>new CDbExpression('NOW()'),

			              'setOnEmpty'=>false,'on'=>'insert'),

			array('update_time','default',

			              'value'=>new CDbExpression('NULL'),

			              'setOnEmpty'=>false,'on'=>'insert'),

			array('update_time','default',

			              'value'=>new CDbExpression('NOW()'),

			              'setOnEmpty'=>false,'on'=>'update'),

		);

	}

	

}


?>

Which I add to the model rules as follows:

	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		return array_merge(array(

			array('create_user_id, update_user_id', 'numerical', 'integerOnly'=>true),

			array('name', 'length', 'max'=>128),

			array('description, create_time', 'safe'),

			array('name', 'required'),

			// The following rule is used by search().

			array('id, name, description, create_time, create_user_id, update_time, update_user_id', 'safe', 'on'=>'search'),

		), NRules::timeRules());

	}

Works like a charm. But when there are better ways to do this, I certainly want to learn about it.