Processing DATETIME 0000-00-00 00:00:00 in JDBC

Question

I get an exception (see below) if I try to do

resultset.getString("add_date"); 

to connect a JDBC to a MySQL database containing the DATETIME 0000-00-00 value 00:00:00 (the quasi-beam value for DATETIME), although I'm just trying to get the value as a 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?

I want to say that I just need a DATETIME string, so I can analyze it myself as is .

note: here where 0000 is: (from http://dev.mysql.com/doc/refman/5.0/en/datetime.html )

Invalid DATETIME, DATE, or TIMESTAMP values ​​are converted to a "zero" value of the corresponding type ('0000-00-00 00:00:00' or '0000-00-00').

A specific exception is the following:

 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) 

Answer 1

I stumbled upon this while trying to solve the same problem. The installation I'm working with uses JBOSS and Hibernate, so I had to do it differently. In the base case, you should add zeroDateTimeBehavior=convertToNull to your connection URI according to this configuration property page .

I found other suggestions all over the world, citing the inclusion of this parameter in the hibernate configuration:

In hibernate.cfg.xml:

 <property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property> 

In hibernate.properties:

 hibernate.connection.zeroDateTimeBehavior=convertToNull 

But I had to put it in my mysql-ds.xml file for JBOSS as:

 <connection-property name="zeroDateTimeBehavior">convertToNull</connection-property> 

Hope this helps someone. :)

Answer 2

Alternative answer, you can use this JDBC URL directly in your data source configuration:

 jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull 

Edit:

Source: MySQL Guide

Datetimes with all-zero components (0000-00-00 ...) - These values ​​cannot be reliably represented in Java. Connector / J 3.0.x always converted them to NULL when reading from a ResultSet.

The / J 3.1 connector throws a default exception when these values ​​are encountered, as this is the most appropriate behavior in accordance with the JDBC and SQL standards. This behavior can be changed using the zeroDateTimeBehavior configuration property. Valid Values:

• exception (default) that throws a SQLException with SQLState from S1009.
• convertToNull , which returns NULL instead of a date.
• round , which rounds the date to the nearest nearest value, which is 0001-01-01.

Update: Alexander reported an error affecting mysql-connector-5.1.15 on this function. See CHANGELOGS on the official website .

Answer 3

My point is that I just need a DATETIME string, so I can parse it as it is.

This makes me think that your “workaround” is not a workaround, but actually 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, a few more notes from the MySQL documentation:

MySQL Invalid Data Limitations :

Prior to MySQL 5.0.2, MySQL forgives illegal or incorrect data values ​​and forces them to legal values ​​for data entry. In MySQL 5.0.2 and later, this remains the default, but you can change the SQL server mode to select a more traditional treatment of bad values, so that the server rejects them and interrupts the statement in which they occur.

[..]

If you try to save NULL in a column that is not NULL, an error occurs for single-line INSERT statements. For multiple INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores an 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" (unless you use 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 a null value for that type.

Answer 4

 DATE_FORMAT(column name, '%Y-%m-%d %T') as dtime 

Use this to avoid mistakes. It returns the date in string format, and then you can get it as a string.

 resultset.getString("dtime"); 

It actually does NOT work. Even if you call getString. Inside mysql is still trying to convert it to a date first.

in 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] in com.mysql.jdbc.ResultSetImpl.getString (ResultSetImpl.java:5576)

~ [MySQL-socket-Java-5.1.15.jar: on]

Answer 5

If after adding the lines:

 <property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property> hibernate.connection.zeroDateTimeBehavior=convertToNull <connection-property name="zeroDateTimeBehavior">convertToNull</connection-property> 

continues to be a mistake:

 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 strings:

 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 the following lines accordingly:

 1) Time.valueOf(resultSet.getString("time")); 2) Timestamp.valueOf(resultSet.getString("timestamp")); 3) Date.valueOf(resultSet.getString("date")); 

Answer 6

I struggled with this problem and implemented the "convertToNull" solutions discussed above. It worked on my local instance of MySql. But when I deployed the Play / Scala app for Heroku, it will no longer work. Heroku also combines several arguments with the DB URL that they provide to users, and this solution because Heroku uses the "?" Concatenation until their own set of arguments will work. However, I found another solution that seems to work equally well.

SET sql_mode = 'NO_ZERO_DATE';

I put this in the descriptions of my tables, and he solved the problem "0000-00-00 00:00:00" cannot be represented as java.sql.Timestamp

Answer 7

I suggest you use null to represent a null value.

What exception do you get?

BTW:

There is no year called 0 or 0000. (Although some dates are allowed this year)

And there is no 0 month of the year or 0 day of the month. (This may be the cause of your problem)

Answer 8

I decided that the problem with '00 -00 -.... 'is not a valid date, then I changed the definition of the SQL column by adding the expression “NULL” to allow 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 should be able to insert NULL values, which means that "I have not registered this 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 looks like this:

 mysql> 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<ItemPedido> 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 < :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(); } 

Finally, all his work. Hope this helps you.

Answer 9

To add to the other answers: If you need a string of 0000-00-00 , you can use noDatetimeStringSync=true (with caution, sacrifice the time zone).

MySQL official error: https://bugs.mysql.com/bug.php?id=47108 .

Also, for history, JDBC is used to return NULL for 0000-00-00 dates, but now returns a default exception. Source

Answer 10

you can add jdbc url with

 ?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8 

With this, sql convert '0000-00-00 00:00:00' as a null value.

eg:

 jdbc:mysql:<host-name>/<db-name>?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8