To my knowledge, there is no Oracle TO_DATE format specifier for converting from epoch seconds to a DATE or TIMESTAMP. There should be.
To my knowledge, there is no Oracle built-in function to convert epoch seconds to a DATE or TIMESTAMP. There should be.
However, converting epoch seconds is not very hard. By definition, epoch seconds are seconds since January 1, 1970. So add the seconds to 1970.
Since my problem doesn't specify anything about location, time zone, or daylight saving time, my solution doesn't do anything to account for those. If you use this, you might want to think about those. I think everybody ought to just use UTC anyway.
Also, I don't specify the hour, minute, and second of the epoch I'm adding to. I may have some logic error here. It's good enough for my purposes, but if you need to use this, think about it and make sure it does the right thing before you depend on it.
CREATE OR REPLACE FUNCTION from_epoch(v_epochseconds NUMBER)
RETURN TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + v_epochseconds / 86400;
86400 of course is the number of seconds in a day, so the division converts your input value to days for use with Oracle's arithmetic.
By the way, this also doesn't account for leap seconds.
Hmm, probably if I'm going to use TIMESTAMP instead of date I should convert the input value to a real, first-class, INTERVAL value and add that instead of relying on an implicit conversion from number to days.