-
Need Help with Date Conversion to Session Timezone
I have an application that is used by users across the country. It reads from a table in a centrally located database. It needs to convert a standard date type field in the table to the timezone of the user.
I initially wrote a function, TO_STZ to convert a date to the sessiontimezone that essentially takes the difference between dbtimezone and sessiontimezone and adjust the date by the difference:
RETURN arg_date(+(to_number(substr(dbtimezone,2,2))-to_number(substr(sessiontimezone,2,2)/24
When the user issues the statement "SELECT TO_STZ(date_field) from TABLE;" the date in the table was converted as desired.
This worked fine up until Daylight Saving Time - at which point sessiontime changed by -1 hour but dbtimezone stayed the same (I didn't realize at the time that it was a constant offset from GMT)
I could ask my dba to change the dbtimezone - but this doesn't seem right & he would have to do it 2x a year.
Any suggestions on a function that does what I want?
Thanks, Marty
-
Got it...
CREATE OR REPLACE FUNCTION TO_STZ (f_date DATE) RETURN DATE IS
BEGIN
RETURN f_date+(extract(TIMEZONE_HOUR from
current_timestamp)-extract(TIMEZONE_HOUR from systimestamp))/24;
END TO_STZ;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|