I'm doing a union all on 2 tables. The first table has the date stored in the DATE format, which is good. The second table has the date format in seconds. Why, you may ask is it this way? I don't know why it is stored that way. Anyway the format in seconds is in EPOCH time. I'm trying to convert seconds to a format of mm/dd/yyyy but I cannot figure out how to do it. I found this way online and it is close but only prints out the 01/01/1970 and not the correct date.
to_date('01-jan-1970','dd-mon-yyyy') + ( MY_DATE/1000000/60/60/24 )
I've tried various other ways with not luck. I get a data type error or errors on the display so I'm having no luck.
no create table
no inserts
no example to play with :(
this:
https://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551323629589952358 shows how to take a "C" time - which is the number of seconds since jan 1 1970 in GMT - and convert it into an Oracle Date (you supply the timezone).
it should be:
to_date('01011970','ddmmyyyy') + 1/24/60/60 * :theTime
not sure where the 1,000,000 comes from in your computation. If you have the number of seconds - you need to convert it into the number of days - that would be 1/24/60/60 TIMES the number of seconds.
You divide the number of seconds by 60 to get minutes. That by 60 to get hours.. That by 24 to get days. Add days to jan-1 1970. remember to adjust for timezones - that epoch should probably be the number of seconds in GMT....