SQL & PL/SQL

Hi All,

I have a requirement of comparing a calculated timestamp value with SYSTIMESTAMP. For the data structure shown below, I need to check if the SYSTIMESTAMP lies in the window between (Effective_End_Time + Expiration_reminder_period ) and (Effective_End_Time - Expiration_reminder_period ), then we I need to return true value. The expiration reminder type can have possible values as DAYS, MONTHS, WEEKS and the period is stored in the said column. I have already written a query using the responses on an earlier 2447657 .

DB Version: 11.2.0.3.0

Create table TESTEXPREMINDER 
(Expiration_reminder_type VARCHAR2(20),
expiration_reminder_period NUMBER(5),
effective_end_time TIMESTAMP(6)
);

Insert into TESTEXPREMINDER (EXPIRATION_REMINDER_TYPE,EFFECTIVE_END_TIME,EXPIRATION_REMINDER_PERIOD) values ('DAYS',to_timestamp('04-OCT-12 01.15.56.907000000 PM','DD-MON-RR HH.MI.SS.FF AM'),1);

Select systimestamp,
       effective_end_time,
       EFFECTIVE_END_TIME -  NUMTODSINTERVAL (EXPIRATION_REMINDER_PERIOD,'DAY') as calcTimeStamp,  
CASE EXPIRATION_REMINDER_TYPE 
WHEN 'DAYS' 
THEN  
  CASE  WHEN SYSTIMESTAMP BETWEEN (EFFECTIVE_END_TIME -  NUMTODSINTERVAL (EXPIRATION_REMINDER_PERIOD,     'DAY'))
                AND (EFFECTIVE_END_TIME + NUMTODSINTERVAL (EXPIRATION_REMINDER_PERIOD,     'DAY'))
        THEN 'TRUE' 
        ELSE 'FALSE' 
   END 
END AS IS_EXPIRING_TIME
FROM testexpreminder;

The above query returns the correct value when required.

1. The problem is that the query does not return the correct value if it is accessed from a location that has a different time zone than the DB server, although systimestamp returns the db server time.
2. Systimestamp value as returned by the select query has timezone information whereas the calcTimestamp does not. Could this cause any issue?
3. Which is the recommended way to compare timestamps?

Thanks,
Maithily