10Nov

A Wrinkle in Oracle Date and Timestamp

By: Anton Nielsen On: November 10, 2022 In: APEX Developer Solutions Comments: 5

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

  1. Stop using DATE. Use the appropriate variation of TIMESTAMP instead (explored below).
  2. 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).
    1. 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.
  3. Never use offsets when defining a user’s time zone. Always use the most specific time zone name possible.
  4. Always use proper timestamp arithmetic using the INTERVAL data type (a “smart” amount of time) and the handy conversion functions numToDSInterval or  numToYMInterval
  5. 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.

About That Acronym (UTC)

The name of the replacement for Greenwich Mean Time is Coordinated Universal Time. So why don’t we refer to it as CUT? Because the International Telecommunication Union (ITU) and the International Astronomical Union didn’t want to favor either the Americans or the French (for whom this wonderful new destination is named ” Temps Universel Coordonné” or TUC). Instead, they chose an acronym that would be the same across “all” languages and reinforce “UT” for “Universal Time.”

Ah, the joys of bureaucracy! Read more about this at TimeAndDate.

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.

DATE

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)

TIMESTAMP

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.

Why Store a Time Zone Offset–Not a Time Zone Region Name?

I stress that is important to store date-time data with time zone offset (preferably as a TIMESTAMP WITH LOCAL TIME ZONE)–not a time zone region name. (And hence, it is important that your database time zone is an offset–ideally UTC which is the zero (0) offset.) And, I make the exception for a birth certificate, which still holds…but only because we never issue a birth certificate on a future date. The problem with storing time zone names (as opposed to offsets) occurs with future dates.

Imagine this scenario…today (November 8th, 2022), I schedule a rocket launch for 8:07 am “Eastern time” November 10th, 2023.

If I store the time zone region (Eastern) not the time zone offset I don’t actually know what moment in time that refers to–which is probably important for a rocket launch. I probably chose the time because the earth is in a specific position. As of right now, that date will have a time zone offset of UTC-05. But, if congress changes the law, that time zone (Eastern) may have an offset of UTC-04. If I store the name, not the offset, my rocket may launch an hour off schedule and never reach the correct orbit.

https://www.reuters.com/world/us/us-congress-split-making-daylight-saving-time-permanent-2022-11-03/

Counterpoint: Why Store a Time Zone Region Name–Not an Offset

I was recently at the eye doctor. At the end of the appointment, we scheduled a visit for next year: November 18th, 2023, at 9:15 AM. As I am near Boston, Massachusetts, Eastern time was assumed, though not explicitly stated. As noted above, the US has pending legislation to change “Eastern” time by removing the “fall back,” eliminating Eastern Standard Time (EST, -5), and always remaining on Eastern Daylight Time (EDT -4)*. So, as of today, November 14th, 2023, my appointment next year will be at 14:15 UTC. But, if the law changes, my appointment will still be at 9:15 AM “Eastern,” but that will mean 13:15 UTC.

If at the time I made the appointment, I had stored it as a TSLTZ, I would have lost the fact that it was meant to be in “Eastern” time. That means, if the law changes and I apply the appropriate patch to my database, my appointment will appear to be at 10:15 AM Eastern time. There will be no way to know that it should be at 9:15 AM. I will arrive one hour late. This is very different than a rocket launch. For a rocket launch, the specific moment in time is important. For my eye exam, the prevailing local time is important.

This is the very rare case with using TSLTZ that has a drawback. If the laws defining a time zone change, you may have lost information about future scheduled dates. There are many benefits of using TSLTZ, but this drawback may mean that TSTZ should be the choice for some cases. There is a way to get the best of both. Store the data as TIMESTAMP(0) WITH TIME ZONE, but also have a virtual column that is TIMESTAMP(0) WITH LOCAL TIME ZONE.

When you store the data, you store it with the user’s time zone region name (e.g. US/Eastern or America/New_York). When you query data from the virtual column, it automatically translates to the session time zone.

See the example below:

create table my_tz_example(
    start_tstz          timestamp(0) with time zone,
    start_tsltz         as (cast (start_tstz as timestamp with local time zone))
)
/   


