When I was tasked with putting some data in BigQuery, Google Cloud’s data warehouse implementation, I had to look at the data we currently store in our local Oracle database. I found that we were using
DATE for fields that store “moments in time”. Normally, I would expect Unix timestamps here, or some other data type that contains the time zone.
Naturally, I complained about this, but people were quick to point out that it doesn’t matter, because you should relate the
DATE to the time zone of the database. Oracle, it turns out, is tracking two time zones: the time zone of the database itself:
SELECT dbtimezone FROM dual; -- Europe/Amsterdam
and the time zone of the client session:
SELECT sessiontimezone FROM dual; -- Europe/Moscow
I was warned that Oracle might change the query results based on either of these time zones, meaning when you insert a data type related to dates or time, you might get back an altered representation based on the time zone of the database or the session of the client.
This did not put my mind at ease at all. So, let’s take a look at what some of the common types relating to date and time are in Oracle, and when we should be careful with the query results.
DATE is a calendar date with a wallclock time without a time zone. If you put
2020-02-20 09:11:43 in the database, you will always retrieve the same exact datetime, no matter whether you’re in Amsterdam or New York. (It is the equivalent of a
java.time.LocalDateTime, if that’s your thing.)
SELECT sessiontimezone FROM dual; -- Europe/Amsterdam SELECT def_time FROM users WHERE username = 'email@example.com' -- 2020-02-20 09:04:50 ALTER SESSION SET time_zone = 'UTC'; SELECT def_time FROM users WHERE username = 'firstname.lastname@example.org' -- 2020-02-20 09:04:50
TIMESTAMP (without a time zone) is the same beast as a
DATE, except that you can specify times up to a fraction of a second (a
DATE needs to be rounded to the nearest second).
TIMESTAMP WITH LOCAL TIME ZONE
Say, you store the value
2020-02-20 09:11:43 +01:00. A client with a
Europe/Amsterdam in the winter will retrieve this exact value whether or not they retrieve it from an Amsterdam, Sydney or New York database. Another client with a session time zone that is set to
America/New_York, however, will get the value
2020-02-20 03:11:43 -05:00.
LOCAL means it adjusts the retrieved value to your session time zone.
ALTER SESSION SET time_zone = 'Europe/Moscow'; -- offset +03:00 SELECT to_char("ttz", 'yyyy-MM-dd HH24:MI:SS') FROM (-- timestamp with local, New York time zone SELECT CAST( to_timestamp_tz('2020-02-20 09:11:43 -05:00', 'yyyy-MM-dd HH24:MI:SS TZH:TZM') AS TIMESTAMP WITH LOCAL TIME ZONE) AS "ttz" FROM dual); -- 2020-02-20 17:11:43
See also this tutorial.
TIMESTAMP WITH TIME ZONE
This data type stores the time zone together with the datetime and displays it independently from the session time zone of the client.
If we store a value
2020-02-20 09:11:43 +01:00 in Amsterdam and copy this to a database in New York, this is the value that is returned to clients in both Amsterdam and New York regardless of their session time zone.
DATEs and plain
TIMESTAMPs don’t store a time zone and queries will always return the exact same value that was inserted regardless of
sessiontimezone. This makes them safe choices if you somehow don’t care about time zones.
TIMESTAMP WITH LOCAL TIME ZONE and
TIMESTAMP WITH TIME ZONE do store time zones. A
TIMESTAMP WITH LOCAL TIME ZONE will be represented differently depending on your session time zone. A
TIMESTAMP WITH TIME ZONE will not be represented differently; it will just include an offset.
You might now always want to use
TIMESTAMP WITH TIME ZONE, but apparently, they have problems of their own.
Falling back to
DATEs, however, is problematic because of Daylight Saving Time (DST) in the fall. When you store the current datetime in the fall, and you go through the dance of setting all clocks back from 3:00 to 2:00, you have no way of knowing at what exact point in time the row was inserted. This is bad if you rely on this for anything crucial, such as forensics (“so the hacker copied the entire database at 2:30, but was that before or after we reset our clocks?”).
DATEs in Java
As for me, having to deal with
DATEs without an explicit time zone, this means that the application inserting/retrieving these datetimes will need to decide how to interpret them. In my Java application, I will map them to
java.time.LocalDateTime, add a time zone and convert them to
Instant defTimeInstant = user.getDefTime() .atZone(ZoneId.of("Europe/Moscow")) .toInstant();
user.getDefTime() returns a
LocalDateTime indicating when the user was created. We can give a
LocalDateTime a time zone by invoking
atZone(ZoneId) on it, returing a
ZonedDateTime. Once we have converted the datetime to an actual point on the timeline (no longer being some kind of abstract datetime without a timezone), we can convert it to an
Instant by invoking
toInstant() on the