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
Add a comment
|
4 Answers
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>
Comments
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;
1 Comment
opHasNoName
my problem is : after this it want an unix timestamp not oracle date ;)
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.
Comments
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.
