I need to compare between
userCurrentDate to a list of options.
The options is a table in the database that contain time attribute (definition: TIMESTAMP(6) WITH TIME ZONE. in oracle). Each option have his time zone.
userCurrentDate (definition Date. in java) is a parameter that hold the user time and date with offset. The user can be from all over the world, so each user is in different time zone.
I need to compare between the user current date to the options date. So i need to get this 2 parameters in the same time zone.
How can i get this 2 parameters in the same time zone, in SQL?
I tried to get them in GMT, so i can get them in the same base, like this:
trunc(SYS_EXTRACT_UTC(o.time)) = trunc(sysdate)
But its not a good solution.
So, i need to get them in the same timezone. how can i get that in an SQL command (or other solution)?
Why do you think you need this conversion? What version of Oracle and Oracle JDBC do you have?
Did you try just binding your
userCurrentDate and executing SQL, like this:
connection.prepareStatement("select * from options o where o.time = ?")
.setTimestamp(1, new Timestamp(userCurrentDate.getTime())
I've tested this with
ojdbc14.jar and Oracle 10g, and it works fine.
Timestamp data types do not have any time zone information within them, you shouldn't actually care about matching time zones with DB values as long as:
- You don't need your DB values to actually have specific time zone. When you insert some value into the column of type
TIMESTAMP WITH TIME ZONE, JDBC driver uses your local time zone.
- You don't need to display the original time zone in your Java program, since as I said before
Timestamptypes do not have that information in them.
As long as you just need to match your Java
Date objects to DB values, you should be fine without any conversion.
Also, please check this comprehensive article on the subject: [How To] Handle Oracle TimeStamp with TimeZone from Java
I resolve this problem with at time zone oracle function. used:
to_timestamp_tz(to_char(CAST(FROM_TZ(CAST(sysdate AS TIMESTAMP), 'GMT') AT TIME ZONE o.time AS DATE)
and compare it to each other.