Converting Epoch Time to Date with a Virtual Column

April 15, 2018

oracle, sql

Epoch time is common in some situations because it is easier to do date calculations than with normal dates. The most popular is UNIX Epoch time which is the number of seconds since the start of January 1, 1970.

If you have your data in Oracle you don’t have to worry about date arithmetic, since Oracle handles that for you. So in case you have such a table with epoch time and want to query it with functions that expect the DATE datatype, you can add a virtual column that returns the epoch time converted to DATE like this:

alter table reviews
add review_date date generated always
as (date '1970-01-01' + time_epoch /60/60/24);

If you wonder why epoch time is easier for calculations just think about how you will find the number of days between two dates. With epoch time you simply subtract the earliest from the latest and perform the proper division to convert back to days.

By the way, Oracle supports another epoch time, Julian Day, which is the number of days since January 1, 4712 BC. You can get todays date in Julian date like this:

select to_char(sysdate,'J')
from dual;

Which returned 2458224 on the day this post was published.

Fun fact: the Oracle documentation says number of days since January 1, 4712 BC, but Wikipedia says 4713 BC.

Some dates are better than others.