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

is there any way in Oracle, to get only the dd-mm-yyyy part from an unix timest开发者_如何学运维amp in oracle?

Like:

select to_char(my_timestamp, 'ddmmyyyy') as my_new_timestamp from table

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>

As I understand it, A Unix timestamp is defined as a number of seconds since 1970-01-01, in which case:

select DATE '1970-01-01' + my_timestamp/86400 from table;

(There are 86400 seconds in a day.)

To ignore the hours, minutes and seconds:

select TRUNC(DATE '1970-01-01' + my_timestamp/86400) from table;

However, if what you want is a "truncated" Unix timestamp then try this:

select floor(my_timestamp/84600)*84600 from dual;

I believe it's:

select to_char(my_timestamp, 'dd-mm-yyyy') as my_new_timestamp from table

See also this reference on Oracle's date format specifiers.

Unix timestamp is seconds since Jan 01 1970. (UTC).

To get Unix timestamp of now, try the follwing sql:

select (CAST(SYS_EXTRACT_UTC(current_timestamp) AS date) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 FROM dual;

You can replace current_timestamp with any timestamp-value.

BTW, some answers seem to return seconds since Jan 01, 1970 with local time zone. That's wrong.