timezone - Oracle date compare broken because of DST -

we've been debugging issue sql query executed app server running java via hibernate. error:

[3/10/14 10:52:07:143 edt] 0000a984 jdbcexception w org.hibernate.util.jdbcexceptionreporter logexceptions sql error: 1878, sqlstate: 22008 [3/10/14 10:52:07:144 edt] 0000a984 jdbcexception e org.hibernate.util.jdbcexceptionreporter logexceptions ora-01878: specified field not found in datetime or interval 

we've been able narrow down simple sql below.

select *  my_table t t.my_timestamp >= (current_timestamp - interval '1' hour ); 

when run in same database, error:

ora-01878: specified field not found in datetime or interval 01878. 00000 -  "specified field not found in datetime or interval" *cause:    specified field not found in datetime or interval. *action:   make sure specified field in datetime or interval. 

the my_timestamp column defined timestamp(6).

fwiw, if change comparison in sql above >= <=, query works.

we assume has time change (we're in america/new_york) we're having problems trying figure out go here our debugging.

also, we've seen problem similar query that's running through mybatis , error looks like:

### error querying database.  cause: java.sql.sqlexception: ora-01878: specified field not found in datetime or interval  ### error may involve defaultparametermap ### error occurred while setting parameters ### cause: java.sql.sqlexception: ora-01878: specified field not found in datetime or interval 

update: teammate on windows changed windows date , time settings un-checking "automatically adjust clock daylight saving time" , opened new sqldeveloper instance. second instance able run query without issue first (with old dst setting) still fails.

to avoid error, consider using explicit cast of expression in clause timestamp type (timestamp without timezone), in way:

select *  my_table t t.my_timestamp >= cast(current_timestamp - interval '1' hour timestamp ); 

alternatively can explicitely set session time zone to, example '-05:00' - new york standard (winter) time,
using alter session time_zone = '-05:00', or setting ora_sdtz environment variable in client's environments,
see link details: http://docs.oracle.com/cd/e11882_01/server.112/e10729/ch4datetime.htm#nlspg263

depends on really stored in timestamp column in table, example timestamp 2014-07-01 15:00:00 represents in fact, "winter time" or "summer time" ?

---

current_timestamp function returns value of datatype timestamp time zone
see link: http://docs.oracle.com/cd/b19306_01/server.102/b14200/functions037.htm

while comparing timestamps , dates, oracle implicitely converts data more precise data type using session time zone !
see link --> http://docs.oracle.com/cd/e11882_01/server.112/e10729/ch4datetime.htm#nlspg251

in our particular case, oracle casts timestamp column timestamp time zone type.

oracle determines session timezone client environment.
can determine current session timezone using query:

select sessiontimezone dual; 

for example on pc (win 7), when option ""automatically adjust clock daylight saving time" checked, query returns (under sqldeveloper):

sessiontimezone                                                            --------------- europe/belgrade  

when uncheck option in windows , restart sqldeveloper, gives:

sessiontimezone                                                            --------------- +01:00      

the former session timezone timezone region name, oracle uses daylight saving time rules region in date calculations:

alter session set time_zone = 'europe/belgrade'; select cast( timestamp '2014-01-29 01:30:00' timestamp time zone ) x,        cast( timestamp '2014-05-29 01:30:00' timestamp time zone ) y dual;  session set altered. x                            y                           ---------------------------- ---------------------------- 2014-01-29 01:30:00 europe/b 2014-05-29 01:30:00 europe/b  elgrade                      elgrade        

latter timezone uses fixed offset "+01:00" (always "winter time"), , oracle not apply dst rules it, adds fixed offset.

alter session set time_zone = '+01:00'; select cast( timestamp '2014-01-29 01:30:00' timestamp time zone ) x,        cast( timestamp '2014-05-29 01:30:00' timestamp time zone ) y dual;  session set altered. x                            y                           ---------------------------- ---------------------------- 2014-01-29 01:30:00 +01:00   2014-05-29 01:30:00 +01:00   

---

please note, curiosity's sake, y results in above represent 2 different times !!!
014-05-29 01:30:00 europe/belgrade not same as: 2014-05-29 01:30:00 +01:00

this:
014-05-29 01:30:00 europe/belgrade equal to: 2014-05-29 01:30:00 +02:00

above make aware of how simple "box un-checking" affect queries, , dig reason when users complain "this query worked fine in january, gave wrong results in july".

---

and still on topic of ora-01878 - let session europe/warsaw , table containts timestamp (without time zone)

'timestamp'2014-03-30 2:30:00' 

note in region dst change, in 2014 year, occurs on 30 of march @ 2:00 a.m.
means on march 30, @ 2:00 @ night, must wake , shift watch forward 2:00 3:00 ;)

alter session set time_zone = 'europe/warsaw'; select cast( timestamp'2014-03-30 2:30:00' timestamp time zone ) x dual;  sql error: ora-01878: podane pole nie zostało znalezione w dacie-godzinie ani w interwale 01878. 00000 -  "specified field not found in datetime or interval" *cause:    specified field not found in datetime or interval. *action:   make sure specified field in datetime or interval. 

oracle knows, timestamp is not valid in region according dst rules, because there no time 2:30 on 30 of march - @ 2:00 clock moved 3:00, , there no time 2:30. therefore oracle throws error ora-01878.

query works fine:

alter session set time_zone = '+01:00'; select cast( timestamp'2014-03-30 2:30:00' timestamp time zone ) x dual;  session set altered. x                           ---------------------------- 2014-03-30 02:30:00 +01:00  

and reason of error - table contains timestamps 2014-03-09 2:30 or (for new york, dst shifts occur on 9 of march , 2 of november), , oracle doesn't know how convert them timestamp (without tz) timestamp tz.

---

the last question - why query >= doesn't work, query <= works fine ?

work/don'n work, because sqldeveloper returns first 50 rows (maybe 100 ? depends on settings). query doesn't read whole table, stops when first 50(100) rows fetched.
change "working" query to, example:

select sum( extract(hour my_timestamp) ) my_table  my_timestamp <= (current_timestamp - interval '1' hour ); 

this force query read rows in table, , error appear, i'am 100% sure.