Sunday, 15 January 2012

Is there a MySQL setting to ignore "time" portion of datetime constant when applied to "DATE" column? -


Disclaimer: This is an old and ugly codebase that I do not touch too much.

  • There is a table in the database that has a column of type (between many other people) at DATE .
  • There is a query (Auto-generated generated from the torque criteria) with the java.util.Date parameter; The torque formats it as a whole datetime.

So, the query is getting MySQL, something like this:

  SELECT * my_table from WHERE my_date = '20091201105320'; // 2009-12-01 10:53:20  

The problem is that the server that has MySQL 5.0.27-standard works just fine - as all records of returns From my_table to my_date is set to '2009-12-01'. On another server on which MySQL 5.0.45 does not work, the data is there - If I manually run the query after triming the part of the time then I get the right result.

The question is:

Is there a setting, either MySQL in the configuration file or per-session variable (connection string) or elsewhere to MySQL continuous time conversion During time forced to ignore part?

I can not explicitly use DATE () function changes without code which does not make me too much

Apparently I came out of luck Naseeb. From:

Before MySQL 5.0.42, when the DATE value is compared with the value of DATETIME , then DATETIME Price is ignored, or comparison can be compared to string starting with MySQL 5.0.42, a DATE value as DATETIME type format Adding time part in '00: 00: 00' .

  1. Anyone should meet someone in such a situation, I found that there is only one solution:

    1. Time zones (hours / Min / sec) Java.util.Date for example zero.
    2. Patch (and I mean "patch" - there is no way to plug in your adapter) getDateString () MySQL for the DB adapter of tarak ( org. Apache.torque.adapter.DBMM ) to verify that the time zone is zero and, if so, then use the "date-only" format.

No comments:

Post a Comment