When working with dates and timestamps, an almost inevitable tasks will be working with different time zones and daylight saving transitions. Even if all of the data you use is with a single local time, chances are you will eventually interface with a system that uses a different one.
Assumed Time zones
If I have a date/time value, say “January 3rd, 2020 3pm”, that might sound specific but without context it could be a date and time for anywhere in the world and thus isn’t very helpful by itself. However – within a given application instance, for example – an application running in Ohio with only local data. Perhaps it’s customer transactions in Columbus, or temperature readings in Toledo. The dates and times associated with that data likely will be US/Eastern time, and because Ohio observes Daylight Saving transitions, it can be reasonably assumed that the above date would mean January 3rd, 2020 3pm US/Eastern Standard Time. Many applications can run successfully with an assumed framework such as this. Data usage of this form can be a valid way to manage date/time values, as long as the assumption is documented and clearly understood by developers interfacing with that data. Doing so not only simplifies the program logic it can also be more efficient because you don’t need to waste bytes on redundant time zone contexts or include conversion steps all over the application. As long as you are able to reliably enforce a consistent time zone on data entry the rest of the application might run smoothly with that assumption.
You might have caught some ambiguity in that paragraph with the phrases “can be valid” and “might run smoothly.” While it should be simple to use Ohio data for the January date/time value above, what if I get a date value of March 8, 2020 2:30am?
Such a value can be stored, but what does it mean? The clock jumps from 2:00am to 3:00am on that date. So what time would 2:30 represent? Perhaps you can ignore that as an invalid value or maybe you have to come up with an additional assumption of how to interpret data that falls in the skipped hour. On the other hand what if you got a value of November 1, 2020 1:30am. What does that mean?
The 1:00am-to-2:00am hour occurs twice on the return to Standard time. So, if you see 1:30am, is that the first 1:30 or the second 1:30 for that day? Unlike the spring transition, treating these as invalid values doesn’t make sense, 1:30am really is a valid time on the U.S. clocks, and it will occur twice. Thus, if you have a system with an assumed time zone, you will also need to have a rule for assumed Daylight Saving observation. In addition to these concerns, the enforcement of a consistent time zone can only be controlled in application logic. Which means all applications and services interfacing with the data will need to have the same rules embedded in them. In general, a constraint on a column won’t be feasible to ensure data integrity, it will be on the applications to determine the context and then make sure the table values are written correctly.
Explicit Time zones
If the concerns of using assumed time zones are not palatable or if you need to preserve time zone information of incoming or generated data then you must be explicit. Timestamps make this easy for you by having an explicit type “TIMESTAMP WITH TIME ZONE.” If you have Date types (or TIMESTAMPs without time zones,) you will need to store GMT/UTC offsets or named time zone information along with the date values in order to know the proper context.
A couple of simplified examples might look something like one of these.
create table my_dates (d date, tz varchar2(64)); create table my_dates (d date, utc_offset number(4,2));
As of time zone file v33, there are 595 legal time zone names, and 38 distinct offsets from -12 to +14 hours. To help ensure valid data you could add a constraint to only allow those time zones or offsets that you want to support. If the choice of valid values is small and expected to remain static you could use a check constraint with an IN-list; or, if it will change then create a table of legal values and use a foreign key constraint.
Once you have a date/time value and a time zone you can then gain use of the various functions Oracle provides for formatting and adjusting values including NEW_TIME, FROM_TZ, SYS_EXTRACT_UTC, and TZ_OFFSET as well as the AT TIME ZONE clause for timestamps with time zone. These will be discussed in more detail in the next section.
Converting and extracting time zones
Before Oracle introduces timestamps (9i,) DATEs still needed to be able to have time zones associated with them and the NEW_TIME function could be used to convert some DATE values from one time zone to another.
Unfortunately, the NEW_TIME function only supports a limited set of time zones. As of the 20c SQL Reference the following are still the only abbreviations recognized.
AST, ADT: Atlantic Standard or Daylight Time
BST, BDT: Bering Standard or Daylight Time
CST, CDT: Central Standard or Daylight Time
EST, EDT: Eastern Standard or Daylight Time
GMT : Greenwich Mean Time
HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.
MST, MDT: Mountain Standard or Daylight Time
NST : Newfoundland Standard Time
PST, PDT: Pacific Standard or Daylight Time
YST, YDT: Yukon Standard or Daylight TimeAlso of note, the usage does require the invoker to know if Standard time or Day Light Saving time is to be used, even if the time doesn’t make sense by normal calendars and clocks of the countries using these time zones.
For instance, January 1, 2020 00:00:00 Eastern Daylight Time is a legal, operable time; but in the U.S. that time is not something that would ever appear in normal usage as all time zone and DST observation laws would make the entire day of January 1, 2020 Standard Time (Eastern or otherwise.) This particular quirk of syntax can be troublesome on the Standard/Daylight transition days. If you know the Standard vs Daylight rules in order to use the correct parameters the function itself is pretty straightforward.
SQL> select 'EST to CST' conversion, sysdate,new_time(sysdate,'EST','CST') converted from dual
2 union all
3 select 'EST to CDT' conversion, sysdate,new_time(sysdate,'EST','CDT') converted from dual
4 union all
5 select 'GMT to CST' conversion, sysdate,new_time(sysdate,'GMT','CST') converted from dual
6 union all
7 select 'CST to GMT' conversion, sysdate,new_time(sysdate,'CST','GMT') converted from dual;
CONVERSION SYSDATE CONVERTED
_____________ ______________________ ______________________
EST to CST 2020-10-31 01:41:01 2020-10-31 00:41:01
EST to CDT 2020-10-31 01:41:01 2020-10-31 01:41:01
GMT to CST 2020-10-31 01:41:01 2020-10-30 19:41:01
CST to GMT 2020-10-31 01:41:01 2020-10-31 07:41:01If you want to work with “Prevailing” time, you must build a function of your own as time zones such as EPT (Eastern Prevailing Time) are not real time zones. They are merely abbreviations of convenience meaning Eastern time zone with whatever the current daylight saving rules are for the given time (with the caveat that it’s not known for the doubled clock-hour of transition from Daylight to Standard time.)
Instead of writing your own function, another option is to convert DATE types to TIMESTAMP WITH TIME ZONE types and then use the native timestamp functions.
For example, to convert from Central “Prevailing” Time to GMT, depending on the time of year that might be CDT or CST, that is a -5 or -6 hour offset from GMT. Using native functions you would use ‘US/Central’ as the time zone and then convert that to GMT.
We’ll use the TO_TIMESTAMP_TZ function to construct two timestamps, one for standard time and another for daylight time. Then convert each to GMT.
SQL> SELECT tz, original, original AT TIME ZONE 'GMT' converted
2 FROM (SELECT 'CST' tz, TO_TIMESTAMP_TZ('2020-01-01 US/Central', 'yyyy-mm-dd TZR') original
3 FROM DUAL
4 UNION ALL
5 SELECT 'CDT' tz, TO_TIMESTAMP_TZ('2020-06-01 US/Central', 'yyyy-mm-dd TZR') original
6* FROM DUAL);
TZ ORIGINAL CONVERTED
______ _____________________________ _____________________________
CST 2020-01-01 00:00:00 -06:00 2020-01-01 06:00:00 +00:00
CDT 2020-06-01 00:00:00 -05:00 2020-06-01 05:00:00 +00:00You could also use timestamp literal values to construct the sample times
SQL> SELECT tz, original, original AT TIME ZONE 'GMT' converted
2 FROM (SELECT 'CST' tz, timestamp '2020-01-01 00:00:00 US/Central' original
3 FROM DUAL
4 UNION ALL
5 SELECT 'CDT' tz, timestamp '2020-06-01 00:00:00 US/Central' original
6 FROM DUAL);
TZ ORIGINAL CONVERTED
______ _____________________________ _____________________________
CST 2020-01-01 00:00:00 -06:00 2020-01-01 06:00:00 +00:00
CDT 2020-06-01 00:00:00 -05:00 2020-06-01 05:00:00 +00:00Those examples show how to convert a timestamp, but what if you have a DATE type? You can use the same methods, but first you must convert the DATE to a TIMESTAMP WITH TIME ZONE type and then you can use the AT TIME ZONE clause to change the time zone. Unfortunately you can’t do it one step. First you have to convert the DATE to a TIMESTAMP, then you have convert the TIMESTAMP to a TIMESTAMP WITH TIME ZONE, and then you can do the final conversion.
In the first query below, I use inline views to generate each intermediate value and show them. In real code you would simply nest all the calls all in one as shown in the second query.
SQL> SELECT original,
2 ts,
3 tstz,
4 tstz AT TIME ZONE 'GMT' converted
5 FROM (SELECT original, ts, FROM_TZ(ts, 'US/Central') tstz
6 FROM (SELECT original, CAST(original AS TIMESTAMP) ts
7* FROM (SELECT DATE '2020-01-01' original FROM DUAL)));
ORIGINAL TS TSTZ CONVERTED
______________________ ______________________ _____________________________ _____________________________
2020-01-01 00:00:00 2020-01-01 00:00:00 2020-01-01 00:00:00 -06:00 2020-01-01 06:00:00 +00:00
SQL> SELECT FROM_TZ(CAST(DATE '2020-01-01' AS TIMESTAMP), 'US/Central') AT TIME ZONE 'GMT' all_in_one from dual;
ALL_IN_ONE
_____________________________
2020-01-01 06:00:00 +00:00In these examples I’ve been converting to GMT but due to the internationally common usage of GMT/UTC timing, Oracle has a dedicated function, SYS_EXTRACT_UTC, for extracting UTC timestamps directly without specifying it as a parameter. Note, the return type of this function is a simple TIMESTAMP, not TIMESTAMP WITH TIMEZONE. So, if you need to retain the timezone information, it will still be necessary to use the AT TIME ZONE clause.
SQL> select systimestamp,sys_extract_utc(systimestamp) from dual;
SYSTIMESTAMP SYS_EXTRACT_UTC(SYSTIMESTAMP)
------------------------------------- -------------------------------
05-NOV-20 10.29.41.417347 PM -05:00 06-NOV-20 03.29.41.417347 AMThe last function, TZ_OFFSET, is used to find the hours and minutes offset from UTC. It can be confusing though since it can return different results depending on the given time zone and the current system time. For example, when I check the offset of US/Eastern during the winter months it will return -5 hours.
SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
TZ_OFFS
-------
-05:00If I were to run the same query during the summer the function would return -4 hours because Daylight Saving time would be in effect. Unfortunately there is no way to request an offset relative to a particular date or time; it will always return the offset based on the current system time.
In addition to named time zones, you can also pass in the key words SESSIONTIMEZONE or DBTIMEZONE to extract the offset values for the current settings.
It is also legal to pass in an offset, but then the function simply returns the offset itself. Strangely the offset parameter does have constraints on which values are allowed but it is still possible to put in nonsensical offsets. The queries below throw the valid numerical ranges; but still allows an input offset that doesn’t correspond to any defined time zone.
SQL> select TZ_OFFSET('9:99') from dual;
select TZ_OFFSET('9:99') from dual
*
ERROR at line 1:
ORA-01875: time zone minute must be between -59 and 59
SQL> select TZ_OFFSET('99:99') from dual;
select TZ_OFFSET('99:99') from dual
*
ERROR at line 1:
ORA-01874: time zone hour must be between -15 and 15
SQL> select TZ_OFFSET('12:34') from dual;
TZ_OFFS
-------
+12:34Timestamp with Time Zone Indexes
It is possible to create indexes on DATE and TIMESTAMP columns; but if you try to create one on a TIMESTAMP WITH TIME ZONE column, you won’t get a regular index. Instead Oracle will create a Function-Based Index for you.
SQL> create table test (ts timestamp with time zone not null);
Table TEST created.
SQL> create index test_ts_idx on test (ts);
Index TEST_TS_IDX created.
SQL> select index_name,index_type from user_indexes where table_name = 'TEST';
INDEX_NAME INDEX_TYPE
______________ ________________________
TEST_TS_IDX FUNCTION-BASED NORMAL
SQL> select index_name,column_name,column_position from user_ind_columns where table_name = 'TEST';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
______________ _______________ __________________
TEST_TS_IDX SYS_NC00002$ 1
SQL> select * from user_ind_expressions where table_name = 'TEST';
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
______________ _____________ ________________________ __________________
TEST_TS_IDX TEST SYS_EXTRACT_UTC("TS") 1Fortunately, queries against the table that might want to use the index will also be automatically converted to have compatible form for the index.
Here is an excerpt of an explain plan querying data by the indexed column.
SQL_ID a5myak5ts482j, child number 0
-------------------------------------
select * from test where ts > systimestamp - interval '1' hour
Plan hash value: 544698101
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST |
|* 2 | INDEX RANGE SCAN | TEST_TS_IDX |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEST"."SYS_NC00002$">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)-INTERVAL'+00 01:00:00' DAY(2) TO SECOND(0)))Note the original query uses “systimestamp – interval ‘1’ hour” but the optimizer canonicalizes that to a timestamp without a timezone but converted to UTC. So the final comparison is between two SYS_EXTRACT_UTC results.
This rewrite has additional implications in that it is illegal to have a PRIMARY KEY or UNIQUE constraint on a TIMESTAMP WITH TIME ZONE column.
SQL> drop table test purge;
Table TEST dropped.
SQL> create table test (ts timestamp with time zone not null);
Table TEST created.
SQL> alter table test add constraint pk_test primary key (ts);
Error starting at line : 1 in command -
alter table test add constraint pk_test primary key (ts)
Error report -
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or
a primary key
02329. 00000 - "column of datatype %s cannot be unique or a primary key"
*Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
on a column of datatype VARRAY, nested table, object, LOB, FILE
or REF.
*Action: Change the column datatype or remove the constraint. Then retry
the operation.
SQL> alter table test add constraint uk_test unique (ts);
Error starting at line : 1 in command -
alter table test add constraint uk_test unique (ts)
Error report -
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or
a primary key
02329. 00000 - "column of datatype %s cannot be unique or a primary key"
*Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
on a column of datatype VARRAY, nested table, object, LOB, FILE
or REF.
*Action: Change the column datatype or remove the constraint. Then retry
the operation.
Interestingly, while it is not legal to declare a unique or primary key constraint on the column, it is legal to have a unique index on the column. The unique index will, of course, also be a function-based index on the SYS_EXTRACT_UTC value.
SQL> create unique index uk_test on test (ts);
Index UK_TEST created.TIMESTAMP WITH LOCAL TIME ZONE
There is one additional timestamp type which I have not mentioned earlier. Structurally it’s the same as a TIMESTAMP; but rather than an assumed time zone as described earlier, the TIMESTAMP WITH LOCAL TIME ZONE type has an explicit internal time zone associated with its stored value. When you query the value, your session’s time zone will be used to determine the returned result. So, even if the data is stored internally in UTC (the default) if your session time zone is set to US/Eastern, you’ll see the value converted to US/Eastern time. Conversely, if you insert a US/Eastern value it will be converted to the internal time zone. The internal timezone is dbtimezone.
I do not usually use this data type. While it seems like it would solve a variety of usability issues and provide user convenience, in practice I have found them to cause more problems. I work with data in multiple time zones, so this data type may seem like a natural thing to try; but when two people in different time zones look at the same data inside of the database but then share it outside the database it creates inconsistency. I live in Ohio, some of my coworkers live in Oklahoma. If we each generate the same report, I’ll see the data in US/Eastern whereas the others would see the same data in US/Central. If we then shared our reports with others they would appear to have different results.
I have also seen interfaces that would pre-convert data to canonical time, but when loaded into a TIMESTAMP WITH LOCAL TIME ZONE, the database would convert it again from whatever session settings were used into the dbtimezone, thus creating a doubled conversion.
All of these issues are solvable and I’m sure the data type provides useful convenience in many applications I’ve never had the opportunity to touch. So, I don’t want dissuade readers from trying the data type, but do be wary of how it operates and if you have varied session time zone settings your users may see confusing results if they aren’t prepared for the way the automatic conversions operate.