JDBC driver unable to parse ISO 8601 dates

[lnreeder]

The https://www.sqlite.org/datatype3.html indicates date/time functions in SQLite can store dates as: "TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). "

However, when storing an ISO 8601 string like 2016-01-01 00:00:00, then attempting to use sqlite-jdbc to retrieve as a java Timestamp, ResultSet.getTimestamp returns an error:

testSelectIso8601Timestamp(org.sqlite.ResultSetTest) Time elapsed: 0.003 sec <<< ERROR!
java.sql.SQLException: Error parsing time stamp
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:532)
at org.sqlite.ResultSetTest.testSelectIso8601Timestamp(ResultSetTest.java:156)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)
Caused by: java.text.ParseException: Unparseable date: "2016-01-01 00:00:00" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)
at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:529)
... 32 more

Reproduction test class is attached. See test "testSelectIso8601Timestamp"

ResultSetTest.java.txt

[kaiwinter]

You have two options here

1. Use the expected format: 2016-01-01 00:00:00.000
2. Set a different value for the pragma date_string_format:

SQLiteConfig sqLiteConfig = new SQLiteConfig();
Properties properties = sqLiteConfig.toProperties();
properties.setProperty(Pragma.DATE_STRING_FORMAT.pragmaName, "yyyy-MM-dd HH:mm:ss");
conn = DriverManager.getConnection("jdbc:sqlite:", properties);

I don't know if this is a bug. One could think the value should be converted from 2016-01-01 00:00:00 to 2016-01-01 00:00:00.000 before saving (because of the date_string_format) but I have no idea how this would harm the amount of disk space which is necessary to save this additional information.

The bad thing here is that you can save a value which you can't load anymore. This is because the pattern which is used to parse the timestamp from the database is determined by the date_string_format which doesn't fit to the original saved value.

[bendem]

This also happens when using current_timestamp.
Example:

create table users (
  id integer primary key autoincrement not null,
  email varchar(255) unique not null,
  password char(60) not null,
  created_at timestamp default current_timestamp not null
);
insert into users (email, password) values ('x@x.com', 'bcrypted password');
select * from users order by created_at; -- rs.getTimestamp(4) throws

Caused by: java.sql.SQLException: Error parsing time stamp
    at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:526)
    at be.bendem.sqlstreams.impl.SqlBindings.lambda$map$1(SqlBindings.java:38)
    at be.bendem.sqlstreams.impl.Wrap.get(Wrap.java:27)
    ... 56 more
Caused by: java.text.ParseException: Unparseable date: "2016-04-24 10:39:36" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)
    at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
    at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
    at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:523)
    ... 58 more

EDIT: Same problem happens with date and current_date instead of timestamp and current_timestamp:

Caused by: java.sql.SQLException: Error parsing date
    at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:290)
    at be.bendem.sqlstreams.impl.SqlBindings.lambda$map$1(SqlBindings.java:38)
    at be.bendem.sqlstreams.impl.Wrap.get(Wrap.java:27)
    ... 56 more
Caused by: java.text.ParseException: Unparseable date: "2016-04-24" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)
    at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
    at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
    at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:287)
    ... 58 more

[bendem]

Running into this again, it makes using dates with this driver such a pain. Would be nice if it was fixed.

I know it might sounds crazy, but I kinda expect this driver to be able to retrieve the dates and timestamp it generated.

[TuomasKiviaho]

You have two options here

1. Use the expected format: 2016-01-01 00:00:00.000
2. Set a different value for the pragma date_string_format:

Actually there is a third option in a form of a pull request #33 that I'm using. I just wonder why it never got applied.

[yukido]

Ran into this just today.

Seconding @bendem here, would love to see this fixed and the driver to be able to parse all possible sqlite3 dates.

[xerial]

@TuomasKiviaho Some test cases are failing in #33, but it never get fixed by the author.

[TuomasKiviaho]

@xerial There was an explanation in the ticket that the the test is not conforming with standard so by fixing the following method, I suppose it would start working. There is also a quite thorough explanation what is wrong with it.

    @Test
    public void dateTimeTest() throws Exception {
        Connection conn = getConnection();

        conn.createStatement().execute("create table sample (start_time datetime)");

        Date now = new Date();
        String date = FastDateFormat.getInstance(SQLiteConfig.DEFAULT_DATE_STRING_FORMAT).format(now);

        conn.createStatement().execute("insert into sample values(" + now.getTime() + ")");
        conn.createStatement().execute("insert into sample values('" + date + "')");

        ResultSet rs = conn.createStatement().executeQuery("select * from sample");
        assertTrue(rs.next());
        assertEquals(now, rs.getDate(1));
        assertTrue(rs.next());
        assertEquals(now, rs.getDate(1));

        PreparedStatement stmt = conn.prepareStatement("insert into sample values(?)");
        stmt.setDate(1, new java.sql.Date(now.getTime()));
    }

