getDate() returns wrong values

[GoogleCodeExporter]

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?

Original issue reported on code.google.com by l...@xerial.org on 25 Oct 2011 at 2:09

[GoogleCodeExporter]

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

Original comment by scott80@gmail.com on 24 Jan 2012 at 9:26

Attachments:

• sqlite-jdbc-custom-date-types.diff

[GoogleCodeExporter]

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

Original comment by Grace.Ba...@gmail.com on 12 Sep 2012 at 3:01

[GoogleCodeExporter]

Fixed by 
https://bitbucket.org/gbatumbya/xerial-sqlite-jdbc/commits/bf677755b00908b1b70fa
4ada8210bd924ef8e1b

Original comment by Grace.Ba...@gmail.com on 13 Apr 2013 at 6:56

• Changed state: Fixed