3

I am having a issue with a date format issue. I am using mysql, doctrine2, and php 5.3.

The issue is when I query the database getRepository('Entity\\Srm').findBy($id) I end up with a negative date value of: -0001-11-30 00:00:00.

The value that is currently store in the database is 0000-00-00. The datatype of the field in mysql is date.

The doctrine entity has the variable identified as a date type.

I have done tons of searching with and no good leads.

Any Ideas?

Thanks

eggyal
  • 122,705
  • 18
  • 212
  • 237
robasc
  • 307
  • 1
  • 5
  • 15
  • The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. Maybe that's why it returns negative date. – Leri Jun 05 '12 at 12:49
  • Ok, so what is the best way to properly handle the issue. I have to deal with the values currently in the database. – robasc Jun 05 '12 at 12:53
  • [Here](http://php.net/manual/en/function.checkdate.php#78362) is nice function that helped me with problem something like this. – Leri Jun 05 '12 at 12:56
  • Something else that is strange. If I query the database using createQuery then my data is returned as an array of values and are shown exactly as what is in the database. The date values are returned as string and not datetime. – robasc Jun 05 '12 at 13:11
  • Because of such behaviors of ORMs written in php, I've created my own (I don't say that it's better but it's easier to find where the problem actually is for me). My advice is to take a look how these methods process data retrieved from database. – Leri Jun 05 '12 at 13:14
  • Some kind of strange timezone offset from your zero date? mySQL stores dates in UTC and converts them from local coming in and to local going out. – O. Jones Jun 05 '12 at 15:20

2 Answers2

3

If you can alter the database, add the nullable attribute on your column and replace 0000-00-00 values with null.

Else, you need to implement a custom type: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/advanced-field-value-conversion-using-custom-mapping-types.html

Maxence
  • 12,868
  • 5
  • 57
  • 69
2

I found the solution to my problem. I had the annotations set to date on the attributes in the entities, so I decided to remove the annotations in order to PHP handle them. Now it's working since it is queried as a string type and not date type. I guess this is what I get coming from the static world vs. the dynamic world.

robasc
  • 307
  • 1
  • 5
  • 15