How to Get Date Values in the Session Time Zone Using SQL?

Goal

Retrieve the date values based on session time zone instead of database or server time zone.

Solution

Convert the date values from data base / server time zone to Session time zone.

Date Time

The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

TIMESTAMP WITH TIME ZONE also includes these fields:

• TIMEZONE_HOUR
• TIMEZONE_MINUTE
• TIMEZONE_REGION
• TIMEZONE_ABBR

TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM or TZR TZD format elements are specified.

Date Time Stamp SQL Functions

1. SYS_EXTRACT_UTC

Purpose

SYS_EXTRACT_UTC extracts the UTC (Coordinated Universal Time, formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name. If a time zone is not specified, then the datetime is associated with the session time zone.

Examples

The following example extracts the UTC from a specified datetime:

SQL1

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2019-04-01 11:30:00.00 -07:00') NEW_VALUE

  FROM DUAL;

SQL1 Output

2. FROM_TZ

Purpose

FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. TIME_ZONE_VALUE is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.

Examples

The following example returns a timestamp value to TIMESTAMP WITH TIME ZONE:

SQL2

SELECT FROM_TZ(TIMESTAMP '2018-04-01 11:30:00', '-7:00') NEW_VALUE

  FROM DUAL;

SQL2 Output

3. NEW_TIME

Purpose

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time. The return type is always DATE, regardless of the datatype of date.

Examples

The following example returns a Mountain Day Light Saving Time, given the Greenwich Mean Time  equivalent:

SQL3

SELECT SYSDATE GMT_SYSDATE,

       NEW_TIME(SYSDATE, 'GMT', 'MDT') MDT_SYSDATE

  FROM DUAL;

SQL3 Output

SYSDATE (User Time Zone) SQL

SQL4

SELECT TO_CHAR(NEW_TIME(SYSDATE, 'GMT', TO_CHAR( FROM_TZ (SYS_EXTRACT_UTC (SYSTIMESTAMP),'UTC') AT TIME ZONE FND_PROFILE.VALUE ('FND_TIMEZONE'),'TZD')), 'MM-DD-YYYY') SYSDATE_SESSION_TZ

  FROM DUAL

SQL5

SELECT SYSDATE SYSDATE_TIMESTAMP_SERVER,

       TO_CHAR(SYSDATE, 'MM-DD-YYYY') SYSDATE_SERVER,

       FND_PROFILE.VALUE('FND_TIMEZONE') FND_TZ,

       TO_CHAR(FROM_TZ(SYS_EXTRACT_UTC(SYSTIMESTAMP),'UTC') AT TIME ZONE FND_PROFILE.VALUE('FND_TIMEZONE'),'TZD') SESSION_TZ,

       TO_CHAR(NEW_TIME(SYSDATE, 'GMT', TO_CHAR(FROM_TZ(SYS_EXTRACT_UTC(SYSTIMESTAMP),'UTC') AT TIME ZONE FND_PROFILE.VALUE('FND_TIMEZONE'),'TZD')), 'MM-DD-YYYY') SYSDATE_SESSION_TZ

  FROM DUAL

SQL5 Output

Oracle Support Documents

1. How to Find User Time Zone (Doc ID 2348229.1)
2. Timestamps & Time Zones - Frequently Asked Questions (Doc ID 340512.1)
3. Fusion Core HR : HCM OTBI Time Zone Issue (Doc ID 2517748.1)
4. Export Manage Admin Profile Value For FND_TIMEZONE (Doc ID 2262334.1)
5. TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) Data and DST Updates (Doc ID 756454.1)
6. Fusion BI Publisher: Need local time as report run time in BI Publisher report but it shows UTC time zone (Doc ID 2168341.1)

How To Exclude the BI Report’s Schedule Frequency From Holidays

BI Report scheduling frequency can be skipped based on the localized public holidays or weekends using by report triggers.

Pre-Requisites

Public holidays need to be captured in the Fusion Cloud applications’ Calendar Events.

Step 1: Calendar Events

Navigation: Setup and Maintenance > Manage Calendar Events

Step 2: SQL for Calendar Events

SELECT NAME,
       TO_CHAR(START_DATE_TIME, 'MM/DD/YYYY') START_DATE,
       TO_CHAR(END_DATE_TIME, 'MM/DD/YYYY') END_DATE,
       SHORT_CODE
  FROM PER_CALENDAR_EVENTS_VL
 WHERE CATEGORY = 'PH'
 ORDER BY START_DATE

Step 3: Add Event Triggers in Data Model

Schedule Triggers

A schedule trigger fires when a report job is scheduled to run. Schedule triggers are of type SQL Query. When a report job is scheduled to run, the schedule trigger executes the SQL statement defined for the trigger. If data is returned, then the report job is submitted. If data is not returned from the trigger SQL query, the report job is skipped.

SQL Query

SELECT 'TRUE' FROM DUAL

WHERE NOT EXISTS

(

-- Public Holidays

SELECT 'HOLIDAY' HOLIDAY

  FROM PER_CALENDAR_EVENTS_VL

 WHERE 1=1

   AND CATEGORY = 'PH'

   AND TO_CHAR(:P_RUN_DATE,  'MM/DD/YYYY') BETWEEN TO_CHAR(START_DATE_TIME, 'MM/DD/YYYY') AND TO_CHAR(END_DATE_TIME, 'MM/DD/YYYY')

 UNION 

-- Weekends  

SELECT 'HOLIDAY' 

  FROM DUAL 

 WHERE TO_CHAR(:P_RUN_DATE, 'D') IN (1,7)

)

The above query will exclude the weekends and public holidays.

Documents Reference

Fusion Global HR: How To Setup Calendar Events By Locations (Doc ID 1917689.1)