Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

How to convert Unix timestamp to Oracle date

Let's start from some theory, "what is unix timestamp?".
The unix timestamp is the way to measure time as a running total number of seconds after Unix epoch. Unix epoch started at 1st, January 1970 00:00:00 UTC. The often asked question is how to convert Unix timestamp to Oracle date.

On Linux and Windows systems we can display current timestamp using date command.

mob@mob-ubuntu:~$ date +%s 
1244105650

To convert Unix timestamp to Oracle data we can use the following query:

SQL> alter session set nls_date_format = "DD-MON-RRRR HH24:MI:SS"
2 /

Session altered.

SQL> select to_date('1970-01-01','YYYY-MM-DD') +
2 numtodsinterval(1244108886,'SECOND') as "oracle date"
3 from dual
4 /

oracle date
--------------------
04-JUN-2009 09:48:06

TO_DATE function converts epoch start, which is 1st January 1970 to Oracle date.

NUMTODSINTERVAL converts number of seconds after 1st January 1970, which we get from unix timestamp to Oracle's INTERVAL DAY TO SECOND.

Then we add both values to create an Oracle's date.

If we are planning to use such functionality quite often it would be handy to create a function as on below example:

SQL> create or replace function unixts_to_date(p_timestamp number) return date
2 as
3 begin
4 return to_date('1970-01-01','YYYY-MM-DD') +
numtodsinterval(p_timestamp,'SECOND');
5 end unixts_to_date;
6 /

Function created.

SQL> show err
No errors.

SQL> select unixts_to_date(1244108886) as "oracle date"
2 from dual
3 /

oracle date
--------------------
04-JUN-2009 09:48:06

Categories: