Here is a report from Martin:

The issue is that if I call getDate() on a result set for a column that is a DATETIME type, I get the wrong result.

I am running in pure java mode to try to track down the issue. The behaviour is different, but also wrong, in native mode, and I suspect the cause is similar.

I haven’t spent long enough looking at it to be certain what’s going on, but in RS.java...

public Date getDate(int col, Calendar cal) throws SQLException {

... cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col))); ... }

...the argument to Calendar.setTimeInMillis should be the number of milliseconds since the epoch date. However, the code calls column_long in NestedDB.class...

@Override
synchronized long column_long(long stmt, int col) throws SQLException {

... return Long.parseLong(column_text(stmt, col)); ... }

... which calls column_text() and parses it as a long. For a DATETIME type, column_text returns a string representation of the date, e.g. “20110816 16:56:42”, and the results will be garbage. It would only work if the column contained the date in the format of milliseconds since epoch in numerical or string format, and can’t work for DATETIME types, which I think must be the intention?

I believe your issue is the fact that SQLite does not specify a storage type for dates, and instead lets you store a date as either text ("yyyy-MM-dd HH:mm:ss"), integer (this is supposed to be seconds from the UNIX epoch by SQLite standards, but the JDBC driver stores it as milliseconds instead), or real (Julian date stored in decimal format).

See the "Date and Time Datatype" section on this page: http://www.sqlite.org/datatype3.html

As I said earlier, the JDBC driver chooses to store dates as milliseconds from the UNIX epoch date (January 1, 1970), and this is also the only date format that it can read. For example, if you're creating dates with SQLite's DATETIME function, it will return a string. If you store this result in your database, it will be stored as a text, and the JDBC driver cannot read it. Similarly the JULIANDAY function will return a real, and the driver cannot read those. Rails uses this same JDBC driver under the hood for SQLite connectivity, but it also stores dates as strings rather than integers. This driver is unable to read those dates either. Since it does not comply with the SQLite standard of storing dates as seconds, it would not be able to read anything storing dates in that manner, either. Basically, the only dates that it can read are the ones that it creates itself, and most other tools won't be able to read those.

In our case, we were having issues making our Java app coexist with Rails and DDL files that we were creating. So, I've download the source code for v.1.7.2 and modified the driver to do the following: Out of the box, it will read dates stored as any of the following types based on the data type that it's stored as: 1) INTEGER - Read as milliseconds from the UNIX epoch. 2) TEXT - Parsed as "yyyy-MM-dd HH:mm:ss" 3) REAL - Julian dates

By default it will still store dates as milliseconds, but you can specify properties to override this. I've added the following properties: date_integer_precision: values: "seconds" : Read and store integer dates as seconds from the Unix Epoch (SQLite standard). "milliseconds" : (DEFAULT) Read and store integer dates as milliseconds from the Unix Epoch (Java standard).

date_storage_class: values: "integer" : (DEFAULT) store dates as number of seconds or milliseconds from the Unix Epoch. "text" : store dates as a string of text. "real" : store dates as Julian Dates.

date_string_format: Format to store and retrieve dates stored as text. Defaults to "yyyy-MM-dd HH:mm:ss" (SQLite standard)

I'm submitting a patch with the above mentioned changes. I hope the developers can use it and add these features to the code base. This is based on the 3.7.2 code branch, not the trunk.

Thanks, Scott Nelson

Moved to bitbucket: https://bitbucket.org/xerial/sqlite-jdbc/issue/23

Fixed by https://bitbucket.org/gbatumbya/xerial-sqlite-jdbc/commits/bf677755b00908b1b70fa4ada8210bd924ef8e1b