2

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.

The 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)?

lolo
  • 17,392
  • 9
  • 25
  • 49
  • 1
    I believe the At TIME ZONE function is what you want, as described here http://stackoverflow.com/questions/9216832/querying-oracle-timestamp-with-timezone – Sarah Haskins Apr 09 '12 at 12:53
  • 1
    Please be aware that Date objects don't handle timezones. It keeps track of time as milliseconds passed since 01/01/70 in GMT only. You might want to use Calendar if you need to have some time based logic in your app. – Morfic Apr 09 '12 at 17:26
  • @Grove, Please be aware that i wrote that the useCurrentDate hold the user time and date with **offset** – lolo Apr 10 '12 at 05:55

2 Answers2

0

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())
    .executeQuery();

I've tested this with ojdbc14.jar and Oracle 10g, and it works fine.

Since Date and 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:

  1. 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.
  2. You don't need to display the original time zone in your Java program, since as I said before Date / Timestamp types 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

Andrey Balaguta
  • 1,308
  • 2
  • 21
  • 28
  • In my code, they have time zone. this time zone is important to my data base. o.time is a "list" and i need to compare each one of them to ? – lolo May 09 '12 at 15:56
0

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.

lolo
  • 17,392
  • 9
  • 25
  • 49