Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Oracle SESSIONTIMEZONE Function

by

Oracle SESSIONTIMEZONE FunctionOracle SESSIONTIMEZONE Function is used to get current session’s time zone as a time zone offset or even as a time zone region name.

Oracle SESSIONTIMEZONE Function Syntax

 SELECT SESSIONTIMEZONE
FROM table_name;

Oracle SESSIONTIMEZONE – Using SQL SELECT Statement Example

Oracle SESSIONTIMEZONE Function is used with the SQL SELECT Statement.

For example, the Oracle SESSIONTIMEZONE query below returns the current session’s time zone.

SELECT SESSIONTIMEZONE
FROM DUAL;

May return “-07:00”
Or it may return “:+5:30”
Depending upon the latest session time zone value as set by the ALTER SESSION command.

Oracle DBTIMEZONE Function

by

Oracle DBTIMEZONE FunctionOracle DBTIMEZONE Function is used to get the data-base time zone as a time zone offset or even as a time zone region name.

Oracle DBTIMEZONE Function Syntax

SELECT DBTIMEZONE
FROM table_name;

Oracle DBTIMEZONE – Using SQL SELECT Example

Oracle DBTIMEZONE can be used with the SQL SELECT Statement.

For example, the below query returns the data base time zone or time zone offset.

SELECT DBTIMEZONE
FROM DUAL;

May return “+00:00”
Or it may return “:+5:30”

Depending upon the time zone value set in the Oracle data-base

Oracle FROM_TZ Function

by

Oracle SQL FROM_TZ FunctionOracle FROM_TZ Function is used to convert a TIMESTAMP value to another TIMASTAMP with TIME ZONE value. Oracle FROM_TZ Function can be used with the SQL TRUNC Function and SQL TO_CHAR Function.

Oracle FROM_TZ Function Syntax

SELECT FROM_TZ(timestamp_value,timezone_value)
FROM table_name;
  • ‘timestamp_value’ is the value that has to be converted to a TIMESTAMP with TIME ZONE value
  • ‘timezone_value’ is the time zone that is used to convert the time stamp value to time stamp with time zone value .

Oracle FROM_TZ Function – Using SQL SELECT Statement Example

Oracle FROM_TZ Function is used with the SQL SELECT Statement.

For example, the Oracle FROM_TZ Function query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value.

SELECT FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '5:30')
FROM DUAL;

Will return “12/15/2012 1:50:42.000000000 AM +05:30”

Oracle FROM_TZ Function – Using SQL TRUNC Function Example

Oracle FROM_TZ Function can be used with the SQL TRUNC Function.

For example, the SQL query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value and returns only the date component.

SELECT TRUNC(FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '-7:00'))
FROM DUAL;

Will return “12/15/2012″.

Oracle FROM_TZ Function – Using SQL TO_CHAR Function Example

Oracle FROM_TZ Function can be used with the SQL TO_CHAR Function.

For example, the Oracle FROM_TZ Function query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value and spells the Month.

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '-7:00'),'DD-MONTH-RRRR')
FROM DUAL;

Will return “15-DECEMBER -2012″.

Oracle TZ_OFFSET Function

by

SQL TZ_OFFSET FunctionOracle TZ_OFFSET Function is used to return the TIMEZONE offset of a value.

Oracle TZ_OFFSET Function Syntax

SELECT TZ_OFFSET(time_zone)
FROM table_name;

‘time_zone’ can be any time zone, a time zone offset from UTC, a sessiontimezone or a dbtimezone.

Examples of Timezones:-

Canada/Atlantic Pacific/Easter
Canada/Central Pacific/Honolulu
Canada/East-Saskatchewan Pacific/Kwajalein
Canada/Eastern Pacific/Pago_Pago
Canada/Mountain Pacific/Samoa
Canada/Newfoundland US/Alaska
Canada/Pacific US/Aleutian
Canada/Saskatchewan US/Arizona
Canada/Yukon US/Central
Europe/Dublin US/East-Indiana
Europe/Istanbul US/Eastern
Europe/Lisbon US/Hawaii
Europe/London US/Michigan
Europe/Moscow US/Mountain
Europe/Warsaw US/Pacific
Greenwich US/Pacific-New
Pacific/Auckland US/Samoa
Pacific/Chatham

Using the below SQL SELECT Statement you can view all the Timezones available.

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

Oracle TZ_OFFSET – Using SQL SELECT Example

Oracle TZ_OFFSET Function is used with the SQL SELECT Statement.

For example, the Oracle TZ_OFFSET query below returns the Time Zone offset of “Asia/Tokyo” zone.

SELECT TZ_OFFSET('Asia/Tokyo')
FROM DUAL;

Will return “+09:00” as the offset for the ASIA/TOKYO time zone.

Oracle TZ_OFFSET – Using SESSIONTIMEZONE Example

Oracle TZ_OFFSET can be used with the SQL SESSIONTIMEZONE Function.

For example, the Oracle TZ_OFFSET query below returns the Timezone offset of the current session of the database.

SELECT TZ_OFFSET(SESSIONTIMEZONE)
FROM DUAL;

May return something like “+05:30” depending upon the session’s timezone offset.

Oracle TZ_OFFSET – Using DBTIMEZONE Example

Oracle TZ_OFFSET can be used with the SQL DBTIMEZONE to fetch the database time zone offset.

For example, the SQL TZ_OFFSET query below returns the timezone offset for the database.

SELECT TZ_OFFSET(DBTIMEZONE)
FROM DUAL;

May return something like “+00:00” depending upon the database timezone settings.

Oracle LOCALTIMESTAMP Function

by

SQL LocalTimeStamp FunctionOracle LOCALTIMESTAMP Function returns current date and time of the current SQL session.
Oracle LOCALTIMESTAMP returns the TIMESTAMP value.

Oracle LOCALTIMESTAMP Function Syntax

SELECT LOCALTIMESTAMP
FROM table_name;

Oracle LOCALTIMESTAMP returns date and time as set by the ALTER SESSION statement.

Oracle LOCALTIMESTAMP – Using SQL SELECT Statement Example

Oracle LOCALTIMESTAMP is used with SQL SELECT Statement to fetch current date and timestamp.

For example, the below SQL SELECT query will return current date and timestamp.

SELECT LOCALTIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:09:00.807000 AM”

Oracle LOCALTIMESTAMP – Using ALTER SESSION Example

We can use the ALTER SESSION SET TIME_ZONE to change the time zone of the database.

For example, the SQL SELECT query below will return the date and time after we have altered the database timezone.

ALTER SESSION SET TIME_ZONE = '-7:0';

And then we query the LOCALTIMESTAMP as

SELECT LOCALTIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:09:21.186000 AM” because the time zone has been altered

Oracle LOCALTIMESTAMP – Using TRUNC Function Example

LOCALTIMESTAMP Function can be used with the SQL TRUNC Function to remove the timestamp part.

For example, the SQL query below returns only the date part from Oracle LOCALTIMESTAMP Function.

SELECT TRUNCT(localtimestamp)
FROM dual;

May return something like “11/5/2012”.