Oracle Sql Query Needs To Change Based On Timezone

I have a sql query to obtainrecords for a date range. My query works find byut when I analyzed data, I found that the records are retirved base don GMT timezone value, thus making

Solution 1:

Found the answer to my question.

apparently, you need to consider the time zone settings when you calculate epoch value as well

1. add 10 hours (10*60*60*1000 milliseconds) to epoch value - epoch current value is in GMT so to make it EST (GMT+10), I added this.

2. Used TO_TIMESTAMP_TZ instead of to_date

   SELECT tableA.columnA,tableB.columnB 
   FROM tableA INNERJOIN tableB ON  tableA.aId = tableB.aId 
   WHERE (TO_TIMESTAMP_TZ('1970-01-01 00:00:00 +10:00','yyyy-MM-dd hh24:mi:ss TZH:TZM') +    ((tableB.epochValue+(10*60*60*1000))/60/60/24/1000)) >  to_date('##FROM_DATE## +10:00', 'yyyy-MM-dd hh24:mi:ss TZH:TZM') 
   AND (TO_TIMESTAMP_TZ('1970-01-01 00:00:00 +10:00','yyyy-MM-dd hh24:mi:ss TZH:TZM') + ((tableB.epochValue+(10*60*60*1000))/60/60/24/1000)) <= to_date('##TO_DATE## +10:00', 'yyyy-MM-dd hh24:mi:ss TZH:TZM');