Date Format and MySQL

Yii 1.1 General Discussion
1

Hi,

my application saves date/time as timestap at an MySQL DB.

Until now, the date format looks like that: 21.01.2010 (Day.Moneth.Year).

To work with i18n dates, i use this extension, which works very well: http://www.yiiframework.com/extension/i18n-datetime-behavior/

I use models to read/write (model->save() ) the date from/to mysql.

Now i changed the dateformat at the Application from "21.01.2010" to "21.01.2010 15:28" (i added the time).

After changing the validation rules, this new date format works very well, until i try to save the date to MySQL.

I used CWebLogRout to see, what’s going on.

Asking the model at the onBeforeSave trigger I see: the date is still in the application-format (day.month.year hour:minute)

But at the next step - looking at the MySQL syntax I see, that Yii tries to write 01.01.1970 to mysql.

Probably there is on step between onBeforeSave Trigger an the MySQL ‘update’ command where the application tries to convert the date format to MySQL Style.

What’s happed there an where should i insert some code to transform the date from application format to MySQL format?

What is recommended (in combination with DateTimeI18NBehavior extension)?

Thank you, rall0r.

2

It’s probably because your timestring is missing seconds. The behavior you mentioned uses CDateTimeParser to parse the input date string. It supplies it with the date and time format from your locale. By default the time format for ‘medium’ is used, which is ‘HH:mm:ss’, for germany.

3

Hi Mike,

thank you for your answer.

I had the same idea an added the following code at the model:


	public function onBeforeSave($event)

	{ $this->STARTDATE=$this->STARTDATE.':00';

	  parent::onBeforeSave($event);

	}

That means: adding the seconds to the datetime-string. Date example for today is: 06.09.2011 14:58:00

But nothing changes. :frowning:

MySQL still tries an update with 1970-01-01

Is it possible, that the combination with the "i18n-datetime-behavior" extension is the problem?

Thank you, rall0r

4

The right method name should be "beforeSave()" not "onBeforeSave()". Can you try again please?

5

I tried different things:

1.) (does not work)

2.) (does not work correctly - no MySQL update fired)

3.) (does not works - update fires, but with non-mysql time-format)

I looked at the documentation, but did not understand the difference between onBeforeSave and beforeSave.

Do i have to add all the things i like to do at the onBeforeX by using the beforeX?

So i don’t need to call something like parent::[on]beforeSave($event);??

6

You should never overwrite onBeforeSave but only beforeSave. And if you do, you should call parent::beforeSave() in this method, because this triggers the onBeforeSave event (and let for example do the behavior do its job).

But anyway: maybe you should do some more debugging and add some print_r() to the behavior? I somehow remember that i had a similar problem with this behavior, but i don’t remember what the problem was.

7

Jup… i did - a lot of.

It looks like, there is the following procedure:

1.) DATA is send with POST to the action

2.) copy posted Data to the model

3.) $model->validate

4.) $model->save

4.1.) BeforeSave: add the secondes to the “STARTDATE” Attribute ($this->STARTDATE=$this->STARTDATE.’:00’; )

4.2.) call: parent::beforeSave();

4.3.) DateTimeI18NBehavior runs

4.3.1) converts the "application"-date (07.09.2011 14:49:02) to MySQL date (2011-09-07 14:49:02) <- which works now

4.4.) …yii generates an sql statement, where STARTDATE is set to 1970-01-01 (output of CWebLogRoute)

How can I debug the next steps?

//Update: i found the problem! It was located in memcache (using schemaCachingDuration) and 2 developer working at the same db. It looks like, on developer is overwriting the schema information in the memcache from the other… With wrong schema, wrong DateTimeI18NBehavior…

Thank you!