I want to update a database table which uses date type to timestamp with timezone
type in such a way that the old dates get correct timezone information.
The plain cast is not good for me because if the time zone is for example UTC+2 hours (UTC+1 + 1 hour for daylightsaving) and I try to cast dates to timestamp with timezone
, all the dates in the database table got the same +2 hour as timezone offset, regardless if it's a summer time or winter time date.
I already can write an SQL query which can determine about a date if it is in daylightsaving time or not, IF I know the current time zone in string format, e.g. 'Europe/Berlin'. The problem is that dbtimezone and sessiontimezone can be stored in other formats, too (+02:00, CET, etc). I cannot easily set the current sessiontimezone in a static way, because there are customers in several places on the globe with their own databases, but using a common update script.
Express method for timestamp can not help neither, because it cannot map the offset to named time zones.
I've seen a solution which uses java stored procedure to get the OS’s timezone instead of Oracles timezone. Unfortunately we use Oracle 12c, which contains an older JRE (I think it's 1.6 version). So, although Java 1.8 handles the timezones and daylight saving well (it uses updated tzmapping table), it does not work for me. I tryed it and if I run a test from Netbeans, then it gives me back the right time zone ID (in Europe/Berlin format), but even if it is accepted by Oracle SQL Developer, SQLPlus (which we use for running update scrips), it displays only +02:00.
I've tryied to use JodaTime (recompiled onto Java 1.6 in order to be accepted by SQL*Plus). The latest JodaTimes uses its own mapping table in theory. I read here on StackOverflow that if it cannot gather the time zone from user.timezone
variable, then it turns to java.util
, which is not good, as I mentioned. And if it does not succeed, then uses UTC. But it's not clear to me why it cannot get timezone from user.timezone
systemm variable. Is it a permission problem maybe?
Or how could I possibly solve this issue? Thank you!
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…