alter session set time_zone='America/New_York';

insert into my_tz_example(start_tstz)
  values (current_timestamp);
  
alter session set time_zone='US/Pacific';

insert into my_tz_example(start_tstz)
  values (current_timestamp);

commit; Then check out the results:
--
alter session set NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-YYYY HH24:MI:SS TZR TZD';
alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
alter session set time_zone='America/New_York';  

select mte.*, sessiontimezone
  from my_tz_example mte;

14-NOV-2022 11:01:20 AMERICA/NEW_YORK EST	  14-NOV-2022 11:01:20	America/New_York
14-NOV-2022 08:01:20 US/PACIFIC PST	        14-NOV-2022 11:01:20	America/New_York

--
alter session set time_zone='US/Pacific';

select mte.*, sessiontimezone
  from my_tz_example mte;

--
14-NOV-2022 11:01:20 AMERICA/NEW_YORK EST	  14-NOV-2022 08:01:20	US/Pacific
14-NOV-2022 08:01:20 US/PACIFIC PST	        14-NOV-2022 08:01:20	US/Pacific  

--
alter session set time_zone='Europe/Brussels';  

select mte.*, sessiontimezone
  from my_tz_example mte;

--
14-NOV-2022 11:01:20 AMERICA/NEW_YORK EST	  14-NOV-2022 17:01:20	Europe/Brussels
14-NOV-2022 08:01:20 US/PACIFIC PST	        14-NOV-2022 17:01:20	Europe/Brussels

As a general rule, this is valuable for dates that could be in the future and that are dependent on the time zone region. For audit columns (created, updated) there is no value and TSLTZ is a better choice.

Note: if you want to explicitly see the session time zone as part of your queried TIMESTAMP, you can add another virtual column:

alter table   my_tz_example add (start_tsstz  as (cast(
                                                    cast (start_tstz as timestamp with local time zone)
                                                    as timestamp with time zone))
                                );

*Right now the legislation is that “Eastern” time will always mean “Eastern Daylight Time” (EDT). Of course, the legislation could have the same effect by indicating that everyone that is now on “Eastern” time could move to “Atlantic” time, but always be on “Atlantic Standard Time” (AST). EDT and AST are  both UTC-4.  This would cause great havoc and is unlikely, but if it did, even the method above would not help.

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 numToDSInterval or  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!

References

Datetime and Interval Datatypes – from the Oracle doc set SQL Language Reference

Working With Dates in JavaScript, JSON, and Oracle Database – an in-depth look by Dan McGhan

Date and Timestamp Math – an Oracle LiveSQL script by Mike Hichwa

Oracle Dates, Timestamps and Intervals – the usual outstanding tutorial from Tim Hall

 

Special thanks to Steven Feuerstein for his help

Anton

See the Feuertips episode this blog is based on

Share this:
Share

