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:
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?
CREATE OR REPLACE FUNCTION TO_STZ (f_date DATE) RETURN DATE IS
RETURN f_date+(extract(TIMEZONE_HOUR from
current_timestamp)-extract(TIMEZONE_HOUR from systimestamp))/24;
Click Here to Expand Forum to Full Width