Oracle - compare date column to sysdate

Question

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/

You shouldn't store DATE values in a `varchar` column to begin with. Do you have the possibility to fix that? — , Feb 04 '20 at 09:08
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, Feb 04 '20 at 09:12
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, Feb 04 '20 at 09:15
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, Feb 04 '20 at 09:30
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, Feb 04 '20 at 10:27
"my value isnt a string, its a char". A string _is_ a char. Data types CHAR, VARCHAR, VARCHAR2, NVARCHAR, NVARCHAR2, all - by definition - contain 'strings'. More precisely, "characater strings". or 'strings of characters'. They have no inherent meaning as dates or numbers, regardless of what the string of characters looks like to a human. — EdStevens, Feb 04 '20 at 13:16

Wernfried Domscheit · Answer 1 · 2020-02-04T11:35:59.343

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.

score 0 · Answer 2 · answered Feb 04 '20 at 09:05

0

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.

answered Feb 04 '20 at 09:05

Littlefoot

131,892
15
35
57

Thank you for your reply, i tried that before (and tried again) it doesnt seems to be working, it keeps giving me wrong dates. – Tal Feb 04 '20 at 09:11
You're welcome. As I said, that's the price you have to pay. "Wrong dates" are ... what exactly? Are you **sure** that **all** strings are stored in dd-mm-yyyy hh24:mi:ss format? – Littlefoot Feb 04 '20 at 09:17
yes, i've updated my post with more info hope that will help you to help me :) – Tal Feb 04 '20 at 09:27

Alex Poole · Accepted Answer · 2020-02-04T12:08:08.647

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.

hey alex! thank you so much! your solution worked PERFECTLY! select my_date, 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 where FROM_TZ(TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND, 'UTC') >= systimestamp - INTERVAL '1' HOUR; can you please explain how can i change the the format to something like this? my_date contains DD-MM-YYYY HH24:MI:SS only for example 04/02/2020 10:52:34 - thats it. many thanks! — Tal, Feb 04 '20 at 11:20
The query selects a timestamp with time zone data-type value. Your client is deciding how to format that for display. You can change that, but if you want the query to always show the same format, you can wrap that selected value in a `TO_CHAR()` call which specifies that format. But - only do that for display; leave it as a timestamp until the last possible moment, so you're always comparing correct data types until that point. — Alex Poole, Feb 04 '20 at 11:28
hey alxe, something like this? select my_date as epoch, TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND AT TIME ZONE 'America/New_York' as TO_CHAR(my_date, 'YYYY-MM-DD :HH24:MI:SS') from my_table — Tal, Feb 04 '20 at 11:47

Oracle - compare date column to sysdate

3 Answers3