SQL Query to Find Out Timezone Information from Oracle Database

advertisements

_____________________________________________________________________________________________________________________

Query 1

To view timezone information in an Oracle database, you can use the DBTIMEZONE and SESSIONTIMEZONE functions along with the FROM_TZ and TO_TIMESTAMP_TZ functions. 

Here's a SQL query to achieve this:

SELECT
  SYSTIMESTAMP AS current_timestamp,
  DBTIMEZONE AS db_timezone,
  SESSIONTIMEZONE AS session_timezone,
  FROM_TZ(TIMESTAMP '2023-07-06 12:34:56', 'America/New_York') AS timestamp_with_tz,
  TO_TIMESTAMP_TZ('2023-07-06 12:34:56 -04:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS timestamp_tz_literal
FROM
  DUAL;

advertisements

 

Description: 

SYSTIMESTAMP: Returns the current date and time of the database, including the timezone. 

DBTIMEZONE: Returns the timezone of the database. 

SESSIONTIMEZONE: Returns the current session's timezone. 

FROM_TZ: Converts a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value. 

In this example, it converts the given timestamp '2023-07-06 12:34:56' to the timezone 'America/New_York'. 

TO_TIMESTAMP_TZ: Converts a TIMESTAMP WITH TIME ZONE literal to a TIMESTAMP WITH TIME ZONE value. In this example, it converts the literal '2023-07-06 12:34:56 -04:00' to the corresponding timestamp value with the timezone offset.

CURRENT_TIMESTAMP                  | DB_TIMEZONE | SESSION_TIMEZONE | TIMESTAMP_WITH_TZ            | TIMESTAMP_TZ_LITERAL
----------------------------------|-------------|------------------|------------------------------|---------------------------
06-JUL-23 12.34.56.920000 PM UTC   | +00:00      | +00:00           | 06-JUL-23 12.34.56.000000 PM | 06-JUL-23 12.34.56.000000 PM -04:00

In the sample output, CURRENT_TIMESTAMP represents the current timestamp of the database, DB_TIMEZONE shows the timezone of the database, SESSION_TIMEZONE represents the timezone of the current session, TIMESTAMP_WITH_TZ is the converted timestamp with timezone, and TIMESTAMP_TZ_LITERAL is the timestamp from the provided literal with the corresponding timezone offset. 

Query 2. The V$TIMEZONE_FILE view in Oracle provides information about the timezone files installed in the database. Here's a SQL query to view timezone information using the V$TIMEZONE_FILE view:

SELECT
  SYSTIMESTAMP AS current_timestamp,
  DBTIMEZONE AS db_timezone,
  SESSIONTIMEZONE AS session_timezone,
  FROM_TZ(TIMESTAMP '2023-07-06 12:34:56', 'America/New_York') AS timestamp_with_tz,
  TO_TIMESTAMP_TZ('2023-07-06 12:34:56 -04:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS timestamp_tz_literal,
  FILENAME,
  VERSION,
  CON_ID
FROM
  V$TIMEZONE_FILE;

Description: SYSTIMESTAMP: Returns the current date and time of the database, including the timezone. DBTIMEZONE: Returns the timezone of the database. SESSIONTIMEZONE: Returns the current session's timezone. FROM_TZ: Converts a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value. In this example, it converts the given timestamp '2023-07-06 12:34:56' to the timezone 'America/New_York'. TO_TIMESTAMP_TZ: Converts a TIMESTAMP WITH TIME ZONE literal to a TIMESTAMP WITH TIME ZONE value. In this example, it converts the literal '2023-07-06 12:34:56 -04:00' to the corresponding timestamp value with the timezone offset. FILENAME: Provides the name of the timezone file. VERSION: Indicates the version of the timezone file. CON_ID: The container ID. In a non-CDB database, this column has a value of 0.

CURRENT_TIMESTAMP                  | DB_TIMEZONE | SESSION_TIMEZONE | TIMESTAMP_WITH_TZ            | TIMESTAMP_TZ_LITERAL         | FILENAME                          | VERSION | CON_ID
----------------------------------|-------------|------------------|------------------------------|------------------------------|----------------------------------|---------|-------
06-JUL-23 12.34.56.920000 PM UTC   | +00:00      | +00:00           | 06-JUL-23 12.34.56.000000 PM | 06-JUL-23 12.34.56.000000 PM -04:00 | timezlrg_40.dat                    | 40      | 0
06-JUL-23 12.34.56.920000 PM UTC   | +00:00      | +00:00           | 06-JUL-23 12.34.56.000000 PM | 06-JUL-23 12.34.56.000000 PM -04:00 | timezlrg_40.dat                    | 40      | 1
06-JUL-23 12.34.56.920000 PM UTC   | +00:00      | +00:00           | 06-JUL-23 12.34.56.000000 PM | 06-JUL-23 12.34.56.000000 PM -04:00 | timezlrg_40.dat                    | 40      | 3

_____________________________________________________________________________________________________________________

Website Stats