Using Timestamps and Time Zones

There are two timestamp types in NuoDB:

TIMESTAMP: 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.
TIMESTAMP WITHOUT TIME ZONE: 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 a TIMESTAMP, its date and time is equivalent in the local time zone.

Options:

Set the local time zone in NuoDB using the TimeZone connection property. If using a pooled DataSource, all connections in the pool will share this time zone.
Use SET TIME ZONE SQL statement to change the local time zone for the current connection.

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 to be 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 relative to the system time zone, not to the connection time zone.

Accessing Timestamps

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

Statement.setTimestamp() and ResultSet.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.
Working with TIMESTAMP columns.
- No conversion is required when setting a TIMESTAMP column from a java.sql.Timestamp.
- No conversion is required when getting a java.sql.Timestamp from a TIMESTAMP column.
Working with TIMESTAMP WITHOUT TIME ZONE columns
- Setting a TIMESTAMP WITHOUT TIME ZONE column from a java.sql.Timestamp converts the input timestamp from 'UTC' time zone to the local time zone.
- Getting a java.sql.Timestamp from a TIMESTAMP WITHOUT TIME ZONE converts from local time zone to 'UTC'.
- If the local time zone when you set the column is the same as the local time zone when you get the column, these conversions will be invisible.
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 Statement.setString() and ResultSet.getString() are used on a TIMESTAMP WITHOUT TIME ZONE value.
- The same methods on a TIMESTAMP value require a time zone conversion.

Example

Using the set and get methods on both TIMESTAMP and TIMESTAMP WITHOUT TIME ZONE data types.

This application:

