Timezones and daylight savings time

When using JDBC to query TimeTZ and TimestampTZ values, the JVM and session timezones must match to get accurate results.

When building Java applications that query TimeTZ and TimestampTZ values, the JVM and session must use the same timezone to get accurate results. In particular, the CURRENT_TIME and CURRENT_TIMESTAMP functions can return different values if the following conditions are met:

  • The JVM and session timezones are different
  • One of the timezones is one where daylight savings time is in effect

The following example demonstrates how to query for the current timestamp:

  1. The run() method sets the JVM timezone to US/Eastern.
  2. US/Eastern is then passed to the getConnection() method, which sets the session timezone.
  3. The printCurrentTimeAndTimeStamp() method executes the CURRENT_TIME and CURRENT_TIMESTAMP functions, which return TimeTZ and TimestampTZ, respectively. These values should match because the JVM and session use the same timezone.
private Connection getConnection(String timezone) throws SQLException {
    final String host = "host";
    final String port = "5433";
    final String dbName = "database";
    Properties jdbcOptions = new Properties();
    jdbcOptions.put("User", "Your Username");
    jdbcOptions.put("Password", "Your Password");
 
    // Use the ConnSettings connection property to ensure the session's timezone
    // matches the JVM's timezone
    jdbcOptions.put("ConnSettings", "SET TIMEZONE TO '" + timezone + "'");
 
    return DriverManager.getConnection(
            "jdbc:vertica://" + host + ":" + port + "/" + dbName, jdbcOptions);
}
 
private void printCurrentTimeAndTimeStamp(Connection conn) throws SQLException
{
    Statement st = conn.createStatement();
    String queryString = "SELECT CURRENT_TIME(0) AS time , CURRENT_TIMESTAMP(0) AS timestamp";
    ResultSet rs = st.executeQuery(queryString);
    rs.next();
    String timeValue = rs.getString("time");
    String timestampValue = rs.getString("timestamp");
    System.out.println("CURRENT_TIME(): " + timeValue);
    System.out.println("CURRENT_TIMESTAMP(): " + timestampValue);
}
 
public void run() throws SQLException
{
    final String timezone = "US/Eastern";
    // set JVM timezone
    TimeZone.setDefault(TimeZone.getTimeZone(timezone));
    Connection conn = getConnection(timezone);
    try {
       printCurrentTimeAndTimeStamp(conn);
    } finally {
        conn.close();
    }
}