Compare java Date to sql TIMESTAMP in the same timezone

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

-------------Problems Reply------------

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.

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

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.

Category:java Views:4 Time:2012-04-09
Tags: sql java timezone

Related post

  • Convert Java String to sql.Timestamp 2011-10-02

    Got a String coming in with this format: YYYY-MM-DD-HH.MM.SS.NNNNNN The Timestamp is coming from a DB2 database. I need to parse it into a java.sql.Timestamp and NOT lose any precison. So far I've been unable to find existing code to parse that far o

  • Java: Date from unix timestamp 2010-07-30

    I need to convert a unix timestamp to a date object. I tried this: java.util.Date time = new java.util.Date(timeStamp); Timestamp value is: 1280512800 The Date should be "2010/07/30 - 22:30:00" (as I get it by PHP) but instead I get Thu Jan 15 23:11:

  • Calculating and comparing a Date in SQL Server 2012-02-05

    I'm trying to figure out a way to take a date in a table in SQL, check if it's older than the value in another column and possibly output the difference. For Example: ColA has a standard SQL date stamp of 2011-12-12 04:10:00.000 ColB has a number in

  • Sorting and comparing Java Date objects produces unexpected results 2012-03-11

    I have a number of java Date objects which I am attempting to sort in ascending date order. These are loaded from a CSV using a simple date formatter in UK format "dd/mm/yy HH:ss", and output in the same format, e.g. new SimpleDateFormat("dd/mm/yy HH

  • Comparing effective dates in SQL 2008-08-22

    Wondering if there is a better why in the WHERE clause of choosing records when you need to look at effective start and end dates? Currently this how I've done it in the past on MS SQL Server. Just worried about the date and not the time. I'm using S

  • Compare Xml data in SQL 2011-09-15

    I have two tables with same NVARCHAR field that really contains XML data. in some cases this really-XML-field is really same as one row in other table but differs in attributes order and therefor string comparison does not return the correct result!!

  • Does the java date.before function take into account the timezone? 2011-06-10

    I have the timezone set on the server to BST (British Daylight Savings Time), and the date being passed into the function was calculated in UTC. On the Java Oracle site, it simply refers to comparing the time as "now", but will this "now" time be the

  • How to convert java.sql.Timestamp value from EST to UTC timezone 2011-07-25

    I have a java.sql.Timestamp variable in local timezone with following formatting 2011-07-21 00:40:37 I need to convert it to UTC I asked this question to see if it can be done by mysql, but received no answers. I guess I have to somehow do it in java

  • Javascript date comparison ignoring timestamp value 2012-02-15

    What is the simplest way to compare two dates neglecting the timestamps. I have got two dates firstDate coming from the database (converted into javascript date) and secondDate coming from a date picker input field. Essentially for my algorithm these

  • Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column? 2009-12-22

    I have a table with a DATE column with time (as usual in Oracle since there isn't a TIME type). When I query that column from JDBC, I have two options: Manually convert the values with Oracle's to_date() Use a java.sql.Timestamp Both approaches work

  • How to use java.sql.Timestamp as real java.util.Date with JPA 2010-10-25

    I have a problem about the management of dates with milliseconds. I understand the need to use the TIMESTAMP to store milliseconds: @Temporal(TIMESTAMP) @Column(name="DATE_COLUMN", nullable = false) @Override public java.util.Date getDate() { return

  • Before writing a Java Date to an SQL TIMESTAMP column, does JDBC translate the date from the JVM time zone to the database session time zone? 2010-11-08

    Before writing a Java Date to an SQL TIMESTAMP column, does JDBC translate the date from the Java virtual machine time zone to that of the database session? For example, suppose the Java virtual machine time zone is UTC and the database session time

  • Issue converting from java.util.Date to java.sql.Timestamp 2011-05-03

    I'm trying to put a user provided date into an SQL database, and I have the following lines to process the string and convert it to a java.sql.Timestamp object. SimpleDateFormat formatter = new SimpleDateFormat("yyyy-mm-dd'T'hh:mm"); java.util.Date b

  • conflict with java.util.Date and java.sql.TimeStamp 2011-07-20

    I am facing a unique problem. I am querying a database field which has a datatype (in oracle) as DATE . I am retrieving the value from resultSet as rs.getObject("myDate") . We have two setups which have identical configuration . On one setup when I w

  • java.sql.timestamp versus date 2012-01-16

    I am using Derby database with Java and Eclipse. I have a table which has a TIMESTAMP field and I use a model to populate a JTable from it. I am finding timestamp and data and java.sql and java.utils very confusing. The following line of code errors

  • Using a java.sql.Timestamp object in an sql query 2011-03-10

    I am trying to run a query in java that uses a java.sql.Timestamp object as the date to compare with in the where clause. Here is how the query string that is built in Java String getTransactionsSQL = "Select transaction_seq " + "From transactions ct

  • Why can't i compare java.util.Date to mysql dates? 2011-10-28

    i have a strange situation: I'm using jpa/hibernate to get rows from a mySql DB table where a date column is greater or equal to a date i send in (stripped out irrelevant code): SELECT sp.* FROM spaceproduct sp where sp.enddate is null or sp.enddate

  • How to force Hibernate to return dates as java.util.Date instead of Timestamp? 2012-03-02

    Situation: I have a persistable class with variable of java.util.Date type: import java.util.Date; @Entity @Table(name = "prd_period") @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class Period extends ManagedEntity implements

  • How to calculate the difference between two Java java.sql.Timestamps? 2009-02-24

    Please include the nanos, otherwise it would be trivial: long diff = Math.abs(t1.getTime () - t2.getTime ()); [EDIT] I want the most precise result, so no doubles; only integer/long arithmetic. Also, the result must be positive. Pseudo code: Timestam

Copyright (C), All Rights Reserved.

processed in 0.220 (s). 12 q(s)