Oracle SQL - Column with unix timestamp, need dd-mm-yyyy timestamp

oracle, sql

Solution

Given this data ...

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
  2  /

Session altered.

SQL> select * from t23
  2  /

MY_TIMESTAMP
--------------------
08-mar-2010 13:06:02
08-mar-2010 13:06:08
13-mar-1985 13:06:26

SQL>

.. it is simply a matter of converting the time elapsed since 01-JAN-1970 into seconds:

SQL> select my_timestamp
  2        , (my_timestamp - date '1970-01-01') * 86400 as unix_ts
  3  from t23
  4  /

MY_TIMESTAMP            UNIX_TS
-------------------- ----------
08-mar-2010 13:06:02 1268053562
08-mar-2010 13:06:08 1268053568
13-mar-1985 13:06:26  479567186

SQL>

Problem

is there any way in Oracle, to get only the dd-mm-yyyy part from an unix timestamp in oracle? Like: ``` select to_char(my_timestamp, 'ddmmyyyy') as my_new_timestamp from table ```

Original source