Oracle - compare date column to sysdate

0
votes

I have a talbe with a nvarchar2 column which contains date. (lets called it my_date - pattern: DD-MM-YYYY HH24:MI:SS) column type is number(19) and the value inside is epoch time which i convereted like this:

select TO_CHAR(FROM_TZ(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'DD-MM-YYYY HH24:MI:SS') as my_date
from my_table

I want to compare it with sysdate but for unknown reason it doesnt seems to be working.

select my_date
from my_table
where mydate >= TO_CHAR(sysdate-(1/24),'DD-MM-YYYY HH24:MI:SS')

I also did:

select dump (TO_CHAR(sysdate-(1/24),'DD-MM-YYYY HH24:MI:SS')) from my_table
select dump (my_date) from my_table

On both cases i got "typ=1"

Any Ideas?

Thanks in Advance.

To make it more clear: the starting value in my_date is = 1580801246921 using this website to understand it's value to make sure i converted it correctly: https://www.epochconverter.com/

3
oracle
You shouldn't store DATE values in a varchar column to begin with. Do you have the possibility to fix that? - a_horse_with_no_name
sadly i dont, its a data that being stored in the DB from some other product... the actual data inside that column is epoch time which i converted and now trying to compare the date inside of it to sysdate - Tal
Epoch time stored how - actually as a string or as a number; and how are you getting from whatever actual value you have to the 'formatted' value you refer to? (There are lots of questions already about how to convert from epoch/'Unix' time to an Oracle date.) Please include some sample data and the relevant table structure )actual data types etc.) in your question. - Alex Poole
Thanks, but you're referring to my_date as both a number and a string; and it would be helpful to show raw (number) values, what those actually convert to, and also the sysdate - or preferably systimestamp as you're creating a timestamp not a date; and you don't need to convert that to a string at all if you're comparing it with another date/timestamp. - Alex Poole
I've added more info on the post, this is that my value isnt a string, its a char also approved with dump command. can you try to explain what exactly do you mean? - Tal

3 Answers

1
votes

You can run it simply like this:

select my_date
from my_table
where (TIMESTAMP '1970-01-01 00:00:00 UTC' + my_date * INTERVAL '1' SECOND) >= SYSTIMESTAMP - INTERVAL '1' DAY

There is no need to convert the time to your local time. Comparisons of TIMESTAMP WITH TIME ZONE are always performed internally on UTC times.

In order to get sufficient performance I would recommend to create a function:

CREATE OR REPLACE FUNCTION UnixTime2LocalTime(UnixTime IN NUMBER) RETURN TIMESTAMP DETERMINISTIC IS
BEGIN
    RETURN (TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixTime * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York';
END UnixTime2LocalTime;
/

Then add a virtual column to your table as

 LOCAL_TIME TIMESTAMP(0)  GENERATED ALWAYS AS ( UnixTime2LocalTime(my_date ) ) VIRTUAL

After that you can also create an index on the virtual column.

0
votes

If MYDATE is a string, you should convert it to DATE, not vice versa. Strings aren't the best choice for comparing them with > or <. For example, '9' is larger than '20'.

where to_date(mydate, 'dd-mm-yyyy hh24:mi:ss') >= sysdate - 1/24

On the other hand, if there was an index in MYDATE column, TO_DATE against it will make that index unusable (unless you choose to create a function based index).

That's the price you have to pay when storing date values as strings. Why did you do that? If possible, change column's datatype to DATE.

0
votes

You don't need to treat either your converted table value or the current date as strings. It might be helpful to see what the stages of your conversion produce:

-- just for brevity
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZR';

create table my_table (my_date)
as select 1580801246921 from dual;

select my_date as epoch,
  DATE '1970-01-01' + (1/24/60/60/1000) * my_date as plain_date,
  CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP) as plain_ts,
  FROM_TZ(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP), 'UTC') as utc,
  FROM_TZ(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' as est
from my_table;

        EPOCH PLAIN_DATE          PLAIN_TS                UTC                         EST                                     
------------- ------------------- ----------------------- --------------------------- ----------------------------------------
1580801246921 2020-02-04 07:27:27 2020-02-04 07:27:27.000 2020-02-04 07:27:27.000 UTC 2020-02-04 02:27:27.000 AMERICA/NEW_YORK

Or a bit more simply, using timestamps and intervals:

select my_date as epoch,
  TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND as plain_ts,
  FROM_TZ(TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND, 'UTC') as utc,
  FROM_TZ(TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND, 'UTC') AT TIME ZONE 'America/New_York' as est
from my_table;

        EPOCH PLAIN_TS                UTC                         EST                                     
------------- ----------------------- --------------------------- ----------------------------------------
1580801246921 2020-02-04 07:27:26.921 2020-02-04 07:27:26.921 UTC 2020-02-04 02:27:26.921 AMERICA/NEW_YORK

or more simply still:

select my_date as epoch,
  TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND as utc,
  (TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York' as est
from my_table;

        EPOCH UTC                         EST                                     
------------- --------------------------- ----------------------------------------
1580801246921 2020-02-04 07:27:26.921 UTC 2020-02-04 02:27:26.921 AMERICA/NEW_YORK

That also preserves the fractional seconds from the original value, which may or may not be useful (but as it doesn't handle leap seconds the precision is a bit of a moot point...)

You can then use the UTC value and compare with systimestamp instead of sysdate, as that includes the time zone too - so you don't need to worry about converting to local time, except maybe for display:

select my_date,
  (TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York' as est
from my_table
where TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND >= systimestamp - INTERVAL '1' HOUR;

If you want the result as a string in a specific format for display - rather than letting your client/application decide how to format it, which is what you're seeing now - you can control that explicitly with to_char():

select TO_CHAR(
    (TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York',
    'YYYY-MM-DD HH24:MI:SS') as my_string
from my_table
where TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND >= systimestamp - INTERVAL '1' HOUR;

But leave the value as a timestamp until the last moment where you need it for display (or some other fixed output, e.g. JSON) - don't convert to a string and then try to compare with other things, for instance.