oracle-databasedatetimetimezonesysdate

checking expiryDate in Oracle query

I have a table which contains the start date, ExpiryDate, I want to write an oracle query which checks if the expiry date is greater than the current system date, Then I want to return that row, else null will be the result of the query.

I wrote something like this,

 select Name,Password,StartDate,ExpiryDate from db_name where UserName = 'abc' and status =1 and ExpiryDate >=(SELECT Round((sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))*1000 as dt FROM dual);

Here is the table description:

 STARTDATE                 NOT NULL NUMBER(20)
 EXPIRYDATE                NOT NULL NUMBER(20)

The values:

EXPIRYDATE
----------
1.5880E+12

after performing query like select to_char(startdate),to_char(expirydate) I am getting

TO_CHAR(STARTDATE)
----------------------------------------
TO_CHAR(EXPIRYDATE)
----------------------------------------
1587909960000
1587996480000

But it is working fine for all cases, but if the expiry date is less than( the current time+6hrs) it is giving null, can anyone tell me how to solve this?

Solution

  • Unix epoch time is in the UTC time zone. You can convert the current time to UTC time zone and then subtract the epoch:

    SELECT Name,
       Password,
       StartDate,
       ExpiryDate
FROM   IM_USER_MANAGEMENT
WHERE  UserName = 'abc'
AND    status =1
AND    ExpiryDate >= ( CAST( SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE )
                       - DATE '1970-01-01'
                     )*24*60*60*1000