As a child, I enjoyed reading the book A Wrinkle in Time. I didn’t realize that wrinkles in time would consume so much mental energy throughout my career. Most people never need to consider the details of time zones, time zone offsets, daylight saving time, leap years, and the intricacies associated with dates in general. For most, date and time define a specific moment in time. As software developers, though, we have to consider the intricacies. If we choose the right path, though, we can let the underlying software platform do most of the work for us.
Here’s the great news: Oracle Database has all of this “date stuff” worked out. We just need to use it to its fullest potential. In this blog post, I offer a quick summary of the most important takeaways on this topic. Then I review the different date-related datatypes in more detail, and finish up with a quick look at timestamp arithmetic. But first….
The Takeaways, Upfront
- Stop using DATE. Use the appropriate variation of TIMESTAMP instead (explored below).
- Use TIMESTAMP WITH LOCAL TIME ZONE whenever you need to store a specific moment in time (with the rare exception of a birth certificate, in which case, use TIMESTAMP WITH TIME ZONE).
- If you are storing a date in the future and the time zone really matters (to the point that you are concerned that the laws governing your time zone might change its meaning), check out the “Counterpoint” box much farther down the page.
- Never use offsets when defining a user’s time zone. Always use the most specific time zone name possible.
- Always use proper timestamp arithmetic using the INTERVAL data type (a “smart” amount of time) and the handy conversion functions
- Don’t use APEX Automatic Time Zone detection. Use the TZ_Selector plug-in instead.
OK, got it? And now for some details….
What Does “Now” Mean?
Let’s clarify what constitutes a “specific moment in time” (the phrase comes up a lot below) and the different requirements we run into for storing dates and times.
Sometimes we just need the date (for example, our HR system records the day a person started working for our company. The time of day is irrelevant).
On the other end of the spectrum, we may need to store a specific moment in time. It may be irrelevant where the action physically occurred. For example, at 4:17 PM Eastern Daylight Time (EDT) on July 20th, 1969 Apollo 11 landed on the moon. The time zone is only relevant because it establishes the specific moment in time. Without the time zone, 4:17 PM on July 20th could represent more than 24 times around the world. Yes, some time zones have offsets of 30 and even 45 minutes. While time zones are critical to both store date-time and to present a date-time to a user, the two needs (storing and presenting) should be handled differently.
Coordinated Universal Time (UTC)
Coordinated Universal Time (UTC) is the date and time at the prime meridian (roughly located in Greenwich, England). UTC never changes. UTC never “springs forward” at the start of daylight saving time or “falls back” at the return to standard time. Time zones can be represented by either an offset from UTC or by a time zone region name. Time zone offsets represent a positive or negative number of hours and minutes different from UTC. For example, the offset abbreviations UTC-04:00, UTC -04, UTC-4, and -04:00 all represent 4 hours behind UTC–when it is noon UTC, it is 8 AM in the UTC-04 offset. Offsets never change. UTC-04 is always four hours behind UTC. Time zone region names, however, are what humans use day to day. For example, I live in the “Eastern” time zone. Many of these time zones do spring forward and fall back. Hence, the Eastern time zone sometimes has a -04 offset (Eastern Daylight Time – EDT) and sometimes has a -05 offset (Eastern Standard Time). To make things more confusing, over the years, governments have changed just when the switch from EDT to EST happens–and currently, there is pending legislation to remove the switch to EST altogether. Any date-time represented as “Eastern Time” (or any other time zone region name) requires a great deal of knowledge of the history of time zones to establish the precise moment in time it actually occurred.
For the purpose of storing date-time data, it is important to store all date-times with an offset rather than a time zone region name. Ideally, you would store all date-time data with the same offset (e.g. with the zero (0) offset – UTC).
Humans, however, want to know the date-time where they are, and they are likely to provide date-time data to the system in their local time zone region. That means computer systems need to convert date-time data from the local time zone region name into a specific offset (e.g. UTC) to store the data. Then the system needs to convert the data into a time zone region in order to present the data to users.
Examples of why time zone
Returning to specific examples, sometimes we need to store the exact time of day the event occurred in a specific place. While the time zone region name the event occurred in may be theoretically irrelevant, storing it may be helpful. For example, on a birth certificate, we need to show that John Doe was born on September 3rd, 1954, at 7:19 AM in Worcester, MA. As humans, we know that 7:19 AM represents the prevailing time zone offset that was in effect on that day in that location. We can consult some reference material and find that Worcester, MA is in the Eastern time zone and that on September 3rd, 1954, Daylight Saving Time was in effect (hence EDT), so the offset was UTC-04. We could accomplish this by storing the date-time in UTC and the location separately, but recreating that birth certificate would mean we need to map the location to the time zone region. This is the rare case in which storing the time zone region name as part of the date-time data is helpful.
The more common example, though, is that we don’t need to store a specific time zone; normalizing to UTC is both acceptable and preferable. For example, Anton sets up a meeting for 9 AM Eastern time on November 4th, 2022; when Velimir looks at it in his calendar, it shows up as a 2 PM meeting in Serbia (Central European Time). November 4th, 2022 is a particularly vexing date. Daylight Saving Time (DST) ended on October 30th, 2022 in Central European Time, but DST remained in effect in Eastern time until November 6th, 2022. This is true for the year 2022, but was different in 2015 and will again be different in 2023. The solution in this and most cases is to store the date-time in a normalized time zone offset (preferably UTC) and convert all date times from the local time zone into UTC when storing the data, and then convert it into the local time zone of the user when presenting the data. It is also critically important to convert it into the correct offset that applies to the date of the event–not to the offset of “today.” For example, if today is November 4th, 2022 the offset where I am in Boston is UTC-04. If I schedule a meeting on November 14th, the offset on that date is UTC-05.
Yes, all of this is very confusing. Fortunately, we can avoid having to do all of this ourselves.
Oracle Datatypes (Briefly)
DATE – the old standby, storing date and time down to the nearest second, with absolutely no time zone information.
TIMESTAMP(N) – date and time down to the nearest nanosecond (or less, depending on value of N), with absolutely no time zone information. DATE and TIMESTAMP(0) are roughly equivalent (differences explored below).
TIMESTAMP(N) WITH TIME ZONE – date and time down to the nearest nanosecond (or less, depending on value of N), along with a time zone.
TIMESTAMP(N) WITH LOCAL TIME ZONE – date and time down to the nearest nanosecond (or less, depending on value of N), along with time zone automatically normalized to the database time zone and automatically converted to the user time zone when selected out of the database.
Can you tell which datatype lets us use Oracle Database to its fullest potential? You got it: TIMESTAMP WITH LOCAL TIME ZONE
Oracle Datatypes in More Depth
Below are the Oracle database date-related datatypes we need to consider, along with examples of data you might store in each datatype. The list is ordered in what I think is how often they are used. As it turns out, that’s not at all how often they should be used.
This is the classic we all know and have used for years. STOP USING IT. The DATE datatype does not describe a moment in time. DATEs do not include time zone information. When you use a date, you don’t really know the moment in time that something happened. Admittedly, there are some cases when you really don’t care about the specific moment in time.
For example, in the state of Massachusetts, you can legally purchase alcohol when you “turn 21.” It doesn’t matter if you were born in California (which is three time zones behind Massachusetts); at 12:01 am on your 21st birthday, you are allowed to purchase alcohol. So, the DATE datatype might make sense…if TIMESTAMP(0) didn’t exist, but TIMESTAMP(0) does exist. So, I recommend you stop using DATE for these types of things and start using TIMESTAMP(0). That way, all of your date math will be the same.
Examples: HIRE_DATE, DATE_OF_BIRTH (for some purposes)
Like DATE, TIMESTAMP does not include time zone information, so it also does not describe a moment in time. It should only be used in the same way as DATE. TIMESTAMP has two advantages over DATE.
1) You can store more precise time information (to the fractions of a second). TIMESTAMP(0) is essentially the same as DATE. TIMESTAMP(6) with a millionth of a second accuracy is the highest precision that most operating systems support, but TIMESTAMP(9) with a billionth of a second accuracy exists and may be useful for scientific measurements, but realize that you generally won’t get that level of accuracy directly from your operating system, instead you’ll need that accuracy to come from some other kind of equipment that can measure to that level of accuracy.
2) I recommend TIMESTAMP instead of DATE because it uses the same “math” that the TIMESTAMP(N) datatypes below use–and gives the option of higher precision. TIMESTAMP is also a reasonable option within pl/sql when you only care about the difference between two moments in time. For example, if you capture the start and end TIMESTAMPs of a process and only wish to store the INTERVAL difference between the start and end.
Examples: HIRE_DATE, DATE_OF_BIRTH (for some purposes)
TIMESTAMP WITH TIME ZONE (TSTZ): Here, we have a datatype that captures a moment in time. Because TSTZ includes the date + time (optionally to the fraction of a second) + time zone, it does capture a moment in time. The ability to capture a moment in time is critical when scheduling anything, and recording most actions (created_date, publish_date, etc.). If TIMESTAMP WITH LOCAL TIME ZONE (described below) did not exist, TSTZ would be the winner.
TSTZ, however, really should only be used if knowing the time zone that something occurred in is important information. Generally, we care to know the moment in time that an event occurred or will occur–the actual time zone in which it occurs is irrelevant. Today a single meeting often occurs in multiple time zones. In most cases, if we normalize all of our TIMESTAMPs to UTC (or any arbitrary time zone), that is sufficient. As described below, TIMESTAMP WITH LOCAL TIME ZONE does exactly that.
There are occasionally reasons to use TIMESTAMP WITH TIME ZONE. As discussed above, when using date of birth, we usually only care about the day, but when we print it on the birth certificate, we want to know all of the details, including the time and the time zone.
Examples: DATE_OF_BIRTH (for a birth certificate)
TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ): Finally, the datatype we have all been waiting for! TSLTZ automatically normalizes the TIMESTAMP to the time zone of the database when it stores the data, and converts it back to the session time zone when it is selected out of the database. Even more amazing, if you define a variable in pl/sql as TSLTZ it will automatically do the conversions as the session time zone changes. Check out the code below:
declare l_tsltz timestamp with local time zone := systimestamp; begin dbms_output.put_line('Database: ' || to_char(l_tsltz,'YYYY-MM-DD HH24-MI-SS TZR TZD')); execute immediate 'alter session set time_zone=''US/Eastern'''; dbms_output.put_line('US/Eastern: ' || to_char(l_tsltz,'YYYY-MM-DD HH24-MI-SS TZR TZD')); execute immediate 'alter session set time_zone=''AMERICA/CHICAGO'''; dbms_output.put_line('AMERICA/CHICAGO: ' || to_char(l_tsltz,'YYYY-MM-DD HH24-MI-SS TZR TZD')); execute immediate 'alter session set time_zone=''US/PACIFIC'''; dbms_output.put_line('US/PACIFIC: ' || to_char(l_tsltz,'YYYY-MM-DD HH24-MI-SS TZR TZD')); end; /
with this impressive output
Database: 2022-10-27 10-19-37 AMERICA/NEW_YORK EDT US/Eastern: 2022-10-27 10-19-37 US/EASTERN EDT AMERICA/CHICAGO: 2022-10-27 09-19-37 AMERICA/CHICAGO CDT US/PACIFIC: 2022-10-27 07-19-37 US/PACIFIC PDT
Of course, if you want to manually cast to a specific time zone, you can do that as well.
declare l_tsltz timestamp with local time zone := systimestamp; l_tstz timestamp with time zone; l_tz varchar2(200) := 'Europe/Brussels'; begin execute immediate 'alter session set time_zone=''US/Eastern'''; l_tstz := l_tsltz AT TIME ZONE l_tz; -- using a variable to show it is possible, so cool -- 'Europe/Brussels' dbms_output.put_line('l_tsltz US/Eastern: ' || to_char(l_tsltz,'YYYY-MM-DD HH24-MI-SS TZR TZD')); dbms_output.put_line('l_tstz Europe/Brussels: ' || to_char(l_tstz,'YYYY-MM-DD HH24-MI-SS TZR TZD')); end; /
with this equally impressive output
l_tsltz US/Eastern: 2022-10-27 12-24-03 US/EASTERN EDT l_tstz Europe/Brussels: 2022-10-27 18-24-03 EUROPE/BRUSSELS CEST
All of this brings us to the clear conclusion that TSLTZ is the datatype of choice for most dates–those dates that seek to capture a moment in time.
Examples: CREATED, UPDATED, START_DATE, END_DATE
The next thing to consider is how precise your TIMESTAMP needs to be. Your database will likely default to TIMESTAMP(9) WITH LOCAL TIME ZONE. In most cases TIMESTAMP(0) WITH LOCAL TIME ZONE is sufficient. That gets you to the second. It’s just a waste of space to use (9) but doesn’t really hurt anything (unless you start checking to see if TIMESTAMPs are equal, then the extra precision may either help or hurt depending on your requirement).
Regarding the Database Time Zone
The TIMESTAMP WITH LOCAL TIME ZONE datatype normalizes to the database time zone. Hence there is no need to store a time zone with the data–it is, by definition, the database time zone.
I have been told – but I have not confirmed this – that once the database has data in a TIMESTAMP WITH LOCAL TIME ZONE column, the database time zone can not be changed.
Oracle recommends that the database time zone be set to UTC (and never changed). In practice, it does not matter what the database time zone is, so long as it never changes. TIMESTAMP WITH LOCAL TIME ZONE data will always normalize on the way into the field and convert to the session time zone on the way out.
That said, when exporting data (via any method) and importing it (via any method) to another database, you should explicitly include the time zone of the data. This is true regardless of the underlying datatype.
Use Time Zone Name (or Region) — NOT Offset — to Display Date-Time Data
Now that I have you convinced to switch from DATE to TIMESTAMP WITH (LOCAL) TIME ZONE to store a moment in time, let’s discuss some of the craziness that happens with time zones. The query below creates a dozen or so interesting data points.
select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss TZR TZD') sys_ts, to_char(systimestamp at time zone 'Asia/Kathmandu', 'yyyy-mm-dd hh24:mi:ss TZR:TZD') tzd_kathmandu, to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss TZR TZD') current_ts, to_char(TO_TIMESTAMP_TZ('2022-11-25 11:00:00 -04:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzh_tzm, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 US/Pacific','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_pacific_pdt, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 US/Eastern','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_eastern_edt, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 US/Pacific','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_pacific_pst, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 US/Eastern','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_eastern_est, to_char(TO_TIMESTAMP_TZ('2015-11-01 11:00:00 US/Pacific','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_pacific_2015, to_char(TO_TIMESTAMP_TZ('2015-11-01 11:00:00 US/Eastern','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_eastern_2015, -- to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 America/Indiana','YYYY-MM-DD HH24:MI:SS TZR'), -- 'yyyy-mm-dd hh24:mi:ss TZR:TZD') tzr_indiana_is_not_valid, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 America/Indiana/Indianapolis','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_indianapolis, to_char(TO_TIMESTAMP_TZ('2022-12-01 11:00:00 America/Indiana/Indianapolis','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_indianapolis_dec, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 America/Indiana/Knox','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_knox, to_char(TO_TIMESTAMP_TZ('2022-12-01 11:00:00 America/Indiana/Knox','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_knox_dec, to_char(TO_TIMESTAMP_TZ('2022-11-01 11:00:00 America/New_York','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_ny, to_char(TO_TIMESTAMP_TZ('2022-12-01 11:00:00 America/New_York','YYYY-MM-DD HH24:MI:SS TZR'), 'yyyy-mm-dd hh24:mi:ss TZR TZD') tzd_ny_dec, 1 ignore_this from dual
I won’t discuss all of the output, but check out these examples:
- Kathmandu has an offset of +0545. You don’t want to have to deal with that.
2022-10-28 03:40:11 ASIA/KATHMANDU:+0545
- Indianapolis, Indiana, and Knox, Indiana, are not always in the same time zone.
- In New York, 01-Nov-2022 and 01-Nov-2015 were different time zones.
I’ll say it again: you don’t want to have to figure out the offset yourself. The best way to avoid this is to set the session time zone to the time zone name, not the offset. It’s best to use the most explicit name you have access to–but usually, you can just rely on what the client propagates to you. Most clients will pass the time zone name. Browsers, SQL Developer, etc. will pick this up from the client machine and pass it along.
Browsers pass both the offset and the name. Again, be sure to use the name, not the offset. The reason: as you use dates that are not today, the offset of the region may change. If you use the name, the Oracle database will take care of everything. If you use the offset it will be wrong about for future and past dates about half the time in places that switch between standard and daylight saving times.
You can get a list of valid time zone names with this query:
select * from v$timezone_names order by 1 /
Note: If you are using Oracle Application Express, do not use the built-in Automatic Time Zone functionality. It, unfortunately, relies on the time zone offset. Instead, use the TZ_Selector plugin.
Oracle TIMESTAMP Math
For an in-depth treatment of this topic, check the links in References. The bottom line is that you cannot perform arithmetic operations between timestamps as easily as you can with dates.
Suppose for example that I want to add 5 days to a variable or column of type DATE. Then I can simply write:
my_date := sysdate + 5;
If I want to do the same thing with a timestamp, I need to use an interval, as in:
my_ts := my_ts + INTERVAL '5' DAY;
In the example above, however, ‘5’ must be a literal. It can not be a variable or parameter. This is quite limiting.
The solution is to use
numToYMInterval to add or subtract time from a timestamp:
my_ts := my_ts + numToDSInterval (5, 'day');
Be careful with
numToYMInterval because it will error if you create a date that does not exist (e.g. you can’t add 1 month to 30-Jan-2022 because 30-Feb-2022 does not exist).
select TO_TIMESTAMP_TZ('2022-01-29 11:00:00 US/Pacific', 'YYYY-MM-DD HH24:MI:SS TZR') + numToYMInterval(1, 'month') my_time from dual / ORA-01839: date not valid for month specified
Summing Things Up
It’s tricky, right? It’s tricky to understand the distinctions between date and timestamp. It’s tricky getting your head around time zones. It’s tricky to do the math on timestamps.
But just imagine how much trickier it would be if you had to actually deal with all the intricacies of calendars and time zones yourself!
So let’s all be very thankful to the engineers on the Oracle Database who did so much heavy lifting on our behalf!
Datetime and Interval Datatypes – from the Oracle doc set SQL Language Reference
Oracle Dates, Timestamps and Intervals – the usual outstanding tutorial from Tim Hall
Special thanks to Steven Feuerstein for his help