Honoring Java timezone settings

[aaime]

While working on sqlite queries with dates having an explicit timezone, and with a desire to set the timezone programmatically in Java (to ensure tests can run successfully no matter who's executing them), we found that the driver seems to ignore the Java TimeZone settings, but reacts to a TZ environment variable.

Would it be possible to make the JDBC driver code account for the Java timezone instead?
I'm guessing it's an issue with the underling native SQL engine, but want to make sure.

[gotson]

we found that the driver seems to ignore the Java TimeZone settings, but reacts to a TZ environment variable.

can you expand on what you tried maybe?

[pmauduit]

can you expand on what you tried maybe?

Here is a modified Sample.java class which isolates one of the queries being performed in our testsuite:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.TimeZone;

public class Sample
{

  public static void main(String[] args)
  {
    // This is ignored by the driver, but TZ=GMT env variable will do
    TimeZone.setDefault(TimeZone.getTimeZone("GMT"));

    try
    (
      // wget -O ne.gpkg https://github.com/opengeospatial/ogcapi-features/raw/refs/heads/master/cql2/standard/data/ne110m4cql2.gpkg
      Connection connection = DriverManager.getConnection("jdbc:sqlite:ne.gpkg");
      Statement statement = connection.createStatement();
    )
    {
      ResultSet rs = statement.executeQuery("SELECT count(*) AS count FROM \"ne_110m_populated_places_simple\" WHERE (datetime(\"start\",'utc' ) = datetime('2022-04-16T10:13:19Z','utc' ) AND datetime(\"start\",'utc' ) IS NOT NULL )");
      while(rs.next())
      {
        System.out.println("count = " + rs.getString("count"));
      }
    }
    catch(SQLException e)
    {
      e.printStackTrace(System.err);
    }
  }
}

Compiling it, then running it does not give the same result depending on the TZ environment variable value:

% TZ=GMT java -classpath ".:sqlite-jdbc-3.47.0.0.jar" Sample
count = 1

vs:

% java -classpath ".:sqlite-jdbc-3.47.0.0.jar" Sample
count = 0

Notes:

• TZ is unset in my environment in the case of the second run
• if I replace TimeZone.setDefault to set to "Europe/Paris", we get the same results as above.

Setting environment variables at runtime in Java is quite complicated as the System class does not give write access to the existing environment, and I am not sure on how it would be passed to loaded native libraries afterwards anyway, so we were wondering if the driver could not use the TimeZone.default instead, which would offer the possibility to be modified at runtime.

[gotson]

In the sample your give there is no date/time handling from Java, the only functions are sqlite native datetime functions. And since your result set returns a count, i don't see how the driver could make the results any different.

[pmauduit]

In the sample your give there is no date/time handling from Java, the only functions are sqlite native datetime functions

As our code runs on the JVM, we were expecting that the TimeZone.setDefault() would have an impact, as the TZ environment variable has, on the number of results returned by our query and would return the same results, so we were confused. We imagined that the driver could react differently depending on the timezone configured java-side.

Nevermind, we could probably add a bit of documentation on our side to disambiguate / explain the behaviour.

[gotson]

I'm not saying the driver can't be enhanced, but in the sample you showed there's literally nothing happening in the Java code of the driver, thus i'm confused as to why changing the TZ environment variable does have an effect at all.

I haven't found anything about the TZ environment variable in SQLite itself though.

[gotson]

I just did some tests with sqlite directly, it reacts to the TZ timezone.

> TZ=GMT sqlite3 ne.sqlite
sqlite> select 
t.start,
datetime(t.start,'utc' )
from ne_110m_populated_places_simple as t
where t.start is not null;
+---------------------+--------------------------+
|        start        | datetime(t.start,'utc' ) |
+---------------------+--------------------------+
| 2021-04-16T10:15:59 | 2021-04-16 10:15:59      |
| 2022-04-16T10:13:19 | 2022-04-16 10:13:19      |
| 2022-04-16T10:15:10 | 2022-04-16 10:15:10      |
+---------------------+--------------------------+

sqlite> select 
t.start,
datetime(t.start,'utc' )
from ne_110m_populated_places_simple as t
where t.start is not null;
+---------------------+--------------------------+
|        start        | datetime(t.start,'utc' ) |
+---------------------+--------------------------+
| 2021-04-16T10:15:59 | 2021-04-16 02:15:59      |
| 2022-04-16T10:13:19 | 2022-04-16 02:13:19      |
| 2022-04-16T10:15:10 | 2022-04-16 02:15:10      |
+---------------------+--------------------------+

See how the second column is different.

From the SQLite documentation:

The "utc" modifier is the opposite of "localtime". "utc" assumes that the time-value to its left is in the local timezone and adjusts that time-value to be in UTC. If the time to the left is not in localtime, then the result of "utc" is undefined.