handling DATETIME values 0000-00-00 00 00 00 in JDBC

Question

I get an exception  see below  if I try to do   resultset getString  add date      for a JDBC connection to a MySQL database containing a DATETIME value of 0000-00-00 00 00 00  the quasi-null value for DATETIME   even though I m just trying to get the value as string  not as an object   I got around this by doing   SELECT CAST add date AS CHAR  as add date   which works  but seems silly    is there a better way to do this   My point is that I just want the raw DATETIME string  so I can parse it myself as is   note  here s where the 0000 comes in   from http   dev mysql com doc refman 5 0 en datetime html      Illegal DATETIME  DATE  or TIMESTAMP   values are converted to the    zero      value of the appropriate type     0000-00-00 00 00 00  or    0000-00-00      The specific exception is this one   SQLException  Cannot convert value  0000-00-00 00 00 00  from column 5 to TIMESTAMP  SQLState  S1009 VendorError  0 java sql SQLException  Cannot convert value  0000-00-00 00 00 00  from column 5 to TIMESTAMP      at com mysql jdbc SQLError createSQLException SQLError java 1055      at com mysql jdbc SQLError createSQLException SQLError java 956      at com mysql jdbc SQLError createSQLException SQLError java 926      at com mysql jdbc ResultSetImpl getTimestampFromString ResultSetImpl java 6343      at com mysql jdbc ResultSetImpl getStringInternal ResultSetImpl java 5670      at com mysql jdbc ResultSetImpl getString ResultSetImpl java 5491      at com mysql jdbc ResultSetImpl getString ResultSetImpl java 5531

User · Answer

I wrestled with this problem and implemented the 'convertToNull' solutions discussed above. It worked in my local MySql instance. But when I deployed my Play/Scala app to Heroku it no longer would work. Heroku also concatenates several args to the DB URL that they provide users, and this solution, because of Heroku's use concatenation of "?" before their own set of args, will not work. However I found a different solution which seems to work equally well.

SET sql_mode = 'NO_ZERO_DATE';

I put this in my table descriptions and it solved the problem of '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

User · Answer

I solved the problem considerating  00-00-      isn t a valid date  then  I changed my SQL column definition adding  NULL  expresion to permit null values   SELECT  -- Tabla item pedido   CREATE TABLE item pedido       id INTEGER AUTO INCREMENT PRIMARY KEY      id pedido INTEGER      id item carta INTEGER      observacion VARCHAR 64       fecha estimada TIMESTAMP      fecha entrega TIMESTAMP NULL     HERE IS     NULL   DELIVERY DATE NOT SET YET     CONSTRAINT fk item pedido id pedido FOREIGN KEY  id pedido          REFERENCES pedido id        Then  I ve to be able to insert NULL values  that means  I didnt register that timestamp yet      SELECT     INSERT item pedido   INSERT INTO item pedido VALUES  01  01  01   Ninguna   ADDDATE  HOY  INTERVAL 5 MINUTE   NULL    02  01  02   Ninguna   ADDDATE  HOY  INTERVAL 3 MINUTE   NULL        The table look that   mysql gt  select   from item pedido   ---- ----------- --------------- ------------- --------------------- ---------------------    id   id pedido   id item carta   observacion   fecha estimada        fecha entrega          ---- ----------- --------------- ------------- --------------------- ---------------------     1           1               1   Ninguna       2013-05-19 15 09 48   NULL                     2           1               2   Ninguna       2013-05-19 15 07 48   NULL                     3           1               3   Ninguna       2013-05-19 15 24 48   NULL                     4           1               6   Ninguna       2013-05-19 15 06 48   NULL                     5           2               4   Suave         2013-05-19 15 07 48   2013-05-19 15 09 48      6           2               5   Seco          2013-05-19 15 07 48   2013-05-19 15 12 48      7           3               5   Con Mayo      2013-05-19 14 54 48   NULL                     8           3               6   Bilz          2013-05-19 14 57 48   NULL                   ---- ----------- --------------- ------------- --------------------- ---------------------  8 rows in set  0 00 sec    Finally  JPA in action    Stateless  LocalBean public class PedidosServices        PersistenceContext unitName  vagonpubPU       private EntityManager em       private Logger log   Logger getLogger PedidosServices class getName           SuppressWarnings  unchecked       public List lt ItemPedido gt  obtenerPedidosRetrasados             log info  Obteniendo listado de pedidos retrasados            Query qry   em createQuery  SELECT ip FROM ItemPedido ip  Pedido p WHERE                      ip fechaEntrega NULL                      AND ip idPedido p id                      AND ip fechaEstimada  lt   arg3                      AND  p idTipoEstado  arg0 OR p idTipoEstado  arg1 OR p idTipoEstado  arg2             qry setParameter  arg0   Tipo ESTADO BOUCHER ESPERA PAGO           qry setParameter  arg1   Tipo ESTADO BOUCHER EN SERVICIO           qry setParameter  arg2   Tipo ESTADO BOUCHER RECIBIDO           qry setParameter  arg3   new Date              return qry getResultList            At last all its work  I hope that help you

User · Answer

