Cannot get the row using BETWEEN in dates correctly with pl/sql

Question

I am trying return a row with a between '2015/06/15 7:00:00' and '2015/06/15 15:00:00' and the exact date of that row is 2015/06/15 07:32:39 so it should be between those two dates.

I was trying to use below:

 SELECT * from table1 where  (TO_DATE(TO_CHAR(NEW_TIME(TO_DATE('1970/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') + (order_date / 86400), 'PST', 'GMT'), 'mm/dd/yyyy'), 'mm/dd/yyyy') > TO_DATE('06152015 07:00:00', 'mmddyyyy hh24:mi:ss')
AND TO_DATE(TO_CHAR(NEW_TIME(TO_DATE('1970/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') + (order_date/ 86400), 'PST', 'GMT'), 'mm/dd/yyyy'), 'mm/dd/yyyy') < TO_DATE('06152015 14:00:00', 'mmddyyyy hh24:mi:ss'))

I could not achieve it using the above query but when I change "06152015 07:00:00' into "06142015 07:00:00" it returns the row correctly. Can you help me fix what is wrong with the query above?

Answer 1

try this.
NEW_TIME(date,'timezone1', 'timezone2') -> convert date from timezone1 to timezone2

SELECT * from table1 where  
NEW_TIME(TO_DATE('1970/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') + (order_date / 86400), 'PST', 'GMT') 
 > NEW_TIME(TO_DATE('06152015 07:00:00', 'mmddyyyy hh24:mi:ss'), 'PST', 'GMT')

AND 
NEW_TIME(TO_DATE('1970/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') + (order_date/ 86400), 'PST', 'GMT')