[danap]

Hello,

I closed issue #24, because I thought the issue had been address and I did not have additional
time to review the failing tests in pull #33.

After testing today, 3.19.3, and reviewing this issue, it is still present. The main problem appears to
be on the storing of java.sql.Time, Date, and Timestamp in a java.util.Date numeric value in the
database. That approach in my opinion is perhaps incorrect. It is fine for a Timestamp, but TIME, DATE, DATETIME, have the formats (YYYY-mm-dd), (HH:mm:ss), and (YYYY-mm-dd HH:mm:ss), String literals.

As pointed out a change now will break all databases which are storing these types as numeric
if changed to strings. The orginal pull #33 was a patch. I never had or do now have the knowledge
of how DBs store these types typically, this seems to be an architecture issue for the driver in
order to be properly fixed.

I have created an additional test to today that shows an error with the SQL Statement execution
for the problem, Prepare Statements work, but again the wrong data numeric, seems present
instead of strings being stored.

https://dandymadeproductions.com/temp/SQLite_JDBC_Temporal.java

danap.
Ajqvue Project Manager.

[bendem]

As pointed out a change now will break all databases which are incorrectly storing the thesetemporal values, beside Timestamp. I believe these other SQL types should be stored asthere correct represented STRINGS to make the SQLite Database compatible with other DBsand tools in accessing the data.

This doesn't have to be the case, the fix would be to add support for a *new* format. That change shouldn't affect the parsing of existing dates and times.

[danap]

This doesn't have to be the case, the fix would be to add support
for a new format. That change shouldn't affect the parsing
of existing dates and times.
bendem

I would agree. I would assume that the storing of these types,
TIME, DATE, DATETIME, and TIMESTAMP in Long values was chosen
for a purpose, efficiency, limit space in the database, etc...

If that would be to be maintained then INSERTS as demonstrated
in my test case, strings, should still function properly when
the data is retrieved with either getTime(), getDate(), etc,
but also getString(). Also pointed out at start of this issue.

Xerial,

The other reason I did not review the test cases that were
breaking with pull #33 is because I did not get a clear confirmation
that the pull would be even accepted. I had already spent a
week reviewing the code and putting together a patch that might
not have been the proper approach/solution.

I'm trying too do a complete review of my project's support of
databases, and I use this sqlite-jdbc driver. At this point I
really think my test case points to a serious deficiency in this
driver that has still not been fixed. I hate to think that I
would again spend time, weeks understanding the code and putting
a patch together for this project without better communication.

#33

danap.

[dmmax]

Someone try to resolve problem? Or maybe have alternative options for getting and saving date in database via SQLite JDBC.

[takacsot]

It is still a valid issue.

I could accept the fact that we should use the proper timestamp format...

BUT

when using built in functions like current_timestamp or datetime('now') and I could not read it back without "hacking" is not something what could be considered as a solution/answer.

Could anyone sum up what is really missing to fix it?
I am considering to be involved and help in any implementation concerns.

Implementation point of view I could imagine a solution like commons lang DateUtil.parseDate.

[ArthurBorsboom]

Unparseable date: "1962-02-18 00:00:00" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)

(milliseconds are missing)

+1 for getting this fixed, somehow.

[davidjpfeiffer]

Totally agree with all the previous comments.

It is dumb that the SQLite Driver is unable to parse date strings created by SQLite.

I think I found the issue: the regex used to parse the date strings only matches if the string contains millisecond information. Apparently those created by CURRENT_TIMESTAMP do not include millisecond information, which was causing the regex to not match the date string, which was preventing the date string from getting parsed as a Date.

I submitted a pull request to try and resolve the issue. Basically I added millisecond information to the date string if it did not already have it. The "millisecond information" I added was just zeros, which clearly does not change the date represented by the date string, so this seems like a safe solution to me.

Hopefully someone accepts my pull request and this bug can finally be resolved.

[davidjpfeiffer]

The fix has been merged! If you are still experiencing this issue try updating your version of sqlite-jdbc

[ArthurBorsboom]

Hi David,

Now I have to fix my workaround. ;-)
Thanks for your efforts. +1

[xerial]

I'll release 3.36.0.1 today.