you can append the jdbc url with   zeroDateTimeBehavior convertToNull amp autoReconnect true amp characterEncoding UTF-8 amp characterSetResults UTF-8   With the help of this  sql convert  0000-00-00 00 00 00   as null value   eg   jdbc mysql  lt host-name gt   lt db-name gt  zeroDateTimeBehavior convertToNull amp autoReconnect true amp characterEncoding UTF-8 amp characterSetResults UTF-8

User · Answer

If  after adding lines     lt property name  hibernate connection zeroDateTimeBehavior  gt convertToNull lt  property gt   hibernate connection zeroDateTimeBehavior convertToNull   lt connection-property name  zeroDateTimeBehavior  gt convertToNull lt  connection-property gt    continues to be an error   Illegal DATETIME  DATE  or TIMESTAMP values are converted to the    zero    value of the appropriate type   0000-00-00 00 00 00  or  0000-00-00      find lines   1  resultSet getTime  time       time   00 00 00 2  resultSet getTimestamp  timestamp       timestamp   00000000000000 3  resultSet getDate  date       date   0000-00-00 00 00 00   replace with the following lines  respectively   1  Time valueOf resultSet getString  time     2  Timestamp valueOf resultSet getString  timestamp     3  Date valueOf resultSet getString  date

User · Answer

Alternative answer  you can use this JDBC URL directly in your datasource configuration   jdbc mysql   yourserver 3306 yourdatabase zeroDateTimeBehavior convertToNull   Edit   Source  MySQL Manual     Datetimes with all-zero components  0000-00-00          These values can not be represented  reliably in Java  Connector J 3 0 x always converted them to NULL when being read from a  ResultSet       Connector J 3 1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards  This behavior can be modified using the zeroDateTimeBehavior configuration property  The allowable values are          exception  the default   which throws an SQLException with an SQLState of S1009    convertToNull  which returns NULL instead of the date    round  which rounds the date to the nearest closest value which is 0001-01-01       Update  Alexander reported a bug affecting mysql-connector-5 1 15 on that feature  See CHANGELOGS on the official website

User · Answer

DATE FORMAT column name    Y- m- d  T   as dtime  Use this to avoid the error  It return the date in string format and then you can get it as a string  resultset getString  quot dtime quot     This actually does NOT work   Even though you call getString  Internally mysql still tries to convert it to  date first   at com mysql jdbc ResultSetImpl getDateFromString ResultSetImpl java 2270    mysql-connector-java-5 1 15 jar na  at com mysql jdbc ResultSetImpl getStringInternal ResultSetImpl java 5743    mysql-connector-java-5 1 15 jar na  at com mysql jdbc ResultSetImpl getString ResultSetImpl java 5576    mysql-connector-java-5 1 15 jar na

User · Answer

My point is that I just want the raw DATETIME string  so I can parse it myself as is   That makes me think that your  quot workaround quot  is not a workaround  but in fact the only way to get the value from the database into your code  SELECT CAST add date AS CHAR  as add date  By the way  some more notes from the MySQL documentation  MySQL Constraints on Invalid Data   Before MySQL 5 0 2  MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry  In MySQL 5 0 2 and up  that remains the default behavior  but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur       If you try to store NULL into a column that doesn t take NULL values  an error occurs for single-row INSERT statements  For multiple-row INSERT statements or for INSERT INTO     SELECT statements  MySQL Server stores the implicit default value for the column data type   MySQL 5 x Date and Time Types   MySQL also allows you to store  0000-00-00  as a    dummy date     if you are not using the NO ZERO DATE SQL mode   This is in some cases more convenient  and uses less data and index space  than using NULL values       By default  when MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type  as described at the beginning of this section   it converts the value to the    zero    value for that type

User · Answer

I suggest you use null to represent a null value   What is the exception you get   BTW    There is no year called 0 or 0000   Though some dates allow this year   And there is no 0 month of the year or 0 day of the month   Which may be the cause of your problem

User · Answer

To add to the other answers  If yout want the 0000-00-00 string  you can use noDatetimeStringSync true  with the caveat of sacrificing timezone conversion    The official MySQL bug  https   bugs mysql com bug php id 47108   Also  for history  JDBC used to return NULL for 0000-00-00 dates but now return an exception by default  Source

User · Answer

I stumbled across this attempting to solve the same issue   The installation I am working with uses JBOSS and Hibernate  so I had to do this a different way   For the basic case  you should be able to add zeroDateTimeBehavior convertToNull to your connection URI as per this configuration properties page   I found other suggestions across the land referring to putting that parameter in your hibernate config   In hibernate cfg xml    lt property name  hibernate connection zeroDateTimeBehavior  gt convertToNull lt  property gt    In hibernate properties   hibernate connection zeroDateTimeBehavior convertToNull   But I had to put it in my mysql-ds xml file for JBOSS as    lt connection-property name  zeroDateTimeBehavior  gt convertToNull lt  connection-property gt    Hope this helps someone

[java] handling DATETIME values 0000-00-00 00:00:00 in JDBC

Examples related to java

Examples related to sql

Examples related to date

Examples related to jdbc