Search code examples
sqloracleapex

oracle SQL convert seconds into date / time

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

Solution

  • 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.