I'd like to know if Oracle is able to compare dates with different Timezones, as in:
12/26/2016 3:58:16.491476 AM -06:00 > 12/26/2016 3:58:16.491476 AM +05:00
Btw, I'm using JPA to do this comparison, the idea would be to look for all the rows created an hour ago.
I found that I might be able to use the After keyword to look for it (i.e. findMeasureDateAfter)
Very easy to check in Oracle. The answer is YES. Please note, in the illustration below the output shows timestamps using my session's NLS settings (I didn't care to change them).
with
inputs ( ts1, ts2 ) as (
select to_timestamp_tz('12/26/2016 3:58:16.491476 AM -06:00',
'mm/dd/yyyy hh:mi:ss.ff AM TZH:TZM'),
to_timestamp_tz('12/26/2016 3:58:16.491476 AM +05:00',
'mm/dd/yyyy hh:mi:ss.ff AM TZH:TZM') from dual
)
select ts1, ts2, case when ts1 > ts2 then 'ts1 > ts2'
when ts1 = ts2 then 'ts1 = ts2'
when ts1 < ts2 then 'ts1 < ts2'
end as comparison,
ts1 - ts2 as difference
from inputs
;
TS1 TS2 COMPARISON DIFFERENCE
----------------------------- ----------------------------- ---------- -------------------
26-DEC-16 03.58.16.491 AM -06 26-DEC-16 03.58.16.491 AM +05 ts1 > ts2 +00 11:00:00.000000
If you are pulling the data from an Oracle table based on a predicate like this, it is much better to do that work in the database - so how this would be done in Java is irrelevant. (You certainly don't want to fetch all the rows, only to ignore most of them after you check the timestamp in Java.) Of course, if you need "the last hour" you would compare against systimestamp - 1/24.
First, if those inputs are actually strings as presented in the Question, use standard ISO 8601 formats instead if at all possible. The standard formats are intuitive to humans and easier to parse by computers. Indeed, the java.time classes use ISO 8601 formats by default when parsing/generating strings.
While I do not know about the the query in Oracle (I'm a Postgres man myself), I can show how to form the query more on the Java side.
Ideally we would parse that input string as a OffsetDateTime as it lacks an indication of time zone, only has offset-from-UTC. A zone is an offset plus a set of rules for handling anomalies such as Daylight Saving Time (DST). A time zone is named in format of continent/region such as America/Montreal.
Unfortunately, the java.time implementation in Java 8 has some bugs around parsing offset-from-UTC in the DateTimeFormatter class. So until Java 9, here is a bit of hack code to parse as a ZonedDateTime and convert to the more appropriate OffsetDateTime.
String input = "12/26/2016 3:58:16.491476 AM -06:00";
DateTimeFormatter f = DateTimeFormatter.ofPattern ( "MM/dd/uuuu h:m:s.SSSSSS a z" , Locale.ENGLISH );
OffsetDateTime odt = ZonedDateTime.parse ( input , f ).toOffsetDateTime ();
odt.toString(): 2016-12-26T03:58:16.491476-06:00
Repeat for your ending moment.
If your JDBC driver supports JDBC 4.2 or later, you may be able to pass these java.time types directly via PreparedStatement::setObject.
If not, convert to java.sql types. To convert, look to new methods added to the old classes. The from method takes an Instant which is a moment on the timeline in UTC. You can think of an Instant as a OffsetDateTime stripped of its offset. Call OffsetDateTime::toInstant to extract an Instant.
java.sql.Timestamp ts = java.sql.Timestamp.from( odt.toInstant() ) ;
Do this for both your beginning and ending moments. Pass these java.sql.Timestamp objects to your PreparedStatement.
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
