FB

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 Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

 

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 timezones, you can combined 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:

TZ_TEST
-04:00

 

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:

TZ_TEST
-02:00

 

Example 3 – SESSIONTIMEZONE

This example uses the SESSIONTIMEZONE function as a parameter.

SELECT TZ_OFFSET(SESSIONTIMEZONE) AS tz_test
FROM dual;

Result:

TZ_TEST
+10:00

 

Example 4 – DBTIMEZONE

This example uses the DBTIMEZONE function as a parameter.

SELECT TZ_OFFSET(DBTIMEZONE) AS tz_test
FROM dual;

Result:

TZ_TEST
+0:00

 

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 the 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your SQL Function Cheat Sheet Now: