oracle SQL convert seconds into date / time

Question

Using Oracle SQL inside APEX. I need to display seconds (1661596801) as date / time (dd/mm/yyyy - HH24:MI:SS).

The Seconds valve was create in MySQL - $startTimeinSeconds = time(); - this is successful

To display the date / time in Oracle SQL I have been using the follow which gives no result:

select TO_DATE("1661596801", "DD/MM/YYYY HH24:MI:SS") from dual;

What do wrong?

Thanks, Pete

for the given input 1661596801 seconds the output is 20-JAN-70 ., using this query select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1661596801 from dual; what is the column datattype? — Sund'er, Aug 27 '22 at 12:03
Does this answer your question? [oracle convert unix epoch time to date](https://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date) — astentx, Aug 27 '22 at 19:34

score 0 · Accepted Answer · answered Aug 27 '22 at 12:48

That value looks like number of seconds elapsed since 1st of January 1970 (i.e. Unix epoch time). If so, then you could try with this:

(first, setting session date/time format, just to know what is what. You don't have to do that):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

and then

SQL> select date '1970-01-01' + 1661596801 * interval '1' second result
  2  from dual;

RESULT
------------------------------
27.08.2022 10:40:01

SQL>

which is today at 10:40:01 in the morning.

oracle SQL convert seconds into date / time

1 Answers1