Convert Unixtime to Datetime SQL (Oracle)

Question

I have a datetime field (P_DT), and I would like to return all the results where P_DT is greater than the unix input timestamp.

Does Oracle have any built-in features that can help?

In my searches, I found resuts for DateTime for Unix, but not Unix for DateTime ...

Answer 1

There are no built-in functions. But it is relatively easy to write. Since the Unix timestamp is the number of seconds since January 1, 1970.

CREATE OR REPLACE FUNCTION unix_ts_to_date( p_unix_ts IN NUMBER ) RETURN DATE IS l_date DATE; BEGIN l_date := date '1970-01-01' + p_unix_ts/60/60/24; RETURN l_date; END; 

which you see as being called

 SQL> select unix_ts_to_date( 1336822620 ) from dual; UNIX_TS_TO_DATE(133 ------------------- 2012-05-12 11:37:00 

Answer 2

I used this at the end ...

 date=unixtimestamp number to_date(\'1970-01-01\',\'YYYY-MM-DD\') + numtodsinterval('.$_GET["date"].',\'SECOND\')