Home > other >  oracle SQL convert seconds into date / time
oracle SQL convert seconds into date / time

Time:08-31

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

CodePudding user response:

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.

  • Related