Working with DATETIME fields via ActiveRecord

Yii 1.1 General Discussion
1

Dates in examples (The Yii Blog Tutorial) are stored in DB as integers.

I think it isnt best practice. I wont to store dates in DATETIME format.

But formatting date by php function date() with hardcoded date format in model when i wont to store data isnt best practice too coz some DBMS may use different date format.

What is best way to store date to DB in DATETIME format using CActiveRecord?

2

In your main config file, add an application parameter for whatever dat format you prefer

…

‘params’=>array(

‘myDateFormat’ => ‘Y-m-d’,

…

Then you can use php date like this

…

$myModel->myDate = date(Yii::app()->params[‘myDateFormat’]);

…

3

I added param to application config:




'params'=>array(		

        'dbDateFormat'=>'Y-m-d h:m:s'

	)

and use date(Yii::app()->params[‘dbDateFormat’]);

But i dont like that this param is not in DB configuration (CDBConnection has no such accesible params) and still think that its not best way

4

This is what I do:




<?php

/**

* Helps bridge the gap between php 5.2 and 5.3 DateTime class and also implants

* __toString() to spit out a MySQL datetime string

*/

class EDateTime extends DateTime {

	public static $DBFormat = 'Y-m-d H:i;s';


	public function getTimestamp() {

		return method_exists('DateTime', 'getTimestamp') ?  parent::getTimestamp() : $this->format('U');

	}

	

	function setTimestamp($timestamp) {

		if (method_exists('DateTime', 'setTimestamp')) parent::setTimestamp($timestamp);

		

		$thisz_original = $this->getTimezone()->getName();

		$thisz_utc = new DateTimeZone('UTC');

		$this -> setTimezone($thisz_utc);

		$year = gmdate("Y",$timestamp);

		$month = gmdate("n",$timestamp);

		$day = gmdate("j",$timestamp);

		$hour = gmdate("G",$timestamp);

		$minute = gmdate("i",$timestamp);

		$second = gmdate("s",$timestamp);

		$this -> setDate($year,$month,$day);

		$this -> setTime($hour,$minute,$second);

		$this -> setTimezone(new DateTimeZone($thisz_original));

	}

	

	public function __toString(){

		return (string)parent::format(self::$DBFormat);

	}

}







$model = new Model;

$model->date = new EDateTime('now');
5

I’m just starting to dig in with Yii, and liking it so far. Haven’t really committed to either of the proposed solutions yet for dates, but have a few questions:

First, does this mean that every time I set a date before writing it to MySQL (using the Save operation), I must first convert it to the correct format?

Secondly, is it not strange that this would be set by default in the MySQL provider configuration section, so that the framework could instantiate and work with dates in the "preferred" format by default?

Third, Jonah, where does best practice dictate you put utility code like that? Does it live in its own file? Some place special in the directory structure?

Thanks!!

6

Follow-up for posterity, I was trying to utilize this method and getting odd results. I had to change the dbDateFormat string as follows (note the i instead of m for minutes):




'params'=>array(		

        'dbDateFormat'=>'Y-m-d H:i:s'

	)

Now, every time I write to the (MYSQL) database, it looks like this:




$this->ModifiedOn=date(Yii::app()->params['dbDateFormat']);
7

I personally put it under protected/components, but you can put it anywhere you like as long as the directory is set to auto-include or you manually include it each time

8

Thanks Jonah.

For continuity’s sake, can a mod rename this thread to have “ActiveRecord” spelled correctly? Will help in finding it through search.