Doctrine2 and Postgresql timestamp with millisecond issue

I’ve been doing a lot of work with Doctrine 2 and finding some issues when dealing with PostgreSQL and Doctrine which I’ll be blogging about. I’ve ignored this particular issue for some time and finally decided to address it.

The issue is declaring a Doctrine entity property as datetime if the column in the db is storing the timestamp with milliseconds such as ‘2012-01-01 10:12:35.542’. When you try to load that into your entity, Doctrine will give you an error. An example of how this can happen is declaring a column timestamp without time zone and using now() as the default value.

Here is an example of a Doctrine entity property declared as a datetime

/**
* @Column(type="datetime")
*/
protected $discontinued;

And what the column sql declaration looks like

discontinued timestamp without time zone NOT NULL

There are three solutions that work, one is to configure Doctrine to not use the default DateTime classes, or you can declare your datetime properties as strings in your entities and convert them to datetime yourself if needed and finally the other approach is to update your db to not store milliseconds. I considered all approaches are decided that it would be better and more efficient to update the db than to write some custom DateTime classes or treat dates as string. If you would like to tackle this issue by creating your own custom DateTime classes then follow this link for more information.

I used the following query to identity all the timestamp columns in my db table:

select table_name, column_name 
from information_schema.colum
where table_catalog = 'gatweb2' and table_schema = 'public' and udt_name like 'timestamp%' 
order by table_name asc

Noticed I used like timestampe% instead of = timestamp. This is because you could have timestamptz (timestamp with time zone) columns as well. Once I had the list, I saved the results, did some search/replace magic to come up with alter statements for each table and ran the queries. I spent a little bit of time looking at cursors to see if I could write a cursor to loop through the list and alter each table but it was taking too long research and search/replaced was quick. This is what the alter queries look like for each table:

alter table 'contact' alter 'date' set data type timestamp(0) without time zone

By altering my timestamp columns to timestamp(0), it lets Postgres know to remove milliseconds from all values as well as to not store milliseconds in the future.

An issue I ran into when running all my alter statements was that some views were referencing some of the columns I was about to alter and Postgres didn’t like that. I did some research and it seems my only choice was to drop those particular views, alter the tables, then recreate the views. I created a big script that did all that, ran it, and now all my issues are gone and my unit test passed.