In this article, we’ll look at the FROM_TZ, TZ_OFFSET, TO_TIMESTAMP_TZ, and NEW_TIME functions in Oracle, explain how they work, and see some examples.
There are several timezone-related functions in Oracle, which I have described in this article.
Purpose of the Oracle Timezone Functions
FROM_TZ
The purpose of the FROM_TZ function is to convert a TIMESTAMP value and a specified TIME ZONE to a TIMESTAMP WITH TIME ZONE value.
It seems like a simple data conversion function, but it’s a good one to know, especially if you work with dates and timestamps a lot.
TZ_OFFSET
The purpose of the TZ_OFFSET function is to display a number that indicates the number of hours from UTC that a specified time is.
You can specify a few different parameters, which I’ll explain shortly.
TO_TIMESTAMP_TZ
The purpose of the TO_TIMESTAMP_TZ function is to convert a STRING into a TIMESTAMP WITH TIME ZONE data type. It’s similar to the TO_TIMESTAMP function.
What is a TIMESTAMP WITH TIME ZONE data type?
Well, it’s a type of DATE which has a few components:
- A date (day, month, and year)
- A time (hours, minutes, and seconds)
- A time zone (number of hours and minutes difference from GMT)
Because there is a need in many systems to store timezone information, Oracle has provided this data type. The timezone part is represented as a time value (hours and minutes) that is the number of hours and minutes before or after GMT.
So, “+7:00” is 7 hours after GMT (Bangkok time) and “-5:00” is 5 hours before GMT (US Eastern Time).
Understanding the TIMESTAMP WITH TIME ZONE data type is the hard part. Converting a string to this data type is pretty simple once you understand the data type.
NEW_TIME
The NEW_TIME function converts a date from one timezone to another timezone.
Get Your SQL Cheat Sheet
Three pages of SQL commands to save/print for easy reference
Oracle FROM_TZ Function Syntax and Parameters
The syntax of the FROM_TZ function is:
FROM_TZ ( timestamp_value, timezone_value )
The parameters of the FROM_TZ function are:
- timestamp_value (mandatory): This is the timestamp value that will be converted.
- timezone_value (mandatory): This is the timezone that the timestamp will be converted in to.
The return type of the FROM_TZ function is a TIMESTAMP WITH TIME ZONE type.
Oracle TZ_OFFSET Syntax and Parameters
The syntax of the TZ_OFFSET function is:
TZ_OFFSET ( timezone_name | time_value | SESSIONTIMEZONE | DBTIMEZONE )
This function has one parameter, but you can specify different values for the parameter.
The parameters of the Oracle TZ_OFFSET function are:
- timezone_name (optional): This is the name of the timezone to use (e.g. US/Eastern). See the section below on how to get a list of valid timezones.
- time_value (optional): You can enter an offset value from UTC, which will simply return itself. It can be entered in the format of ‘+/- hh:mm’.
- SESSIONTIMEZONE (optional): You can pass the SESSIONTIMEZONE function as a parameter to get the timezone offset of the current session.
- DBTIMEZONE (optional): You can pass the DBTIMEZONE function as a parameter to get the timezone offset of the database.
Oracle TO_TIMESTAMP Function Syntax and Parameters
The syntax of the Oracle TO_TIMESTAMP_TZ is:
TO_TIMESTAMP_TZ ( input_string [, format_mask] [, nls_param] )
The parameters of the TO_TIMESTAMP_TZ function are:
- input_string (mandatory): This is the string that is to be converted to the TIMESTAMP WITH TIME ZONE data type.
- format_mask (optional): This is the format of the input_string, as the input_string can be in many different formats. If this is not supplied, then the input_string must be in the same format as the default TIMESTAMP WITH TIME ZONE data type.
- nlsparam (optional): If this string is supplied, you can specify the language in which the month and day names and abbreviations are returned.
Some more notes on this function:
- The input type can be any of CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
- The return type is TIMESTAMP WITH TIME ZONE.
Oracle NEW_TIME Function Syntax and Parameters
The syntax of the NEW_TIME function is:
NEW_TIME ( input_date, timezone1, timezone2 )
The parameters of the NEW_TIME function are:
- input_date (mandatory): The date, including time, that is to be converted.
- timezone1 (mandatory): The timezone of the specified input_date value.
- timezone2 (mandatory): The timezone to convert the input_date to.
The return type is always DATE.
What is the FROM_TZ Timezone List?
There is a range of values you can set for the TIMEZONE parameter of the FROM_TZ function.
You can find a list of valid time zone names by querying the V$TIMEZONE_NAMES view.
SELECT * FROM V$TIMEZONE_NAMES;
| TZNAME |
TZABBREV |
| Africa/Abidjan |
LMT |
| Africa/Abidjan |
GMT |
| Africa/Accra |
LMT |
| Africa/Accra |
GMT |
| Africa/Accra |
GHST |
| Africa/Addis_Ababa |
LMT |
| … |
… |
What are the Valid Timezone Names in Oracle?
To get a list of all of the valid timezone names in an Oracle database, you can query the V$TIMEZONE_NAMES view.
SELECT * FROM V$TIMEZONE_NAMES;
| TZNAME |
TZABBREV |
| Africa/Abidjan |
LMT |
| Africa/Abidjan |
GMT |
| Africa/Accra |
LMT |
| Africa/Accra |
GMT |
| Africa/Accra |
GHST |
| Africa/Addis_Ababa |
LMT |
| … |
… |
How Can I Use Oracle TZ_OFFSET with Daylight Saving?
To use daylight savings with TZ_OFFSET, you need t o specify the timezone name when using this function.
Oracle has knowledge of what timezones map to what timezone offsets. So, to use daylight savings with this function, you need to specify a daylight savings timezone.
For example, the timezone name of ‘US/Eastern EDT’ relates to Daylight Savings time in US East, and ‘US/Eastern’ or ‘US/Easter EST’ relates to standard time in US East.
Can I Convert TZ_OFFSET to a Number?
Yes, you can, using a series of SIGN and SUBSTR functions.
What Is The Default Oracle TO_TIMESTAMP_TZ Format?
I mentioned earlier that the format_mask is optional. If you don’t supply it, you must specify the input_string in the default TIMESTAMP WITH TIME ZONE format.
But what is that format?
You can find it by looking at the NLS_DATABASE_PARAMETERS view, for the parameter “NLS_TIMESTAMP_TZ_FORMAT”
SELECT *
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_TIMESTAMP_TZ_FORMAT';
Result:
| PARAMETER |
VALUE |
| NLS_TIMESTAMP_TZ_FORMAT |
DD-MON-RR HH.MI.SSXFF AM TZR |
As you can see, the default value for TIMESTAMP WITH TIME ZONE is shown there. If you omit the format_mask, this is the format you must specify the input_string in.
What Formats Can I Use For The TO_TIMESTAMP_TZ Function?
Just like with many other Oracle functions, you can use the standard format identifiers.
| Parameter |
Explanation |
| YYYY |
Four digit year |
| MM |
Month (01-12, where JAN = 01) |
| MON |
Abbreviated name of month |
| MONTH |
Name of month, padded with blanks to length of 9 characters |
| DD |
Day of month (1-31) |
| HH |
Hour of day (1-12) |
| HH12 |
Hour of day (1-12) |
| HH24 |
Hour of day (0-23) |
| MI |
Minute (0-59) |
| SS |
Second (0-59) |
NEW_TIME Timezone Values
The NEW_TIME function can only take a limited range of timezones as inputs.
If you want to use more time zones, you can combine the FROM_TZ function and a date value (such as input_date or another date).
The timezone1 and timezone2 parameters can be any of these values:
- 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 Time
Examples of the FROM_TZ Function
Here are some examples of the FROM_TZ function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This is a simple example of the FROM_TZ function.
SELECT
FROM_TZ(TIMESTAMP '2016-07-13 19:20:14', '02:00') AS tz_test
FROM dual;
Result:
| TZ_TEST |
| 13-JUL-16 07.20.14.000000000 PM +02:00 |
This result shows the same date and time, but includes the time zone, as it’s a TIMESTAMP WITH TIME ZONE data type.
Example 2
SELECT
FROM_TZ(TIMESTAMP '2016-07-13 04:06:55', '-11:00') AS tz_test
FROM dual;
Result:
| TZ_TEST |
| 13-JUL-16 04.06.55.000000000 AM -11:00 |
Examples of the TZ_OFFSET Function
Here are some examples of the TZ_OFFSET function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1 – Timezone Name
This example uses a timezone name as the parameter.
SELECT
TZ_OFFSET('US/Eastern') AS tz_test
FROM dual;
Result:
Example 2 – Hours and Minutes
This example uses an hours and minutes value as the input.
SELECT
TZ_OFFSET('-02:00') AS tz_test
FROM dual;
Result:
Example 3 – SESSIONTIMEZONE
This example uses the SESSIONTIMEZONE function as a parameter.
SELECT
TZ_OFFSET(SESSIONTIMEZONE) AS tz_test
FROM dual;
Result:
Example 4 – DBTIMEZONE
This example uses the DBTIMEZONE function as a parameter.
SELECT
TZ_OFFSET(DBTIMEZONE) AS tz_test
FROM dual;
Result:
Example 5 – Convert to Number
This example shows the session time zone, as well as the session time zone converted to a number using TO_NUMBER.
SELECT
TZ_OFFSET(SESSIONTIMEZONE) AS tz_test,
SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3))) * (
TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 2, 2)) +
TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 5, 2)) / 60
) AS tz_offset_num
FROM dual;
Result:
| TZ_TEST |
TZ_OFFSET_NUM |
| +10:00 |
10 |
Examples of the TO_TIMESTAMP_TZ Function
Here are some examples of the TO_TIMESTAMP_TZ function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This example uses the default TIMESTAMP WITH TIME ZONE format, as I have not supplied a format_mask.
SELECT
TO_TIMESTAMP_TZ('8-DEC-15 10:04:01 AM +2:00') AS TS_TEST
FROM dual;
Result:
| TS_TEST |
| 08-DEC-15 10.04.01.000000000 AM +02:00 |
As you can see, the data looks the same, but it will be stored as a different data type. It follows the default pattern, which is “DD-MON-RR HH.MI.SSXFF AM TZR”.
Example 2
This example uses a timezone which is later than GMT.
SELECT
TO_TIMESTAMP_TZ('8-DEC-2015 02:44 AM +6:00', 'DD-MON-YYYY HH:MI AM TZH:TZM') AS TS_TEST
FROM dual;
Result:
| TS_TEST |
| 08-DEC-15 02.44.00.000000000 AM +06:00 |
The data is stored in a timezone that is +6 hours.
Example 3
This example uses a timezone which is earlier than GMT.
SELECT
TO_TIMESTAMP_TZ('12-DEC-2015 6:51 PM -11:00', 'DD-MON-YYYY HH:MI AM TZH:TZM') AS TS_TEST
FROM dual;
Result:
| TS_TEST |
| 12-DEC-15 06.51.00.000000000 PM -11:00 |
The data is stored in a timezone that is -11 hours.
Examples of the NEW_TIME Function
Here are some examples of the NEW_TIME function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1 – Using SYSDATE
In this example, I’ve used the SYSDATE function and converted it to a different timezone.
SELECT SYSDATE,
NEW_TIME(SYSDATE, 'GMT', 'PST') AS newtime_test
FROM dual;
Result:
| SYSDATE |
NEWTIME_TEST |
| 21/SEP/16 |
20/SEP/16 |
You can see here that both of the outputs are dates only, no time. This is because of my default output settings, and because I haven’t changed the output using a function such as TO_CHAR.
Example 2 – Using SYSTIMESTAMP
Let’s try another example using SYSTIMESTAMP instead of SYSDATE
SELECT
SYSTIMESTAMP,
NEW_TIME(SYSTIMESTAMP, 'GMT', 'PST') AS newtime_test
FROM dual;
Result:
| SYSTIMESTAMP |
NEWTIME_TEST |
| 21/SEP/16 05:09:25.300000000 AM +10:00 |
20/SEP/16 |
This example shows SYSTIMESTAMP showing date and time, but NEW_TIME does not.
Example 3 – Formatting Output
Let’s try using NEW_TIME again, but formatting the output.
SELECT
SYSTIMESTAMP,
TO_CHAR(NEW_TIME(SYSTIMESTAMP, ‘GMT’, ‘PST’), ‘dd-mm-yy hh:mi:ss AM’) AS newtime_test
FROM dual;
Result:
| SYSDATE |
NEWTIME_TEST |
| 21/SEP/16 05:09:55.610000000 AM +10:00 |
20-09-16 09:09:55 PM |
As you can see, the times are different. The GMT time is showing as 5AM, and the time converted to PST is 9PM. This is an 8 hour difference.
Similar Functions
Some functions which are similar to these timezone functions are:
- TO_TIMESTAMP: This function converts a string into a TIMESTAMP data type, which is similar, but has no time zone information.
- TO_DATE: This function converts a string into a date data type.
You can find a full list of Oracle SQL functions here.
Chris Memo 