Creates a timestamp object for 11:20am on 16 January 2022 (1642332000000L in UNIX time).
Creates a test table (called times) with both a `TIMESTAMP and TIMESTAMP WITHOUT TIME ZONE column in each row.
Adds two rows to the test table with local time zone set to New York (UTC-5)
- The first row uses a Java Timestamp to set the timestamp columns.
- The second uses a Java String containing the time in YYYY:MM:DD hh:mm:ss format.
- Outputs both rows (as Data Set 1 and 2) to see what values they contain
Repeats the same steps using local time zone Rome (UTC+1).
- Outputs Data Sets 3 and 4.

If necessary, scroll right to see the callouts ① ② ③ in this code.

public class TimestampDemo {

    protected void runDemo(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        PreparedStatement insertStmt = conn.prepareStatement("insert into times values(?, ?, ?, ?)");

        long timestampMillis = 1642332000000L; // UTC unix time for "2022-01-16 11:20:00"
        Timestamp timestamp = new Timestamp(timestampMillis);
        String timestampString = "2022-01-16 11:20:00";  // Same time as a String - note no timezone
        int dataSet = 1;

        /* Test using these two time zones */
        String[] timeZones = { "'America/New_York'", "'Europe/Rome'" };

        /* Table with a 'TIMESTAMP' column and a 'TIMESTAMP WITHOUT TIME ZONE' column */
        stmt.execute("USE USER");
        stmt.execute("DROP TABLE times IF EXISTS");
        stmt.execute("CREATE TABLE times(id INT, desc STRING, ts TIMESTAMP, tswotz TIMESTAMP WITHOUT TIME ZONE)");

        System.out.println();
        System.out.println("Original timestamp: " + timestampMillis + " = " + timestampString);
        System.out.println();

        /* Run this code twice, once for each time zone */
        for (String localTimeZone : timeZones) {

            /* Make local time zone New York (UTC-5) */
            stmt.execute("set time zone " + localTimeZone);                                   (1)
            stmt.execute("TRUNCATE TABLE times");  // Clear table for next test

            /* Set using Java Timestamp instance */
            insertStmt.setInt(1, dataSet++);
            insertStmt.setString(2, "timestamp");
            insertStmt.setTimestamp(3, timestamp);    // Saved as 11:20 UTC (ignoring local time zone)
            insertStmt.setTimestamp(4, timestamp);    // Saved as 11:20 no time zone
            insertStmt.execute();

            /* Set using Java String holding ANSI format date/time */
            insertStmt.setInt(1, dataSet++);
            insertStmt.setString(2, "String");
            insertStmt.setString(3, timestampString); // Saved as 06:20 New York time (UTC-5) due to
            insertStmt.setString(4, timestampString); // Saved as 11:20, no time zone
            insertStmt.execute();

            /* Make local time zone UTC */
            stmt.execute("set time zone 'UTC'");                                              (2)
            ResultSet rs = stmt.executeQuery("SELECT * FROM times");

            while (rs.next()) {
                int i = rs.getInt("id");
                String desc = rs.getString("desc");
                long millisTs = rs.getTimestamp("ts").getTime();
                long millisTswotz = rs.getTimestamp("tswotz").getTime();
                String stringTs = rs.getString("ts");
                String stringTswotz = rs.getString("tswotz");

                System.out.println("Local time zone: " + localTimeZone);
                System.out.println("  Data Set " + i + ": columns set from Java " + desc);
                System.out.println("    TIMESTAMP to getTimestamp().getTime(): " + millisTs
                        + showDifference(millisTs, timestampMillis));                         (3)
                System.out.println("    TIMESTAMP to getString(): " + stringTs);
                System.out.println("    TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): " + millisTswotz
                        + showDifference(millisTswotz, timestampMillis));                     (3)
                System.out.println("    TIMESTAMP WITHOUT TIME ZONE to getString(): " + stringTswotz);
                System.out.println();
            }
        }
    }

    /**
     * Returns either "(+/- N hours)", the number of hours difference between the two times, or
     * "(same as original timestamp)" if they are the same.
     */
    protected String showDifference(long newTime, long originalTime) { ... }
}

1	Set connection time zone for test, first New York, USA (UTC-5), then Rome, Italy (UTC+1).
2	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.
3	`showDifference()` outputs the number of hours difference relative to the original timestamp.

The output looks like this:

Original timestamp: 1642332000000 = 2022-01-16 11:20:00

Local time zone: ‘America/New_York’                                                                   (1)
 Data Set 1: columns set from Java timestamp                                                          (2)
  TIMESTAMP to getTimestamp().getTime(): 1642332000000 (same as original timestamp)                   (3)
  TIMESTAMP to getString(): 2022-01-16 11:20:00                                                       (3)
  TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642314000000 (-5 hours)                   (4)
  TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 06:20:00                                     (4)

Local time zone: ‘America/New_York’                                                                   (5)
 Data Set 2: columns set from Java String                                                             (6)
  TIMESTAMP to getTimestamp().getTime(): 1642350000000 (+5 hours)                                     (7)
  TIMESTAMP to getString(): 2022-01-16 16:20:00                                                       (7)
  TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642332000000 (same as original timestamp) (8)
  TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 11:20:00                                     (8)

Local time zone: ‘Europe/Rome’                                                                        (9)
 Data Set 3: columns set from Java timestamp                                                          (10)
  TIMESTAMP to getTimestamp().getTime(): 1642332000000 (same as original timestamp)                   (11)
  TIMESTAMP to getString(): 2022-01-16 11:20:00                                                       (11)
  TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642335600000 (+1 hours)                   (12)
  TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 12:20:00                                     (12)

Local time zone: ‘Europe/Rome’                                                                        (13)
 Data Set 4: columns set from Java String                                                             (14)
  TIMESTAMP to getTimestamp().getTime(): 1642328400000 (-1 hours)                                     (15)
  TIMESTAMP to getString(): 2022-01-16 10:20:00                                                       (15)
  TIMESTAMP WITHOUT TIME ZONE to getTimestamp().getTime(): 1642332000000 (same as original timestamp) (16)
  TIMESTAMP WITHOUT TIME ZONE to getString(): 2022-01-16 11:20:00                                     (16)

1	Data Set 1 - local time zone set to New York (UTC-5)
2	The first two values are created from a `Timestamp` object (default time zone UTC).
3	No conversion when a `TIMESTAMP` column is created from a `Timestamp` object, output is 11:20am, 16 January 2022 (UTC).
4	When `TIMESTAMP WITHOUT TIME ZONE` column is created from a `Timestamp` object, it is converted to local time zone (New York = UTC-5): 6:20am, 16 January 2022.
5	Data Set 2 - local time zone still set to New York (UTC-5)
6	The next two values are created from `String` objects (assumed to be in local time zone).
7	Conversion of `TIMESTAMP` column from New York time to UTC so 5 hours added, hence 16:20 pm.
8	No conversion from a `TIMESTAMP WITHOUT TIME ZONE` column as local time is now UTC.
9	Data Set 3 - local time zone now set to Rome (UTC+1)
10	The first two values are created from a `Timestamp` object (default time zone UTC).
11	As before, see <3>, no conversion when a `TIMESTAMP` column is set from a `Timestamp` object, output is 11:20am, 16 January 2022 (UTC).
12	As before, see <4>, `TIMESTAMP WITHOUT TIME ZONE` is set relative to local time zone and the one hour difference can be observed (12:20 instead of 11:20).
13	Data Set 4 - local time zone still set to Rome (UTC+1)
14	The next two values are created from `String` objects (assumed to be in local time zone).
15	The reverse of <11>. Calling `getTimestamp()` against a `TIMEZONE` column does a conversion from 'Europe/Rome' to 'UTC, so the time is different (an hour earlier at 10:20).
16	The reverse of <12>. Calling `getString()` against a `TIMESTAMP WITHOUT TIME ZONE` column, there is no conversion and the result is the same.