Clash Royale CLAN TAG

Oracle session timezone: Can Oracle DB session convert java.sql.Date to correct timezone?

We have an audit table( Columns/Types: ID/Number,.. Audited_Date/Date) which logs audit entries using prepared statements. Until now, for different contexts we set the database session timezone for the connection, after which we were using the CURRENT_DATE attribute for the audited_date column. THIS MEANT THAT THE DATE INSERTED INTO THE COLUMN IS IN THE TIMEZONE OF THE CONNECTION WHICH IS IMPORTANT.

Now, we have a new requirement to add different dates based on the supplied timestamps for the audit logs. Similar to the previous approach where the auditing engine didn't have to worry about the timezone, is there a way to set the date for the column, WITHOUT having to do something like this:

TimeZone timeZone = TimeZone.getTimeZone(timezone);
calendar.setTimeZone(timeZone);
preparedStatement.setDate(4, new java.sql.Date(userTimestampMillis), calendar);


I would really like NOT to do this because the timezone attribute is decided based on multiple attributes like system environments, and other parameters. The application uses ALTER SESSION SET TIME_ZONE="CONTEXT_TIMEZONE" in a global scope of the application where connections are fetched from.
Is there any way to let the DB session handle the timezone conversion?

These two approaches fail to convert the the timestamp to the DB session timezone. If i'm not wrong, they are using the JVM timezone.

FAIL1.

Timestamp timestamp = new Timestamp(userTimestampMillis);
preparedStatement.setTimestamp(4, timestamp);


FAIL2.

preparedStatement.setObject(4, new java.sql.Date(userTimestampMillis));


Any documentation is greatly appreciated.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.