6

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

Does Oracle have any built in functions that can help?

In my searchs I find resuts for DateTime to Unix but no Unix to DateTime...

Dan Ciborowski - MSFT
  • 6,807
  • 10
  • 53
  • 88
  • `numtodsinterval('.$_GET["date"].',\'SECOND\')`... You're vulnerable to SQL injections with this: use parametrised queries instead on passing the values directly by string concatenation. – Bruno Jul 23 '12 at 19:21

2 Answers2

16

There are no built-in functions. But it's relatively easy to write one. Since a 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 can see being called

SQL> select unix_ts_to_date( 1336822620 ) from dual;

UNIX_TS_TO_DATE(133
-------------------
2012-05-12 11:37:00
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I ended up using to_date(\'1970-01-01\',\'YYYY-MM-DD\') + numtodsinterval('.$_GET["date"].',\'SECOND\') Where date is a code in the url of the unix timestamp. But your solution is much more "official" – Dan Ciborowski - MSFT May 13 '12 at 17:19
  • 2
    Unix timestamp is the number of seconds since January 1, 1970 00:00:00 **UTC**. Unless the database server runs on UTC you should use `(TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + numtodsinterval(p_unix_ts,'second')) AT LOCAL` – Wernfried Domscheit Jan 05 '16 at 18:01
0

I used this in the end...

date=unixtimestamp number

to_date(\'1970-01-01\',\'YYYY-MM-DD\') + numtodsinterval('.$_GET["date"].',\'SECOND\')
Dan Ciborowski - MSFT
  • 6,807
  • 10
  • 53
  • 88