Returning a date with time, when passed utc_date and timezone as input

Question

I have an Oracle table which has a date column ( say its name is start_date) that has stored date as UTC date. I have another column that stores a timezone (like 'America/Los_Angeles'). My requirement is I need to display the date column with timestamp corresponding to the timezone stored in the timezone column.

I initially wrote a function that accepts utc_date and the timezone and returns the date as below:

return utc_date +   (SUBSTR (TZ_OFFSET (timezone), 1, 1) || '1')
           * TO_DSINTERVAL (
                   '0 '
                || SUBSTR (TZ_OFFSET (timezone), 2, 5)
                || ':00');

but I realized a flaw. It calculates offset based on current time. So it now returns -00 08:00:00.000000 for Los_Angeles. But if the date stored in the utc_date was a date when daylight was enforced, the tz_offset value is not valid anymore. Can someone provide me some pointers how can I approach this problem?

rajeshrajan rajeshrajan · Accepted Answer · 2014-12-13T23:46:41

I found a solution to my problem. Instead of relying on TZ_OFFSET, I decided to do the following

return cast(from_tz(cast(utc_date as timestamp),'UTC') at time zone timezone as date);

This is returning me the desired date. If anyone see a flaw let me know

Returning a date with time, when passed utc_date and timezone as input

1 Answers