Oracle BETWEEN two dates stored as INT

1
votes

I'm new to DB's, I have a table within Oracle with dates stored as INT, my SELECT statement I include TO_DATE() so I can see the date, I then want to add a WHERE BETWEEN and use "dates" but I feel like there has to be a cleaner more efficient way to do this...I tried using the "alias" for the column but it gives me an error saying it's expecting an integer, that is why I have TO_DATE() again in the WHERE.

SELECT to_date('1-Jan-1970 00:00:00','dd-mon-yyyy hh24:mi:ss')
  + IRF.START_DATE_TIME_KEY/60/60/24) AS START_DATE_TIME_KEY
 WHERE to_date('1-Jan-1970 00:00:00','dd-mon-yyyy hh24:mi:ss')
  + (IRF.START_DATE_TIME_KEY/60/60/24) 
BETWEEN TO_DATE('20-Feb-2015 00:00:01','dd-mon-yyyy hh24:mi:ss') 
    AND TO_DATE('20-Feb-2015 23:59:59','dd-mon-yyyy hh24:mi:ss');
3
sqloracledateintbetween
Why are you storing dates as numbers? Do you have the ability to make changes to the table (not changing the column, but say adding a column)? – David Faber
@DavidFaber without disclosing too much info, it's a vendor app DB, read access only. – AM_Hawk
Ugh, a vendor should really know better. That's just lazy. – David Faber
I can see storing the Unix timestamp but I can't see a good reason for not storing the date as well. Especially now that one can have virtual columns in Oracle which aren't actually stored on disk but are generated at runtime. Having a virtual column is like having an alias that you can index and use in WHERE clauses. – David Faber

3 Answers

2
votes

Couple things. One, using BETWEEN is not that great for dates because dates aren't exactly discrete. It is better to use something like:

WHERE the_date >= TRUNC(start_dt)
  AND the_date < TRUNC(end_dt) + 1;

Two, it looks like you're storing the date as the number of seconds that has passed since 1/1/1970? You might be better off at least using ANSI date literals and intervals:

SELECT DATE'1970-01-01' + NUMTODSINTERVAL(ifs_start_date_time_key, 'SECOND')

You can use an alias for the above, but you can't refer to it in the WHERE clause unless you use a subquery. So putting all of this together:

SELECT start_date_time_key FROM (
    SELECT DATE'1970-01-01' + NUMTODSINTERVAL(irf.start_date_time_key, 'SECOND') AS start_date_time_key
      FROM mytable irf
) WHERE start_date_time_key >= DATE'2015-02-20'
    AND start_date_time_key < DATE'2015-02-20' + INTERVAL '1' DAY; -- or just DATE'2015-02-20' + 1;

Someone asked in the comments why not use BETWEEN with dates. Well, in this case it almost certainly wouldn't matter because there isn't going to be an index on start_date_time_key when it's converted to a DATE, but it will matter in cases where there is an index on the DATE column, so avoiding BETWEEN for dates is just a good habit to get into. I just tried the following on a medium-sized table in my DB:

SELECT * FROM mytable
 WHERE TRUNC(date_created) BETWEEN TRUNC(SYSDATE-2) AND TRUNC(SYSDATE-1);

The above gave me a full table scan with a high CPU cost. Then I did this:

SELECT * FROM mytable
 WHERE date_created >= TRUNC(SYSDATE-2)
   AND date_created < TRUNC(SYSDATE);

That gave me a range scan on the index (because there is an index on date_created) and a fair CPU cost. I can imagine the contrast would be even greater for a "big" table with millions of rows.

Alternately, one could put a function-based index on the DATE column (e.g., TRUNC(mydate)), but that won't help you if your date values also have time portions. Just eschew BETWEEN -- using >= and < isn't that much more typing.

Another thought just struck me. If the column IRF.START_DATE_TIME_KEY is itself indexed, then it might be better to convert the dates to similar integers and use those.

SELECT DATE'1970-01-01' + NUMTODSINTERVAL(irf.start_date_time_key, 'SECOND') AS start_date_time_key
  FROM mytable irf
 WHERE irf.start_date_time_key >= (DATE'2015-02-20' - DATE'1970-01-01') * 86400
   AND irf.start_date_time_key < (DATE'2015-02-20' + 1 - DATE'1970-01-01') * 86400

It's not pretty, but it would have the advantage of using the index on IRF.START_DATE_TIME_KEY in the event there is one.

0
votes

It could be done by a view, but since you said the DB is read-only it could be "nested" in your query sort of like this:

select REAL_DATE_COL
from
(select *, --code to convert int date to real date-- as REAL_DATE_COL
from MY_TABLE)
where REAL_DATE_COL between date1 and date2

This way you only have that ugly conversion code once.

0
votes

I think using the date keyword would make your code much cleaner:

SELECT (DATE '1970-01-01' + IRF.START_DATE_TIME_KEY/60/60/24) AS START_DATE_TIME_KEY
FROM . . .
WHERE DATE '1970-01-01' + IRF.START_DATE_TIME_KEY/60/60/24) BETWEEN DATE '2015-02-20' AND '2015-02-20'