5 Comments:

    • Pradosh
    • February 03, 2024
    • Reply

    Recently I looked at the blogs and the solutions by Anton (from Insum and Steven Feuerstein) about using the Data Type of DATE,TIMESTAMP,TIMESTEAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIME ZONE. Per his suggestion we should not use the DATE and TIMESTAMP data time .

    Link to the blog : https://www.insum.ca/a-wrinkle-in-oracle-date-and-timestamp/

    Primarily use the other timezone variant of the datatye which I agree and I started investigating how Oracle SaaS products like Oracle Fusion ERP , Oracle Fusion implements it and to my surprise none of the TABLEs are using the timezone variant of the data type (meaning they use only DATE and TIMESTAMP(0) or TIMESTAMP(6)) data type.

    Oracle Fusion Database has UTC timezone and it can not be changed. Oracle Fusion provides users to set preference and they can see the data in their timezone and also operations can be done from Fusion Screen from Local timezone set by the User Preference.

    So my question is how does Oracle Fusion is handling these without using the complexity of the timezone aware variant of datatype.

    Are we making it complicated for programmers by using these timezone variant data type OR Oracle Fusion SaaS is dping something which is more complex (as Fusion tables are legacy tables designed long back and may be all data type of those columns are already handled converting DATE or TIMESTAMP data ..as DB is always in UTC ..converting them to DBTIMEZONE of UTC to User Preference Timezone like EST , IST etc and vice versa.. I am not sure how complex will that will be ).

    Example of such table is : HZ_PARTIES table and many other tables .

    https://docs.oracle.com/en/cloud/saas/sales/oedms/hzparties-17207.html#hzparties-17207

    I am just looking for some guidance here which is the correct approach .

    Thanks

    Pradosh

    • Anton Nielsen
    • February 06, 2024
    • Reply

    Hello Pradosh,
    First, all databases should have the DBTIMEZONE set to UTC. That should always be the setting.
    My general recommendation is to use the datatype of TIMESTAMP WITH LOCAL TIME ZONE. This automatically converts any data that gets stored in the table to the DBTIMEZONE and it is stored without a TIME ZONE–because the TIME ZONE is the same as the database (which should be UTC). So, as far as storing things goes, TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE store the same information in the table. The actual data in a TIMESTAMP and a TIMESTAMP WITH LOCAL TIME ZONE column will be the same. How the database reacts to the data when it is retrieved from the database is the difference (and when it is stored). When you
    select my_ts_ltz from my_table — where my_ts_ltz is TIMESTAMP WITH LOCAL TIME ZONE
    it automatically converts the TIMESTAMP into a TIMESTAMP WITH TIME ZONE using the TIME ZONE of the current database session. (And when you insert or update my_ts_tlz the database will convert from the current session TIME ZONE to the DBTIMEZONE, which should be UTC.) That means that any session that has explicitly set its session TIME ZONE will see the date and time automatically TIME ZONE corrected.

    Regarding Fusion Apps and the datatypes they use…these apps either ignore time zones (many cases) OR, in a few cases where these applications handle time zones, they do the conversions within the application, not relying on the database to do it. That means the applications have to explicitly convert a TIMESTAMP WITH TIME ZONE to UTC and do then do the insert or update. Then, when they select the data out of the table, they have to cast the TIMESTAMP data into a TIMESTAMP WITH TIME ZONE in the correct time zone.
    Now…there are RARE cases when you actually want to store the time zone. In that case you should use TIMESTAMP WITH TIME ZONE (without the key word LOCAL). In these rare cases, you may need to manually cast from one time zone to another. That is rare though. It is really only relevant if you are scheduling something in the future and you are concerned that the definition of a TIME ZONE could change. That’s another long discussion, though.

    • Pradosh
    • February 16, 2024
    • Reply

    Thanks for the detailed explanation Anton and now this is making sense to me , that if we do not use the TIMESTAMP WITH LOCAL TIMEZONE data type , the application has to explicitly handle , With TIMESTAMP WITH LOCAL TIMEZONE datatype and correct database session timezone set , the Database will automatically take care of the conversion.

    • Ronald
    • September 18, 2024
    • Reply

    Hi Anton,

    Thanks for your blog and video. It is all a pain.

    You wrote: “Now…there are RARE cases when you actually want to store the time zone. In that case you should use TIMESTAMP WITH TIME ZONE (without the key word LOCAL). In these rare cases, you may need to manually cast from one time zone to another. That is rare though. It is really only relevant if you are scheduling something in the future and you are concerned that the definition of a TIME ZONE could change. That’s another long discussion, though.”
    We have specific requirements to be able to provide the timestamp when something actually happened. For example, I get money from an ATM in Sydney at 14:22. I do not want to see the ’14:22’ in my local time, I want to know the local time it actually happened, i.e. ’14:22’. For that I would store the transaction time in a ‘TIMESTAMP with TIME TIME ZONE’. It will store the timestamp and the offset/region. I can go to UTC and from there to my local time zone, or use the time zone of when the transaction happened.
    Yes, this is a data warehouse.

    Thanks

    Ronald

    • Anton Nielsen
    • September 19, 2024
    • Reply

    Ronald,
    Yes, this is exactly when you would want to use TIMESTAMP WITH TIME ZONE. You can explicitly convert it to the local time zone if you want, but you the information you need using TIMESTAMP WITH TIME ZONE.
    Anton

Leave reply:

Your email address will not be published. Required fields are marked *