Hello,
Using Oracle 11g R2.
I've noticed that when I insert a systimestamp value into a table's column with a datatype of timestamp, it appears different than
systimestamp.
Query:
{
select order_received_ts from intraday_order where intraday_order_id = 17777
union all
select systimestamp from dual
}
Result:
12/12/2013 12:18:22.655613 PM -07:00
12/12/2013 12:26:23.914487 PM -05:00
Since I need to calculate the difference in seconds between the current systimestamp value and the table's column value, I'm finding that I need to subtract -2/24 from the column value in order to get accurate results.
WHERE clause which displays records that are equal to or exceed the maximum time limit.
{
extract(day from (systimestamp - (nvl(io.order_resubmitted_ts, io.order_received_ts)-2/24))) * 24 * 60 * 60
+ extract(hour from (systimestamp - (nvl(io.order_resubmitted_ts, io.order_received_ts)-2/24))) * 60 * 60
+ extract(minute from (systimestamp - (nvl(io.order_resubmitted_ts, io.order_received_ts)-2/24))) * 60
+ extract(second from (systimestamp - (nvl(io.order_resubmitted_ts, io.order_received_ts)-2/24))) >= isr.upper_thresh_sla
}
Could someone please help me understand why I need to do this extra subtraction (-2/24)?