Using Timestamps and Time Zones

There are two timestamp types in NuoDB:

  • TIMESTAMP

  • TIMESTAMP WITHOUT TIME ZONE

The TIMESTAMP data type represents a date and time aware of time zone. Its date and time always remain constant in the 'UTC' time zone, and it may display different date and time when viewed in time zones with different offsets.

The TIMESTAMP WITHOUT TIME ZONE data type represents a date and time in no specific time zone, as defined in the SQL standard. The same date and time is observed when viewed in any time zone.

There are circumstances that require the TIMESTAMP WITHOUT TIME ZONE to be associated with the local time zone. For example when the TIMESTAMP WITHOUT TIME ZONE is converted to TIMESTAMP type its date and time is equivalent in the local time zone.

The local time zone in NuoDB is set by the TimeZone connection property. SET TIME ZONE statement can be used to change the local time zone.

A Java program reads time zone information from the operating system by default. A connection time zone is not necessarily the same as the system time zone. The NuoDB JDBC driver considers the connection time zone is the local time zone whenever possible, but not all operations are controlled by NuoDB. One example is the toString() method of java.sql.Timestamp. It renders the date and time converted to the system time zone, not to the connection time zone.

Setting and getting timestamps

The common ways of setting and getting timestamps in JDBC is using the setTimestamp() and getTimestamp() methods, or setString() and getString() methods.

setTimestamp() and getTimestamp() are used with java.sql.Timestamp objects. Java Timestamp stores a time point in the 'UTC' time zone, similarly to the NuoDB TIMESTAMP type.

Using setTimestamp() on a TIMESTAMP WITHOUT TIME ZONE target converts the input Timestamp from 'UTC' time zone to the local time zone. Likewise, getTimestamp() converts a TIMESTAMP WITHOUT TIME ZONE source from local time zone to 'UTC'.

A string formatted timestamp is considered by NuoDB a date and time in the local time zone. Consequentially, the date and time is preserved when setString() and getString() are used on a TIMESTAMP WITHOUT TIME ZONE value. The same methods on a TIMESTAMP value requires a time zone conversion.

Examples

Take the next example using the set and get methods on both TIMESTAMP and TIMESTAMP WITHOUT TIME ZONE data types:

long timestampMillis = 1642332000000L; // UTC unix time for "2022-01-16 11:20:00"
Timestamp timestamp = new Timestamp(timestampMillis);
String timestampStringFormatted = "2022-01-16 11:20:00";

stmt.execute("create table t(id int, ts timestamp, tswotz timestamp without time zone)");
stmt.execute("set time zone 'America/New_York'");

PreparedStatement insertStmt = conn.prepareStatement("insert into t values(?, ?, ?)");
insertStmt.setInt(1, 1);
insertStmt.setTimestamp(2, timestamp);
insertStmt.setTimestamp(3, timestamp);
insertStmt.execute();
insertStmt.setInt(1, 2);
insertStmt.setString(2, timestampStringFormatted);
insertStmt.setString(3, timestampStringFormatted);
insertStmt.execute();

stmt.execute("set time zone 'UTC'");
ResultSet rs = stmt.executeQuery("select * from t");
while (rs.next()) {
        int i = rs.getInt(1);
        long millisTs = rs.getTimestamp(2).getTime();
        long millisTswotz = rs.getTimestamp(3).getTime();
        String stringTs = rs.getString(2);
        String stringTswotz = rs.getString(3);
        System.out.println();
        System.out.println("Printing row " + i + ":");
        System.out.println("TIMESTAMP to getTimestamp().getTime(): " + millisTs);
        System.out.println("TIMESTAMP to getString(): " + stringTs);
        System.out.println("TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): " + millisTswotz);
        System.out.println("TIMESTAMP WITHOUT TIME ZONE to getString(): " + stringTswotz);
}

The output result is:

Printing row 1:
TIMESTAMP to getTimestamp().getTime(): 1642332000000
TIMESTAMP to getString(): 2022-01-16 11:20:00
TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642314000000
TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 06:20:00

Printing row 2:
TIMESTAMP to getTimestamp().getTime(): 1642350000000
TIMESTAMP to getString(): 2022-01-16 16:20:00
TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642332000000
TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 11:20:00

Setting the connection time zone to 'UTC' guarantees that there are no time zone conversions when the information is retrieved from the database. Any differences observed in the output from the original data is explained by conversions happening during the set and insert operations.

The first row values are created from Timestamp objects and the second row values are created from String objects.

The first two results show that there is no conversion when a TIMESTAMP value is created from a Timestamp object.

The next two results show that Timestamp value is converted from 'UTC' time zone to 'America/New_York' when the target is a TIMESTAMP WITHOUT TIME ZONE data type.

In the second row, when the values are created from String objects, there is a conversion from 'America/New_York' to 'UTC' when the target is TIMESTAMP, but no conversions when the target is TIMESTAMP WITHOUT TIME ZONE.

If the same example is run by setting the time zone to 'Europe/Rome' before retrieving the data from the database, the output is changed to:

Printing row 1:
TIMESTAMP to getTimestamp().getTime(): 1642332000000
TIMESTAMP to getString(): 2022-01-16 12:20:00
TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642310400000
TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 06:20:00

Printing row 2:
TIMESTAMP to getTimestamp().getTime(): 1642350000000
TIMESTAMP to getString(): 2022-01-16 17:20:00
TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642328400000
TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 11:20:00

This time, observations on the conversions happening while getting data from the database can be made by comparing the two outputs.

Calling getTimestamp() from a TIMESTAMP value does no conversion, proved by the result being the same. Calling getString() converts from 'UTC' to 'Europe/Rome' and an hour difference can be observed.

The situation is reversed when the source is TIMESTAMP WITHOUT TIME ZONE. getTimestamp() does a conversion from 'Europe/Rome' to 'UTC' and the result is different. getString() does no conversions and the results is the same.

The same observations are repeated for the second row.