I have a table with a column filled with unix timestamps (how many seconds from whatever 1970) When people access that field it is not taking into account daylight saving time. Originally I was tasked with writing a huge case statement that would subtract an hour based on the time of year. I did a little digging and found the TO_TIMESTAMP_TZ function and read that it will handle all daylight saving time adjustments. So i set my timezone and restarted the db.
SQL> select dbtimezone from dual;
Then I made my TO_TIMESTAMP_TZ statement, 2 of them one a couple days before the DST change and one a couple days after, thinking that the 2nd one would display and hour before, but no change.
hmmm... i see your point about doing the math after the timestamp conversion. i was doing it there because someone else tested it and found that if you did math in it the procedure returned a date instead of timestamp and the DST wasnt taken into account. my next guess would be do the math in a to_date and assign that to a variable and pass that variable to a to_timestamp_tz.
what i am hoping is if i pass Apr 04 2005 09:00 to a to_timestamp_tz oracle would return the time being 08:00 because it the date is in daylight daving time.