checking expiryDate in Oracle query

Question

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?

What is the datatype of `expiry_date`? Please give a few examples of the data it contains. — GMB, Apr 27 '20 at 09:50

score 1 · Accepted Answer · answered Apr 27 '20 at 11:12

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

score 0 · Answer 2 · answered Apr 27 '20 at 10:45

Unix epoch time, eh? See if this helps.

Set date format to something recognizable:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

Sample data:

SQL> select * From test;

             STARTDATE             EXPIRYDATE
---------------------- ----------------------
         1587909960000          1587996480000

Converted to DATE values:

SQL> select
  2    date '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * startdate  startdt,
  3    date '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * expirydate expdt
  4  from test;

STARTDT             EXPDT
------------------- -------------------
26.04.2020 14:06:00 27.04.2020 14:08:00

Or, using it along with sysdate:

SQL> select *
  2  from test
  3  where sysdate between
  4    date '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * startdate and
  5    date '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * expirydate;

             STARTDATE             EXPIRYDATE
---------------------- ----------------------
         1587909960000          1587996480000

As sysdate currently is:

SQL> select sysdate from dual;

SYSDATE
-------------------
27.04.2020 12:45:56

O. Jones · Answer 3 · 2020-04-27T11:12:51.880

It looks to me like these dates of yours are Javascript style timestamps. That is, it looks like they are times since the UNIX epoch 1970-01-01 00:00:00 UTC measured in milliseconds. Notice they're with reference to UTC, not your local time zone. Is your time zone Asia/Dhaka? That's the one six hours ahead of UTC.

It also looks to me like your timestamps have ten-second precision. The two you showed are divisible by 10 000.

This is the formula for converting Javascript times to Oracle UTC date/time values

 SELECT TO_DATE('19700101','yyyymmdd') + (1587909960000/86400000) FROM DUAL;

This yields a SYSDATE - style rendering of your values in UTC time, not local time. It yields 2020-04-26 14:06:00

Because you have a six-hour apparent error, I guess your local time zone is Asia/Dhaka, UTC+6. But it also could possibly be America/Denver, UTC-6.

and your time value, run through that formula, yields 2020-04-26 14:06:00. Which seems like a valid recent date/time.

This is a GUESS! If you're working with other peoples' money or lives in your database, ask the person who programmed it. It's not a DBMS-native way of doing things, so you should double-check.

What's going on in the formula?

In Oracle, adding 1.0 to a SYSDATE - style value adds one calendar day to it. So we start with the Oracle date for the UNIX epoch TO_DATE('19700101','yyyymmdd').

Then we take your millisecond timestamp value and convert it to days, dividing by 86 400 000, Finally we add it to the epoch date.

Here are some suggestions about getting the current time in UTC, so you can compare it to your timestamp data. How to get UTC value for SYSDATE on Oracle

checking expiryDate in Oracle query

3 Answers3