Earlier today a situation came up where a UNIX timestamp (a count of the number of seconds from January 1, 1970, midnight GMT) needed to be converted into an Oracle DATE format. The Oracle TO_DATE (covered in more detail in my article Oracle, SQL, Dates and Timestamps) does not support this type of conversion.
A Google search confirmed for me that their was no easy way to make the TO_DATE function do this, but I did find this article from the Oracle + PHP Cookbook on Oracle’s site which contained this simple function to convert UNIX timestamps to Oracle dates.
[SQL]CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* Converts a UNIX timestamp into an Oracle DATE
*/
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
oracle_date DATE;
BEGIN
IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
‘UNIX timestamp too large for 32 bit limit’
);
ELSIF unixts < min_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
'UNIX timestamp too small for 32 bit limit' );
ELSE
oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
END IF;
RETURN (oracle_date);
END;
/
[/SQL]
Once compiled, you can use the function to convert numerical UNIX timestamps into Oracle dates in the same manner you would use the TO_DATE function to convert text to dates. Here is an example:
SQL> select unixts_to_date(1094165422) from dual;
UNIXTS_TO
---------
02-SEP-04
whenever i execute this procedure, it give following error
LINE/COL ERROR
——– —————————————————————–
22/17 PL/SQL: Statement ignored
22/45 PLS-00201: identifier ‘NUMTODSINTERVAL’ must be declared
I am using
Oracle8i Enterprise Edition Release 8.1.7.4.0 – Production
With the Partitioning option
JServer Release 8.1.7.4.0 – Production
SQL*Plus: Release 9.2.0.1.0
please respond ASAP
Ambrish, for some reason the user you are trying to run this as cannot see the function, or you are using the function improperly.
Did you get any errors after compiling the function?
Are you connecting as the same user who created the function? If not you will need to creat a public synonym for the function and grant execute to the users who will need it.
It seems the NUMTODSINTERVAL function is new in Oracle 9i. If you are working with Oracle 8i or earlier you can substitute this line:
oracle_date := unix_epoch + (unixts/86400);
instead of the NUMTODSINTERVAL to eliminate this error.
The NUMTODSINTERVAL function turns the number of seconds into a fractional number representing days. Dividing the seconds by 86400 (the number of seconds in 1 day) achieves the same thing.
This page:
http://www.internetvibes.net/forum/index.php?showtopic=2872
does the same using
select TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’) + numtodsinterval(unixtimestamp, ‘SECOND’) from dual
Ain’t this much easier and enough though?
That’s a nice and succinct method Johannes. The one above does essentially the same thing with a couple variables and a couple more steps, but also provides some exception handling.
I thought it will solve the problem , but its not.
I implemented the same procedure what ever is given above for converting from timestamp to date and time . It is giveing the correct date but not the time , as my sytem is showing it differently
Srivalli,
I believe the discrepency you are seing is because the UNIX timestamp is reported in Coordinated Universal Time (UTC). UTC is equivalent to Greenwich Mean Time (GMT). To evaluate the date in your local timezone you will need to convert it with the Oracle delivered new_time function as below:
select to_char(new_time(unixts_to_date(1145366808), ‘GMT’, ‘EDT’), ‘MM/DD/YYYY HH24:MI:SS’) from dual
If you grab a UNIX timestamp from a converter on a website be careful, some of them are converting for their own timezone already. This one allows you to enter an offset from GMT for accurate timestamps:
http://www.4webhelp.net/us/timestamp.php
Hope this helps. Perhaps I’ll rewrite this function to compensate for the timezone as well.
Jon
you saved my day…i was ready to bang my head against the screeen.
#version 8.1.7x
oracle_date := unix_epoch + (unixts/86400);
This query converts UNIX to Oracle and handles the time zones. Add TO_CHAR to format the date, if required.
select trunc((timestamp ‘1970-01-01 00:00:00 GMT’ + numtodsinterval(1094165422, ‘SECOND’)) at time zone ‘Canada/Pacific’) at_time_zone_date
from dual
Yup, Mike’s query does the same thing my function does, just without error handling. Nice for a one-off but if you need this often it’s handy to have the function around.
Jon
What to do if my timestamps are bigger that 32-bit format e.g. 1026259200000 for date 2002-07-10 (yyyy-MM-dd)? If I cut last free 0 I’m getting correct date. But how can be sure when to cut them and when not?
Hi,
I need to select timestamp from one of DB tables,but it is in unix tistamp format.How can i convert unix timestamp to normal dd/mm/yyyy and select the data in oracle.
There are a lot of these out there, but this is a really good unix timestamp conversion utility.