CODEWITHSUNDEEP
sqloracle-database
Christopher Jack
Christopher Jack

Reputation: 97

Between two Unix dates in Oracle

Thanks to previous question...

I have a more simplified OR statement.

Question is instead of a IN how could I change this to a between?

TO_DATE(TO_CHAR(TO_DATE(''19700101'',''yyyymmdd'') + + (FLOOR(ph.change_date/24/60/60)))) IN (''23-DEC-2020'', ''29-DEC-2020'')

So I want to say between the 23-DEC-2020 and 29-DEC-2020 including both?

Thanks

Upvotes: 0

Views: 206

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

change_date seems to be in Unix timestamp format -- the number of seconds since 1970-01-01. I would recommend doing the comparison by converting constant values to the same format:

where ph.change_date >= (date '2020-12-23' - date '1970-01-01') * 24 * 60 * 60 and
      ph.change_date < (date '2020-12-30' - date '1970-01-01') * 24 * 60 * 60

Note that this is index (and partition) friendly. And, the second comparison is < on the next day to get the entire day.

If you need to deal with the column as "real" dates, you can add a computed column

alter table t add column change_date_date date generated always as
     (cast(date '1970-01-01' as timestamp) + change_date * interval '1' second);

You can then reference change_date_date and even define a an index on it.

Upvotes: 1

MT0
MT0

Reputation: 167981

If you want to use an index on the change_date column then perform the conversion on the literal values and convert them to epoch times (rather than converting the column's epoch time to a date, which would not allow you to use a normal index on the column):

ph.change_date BETWEEN ( DATE '2020-12-23' - DATE '1970-01-01' ) * 86400
               AND     ( DATE '2020-12-29' - DATE '1970-01-01' ) * 86400 + 86399

Upvotes: 2

Popeye
Popeye

Reputation: 35910

You can convert the epoch time(ph.change_date) to date and then compare as follows:

Date'1970-01-01' + FLOOR(ph.change_date/24/60/60) -- considering that change_date is epoch time
    between date'2020-12-23' and date'2020-12-23'

Upvotes: 1

CoderRambo
CoderRambo

Reputation: 402

I think we can try like this

where <expression> 
between TO_DATE('23-DEC-2020','DD-MON-YYYY') and 
TO_DATE('29-DEC-2020','DD-MON-YYYY')

Upvotes: 1

Viktor T&#246;r&#246;k
Viktor T&#246;r&#246;k

Reputation: 1319

You can do it with the between keyword.

For example:

to_date('2021.01.06', 'yyyy.mm.dd') between to_date('2021.01.01', 'yyyy.mm.dd') and to_date('2021.01.31', 'yyyy.mm.dd')

Upvotes: 1

Related Questions