0

I'm trying to update a timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' field in the database to "0000-00-00 00:00:00" in certain circumstances in a Java app. Another team set up this database structure that I'm trying to work with.

I inherited an application that uses an H2 database to run tests, but Mysql database in production. I have found ways to make it work when I run it against a mysql DB, but not for the H2 DB. So all my tests keep failing.

I tried setting it to null, but that just gives an error that the field can't be null. I found that I could use ts '0000-00-00 00:00:00' for mysql but that doesn't work in H2. Same thing with timestamp(0000-00-00 00:00:00).

Does anyone know if this is possible, and how I can update this field for both database systems? Or do I need to go back to the other team and ask for some sort of redesign?

EDIT I haven't found a technical solution to this. I wasn't able to convince the other team to go with NULL as suggested, but they did agree to '1970-01-01 00:00:01', which is a valid date in both H2 and MYSQL.

britter
  • 141
  • 2
  • 11
  • 2
    My suggestion is to move away from storing `0000-00-00 00:00:00` in your database tables. Instead, just use `NULL` as a placeholder. – Tim Biegeleisen Feb 19 '19 at 13:57
  • It is good idea to modify the column to allow null value, other wise it might create problem when you again try to change db. – Alpesh Jikadra Feb 19 '19 at 14:27
  • From MySQL 5.6 allows to insert 0 and it will be converted to '0000-00-00 00:00:00'. But, I don't know if H2 will accept it. – abestrad Feb 19 '19 at 14:33
  • Possibly partially a duplicate of [#1292 - Incorrect date value: '0000-00-00' [duplicate\]](https://stackoverflow.com/questions/37292628/1292-incorrect-date-value-0000-00-00) – Ole V.V. Feb 19 '19 at 14:41

0 Answers0