Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert Unix timestamp to Oracle Date

202153Apr 21 2005 — edited Apr 21 2005
I need to convert a unix timestamp that is stored in a varchar2 data type column.

Example..

1113135689000

Any insight ?

Comments

John Spencer
It looks like your timestamp is in milliseconds (or you are seriously future dating stuff) because:
SQL> SELECT TO_DATE('19700101','yyyymmdd') + (1113135689000/24/60/60) thedate
  2  FROM dual;
 
THEDATE
--------------------
12-nov-2693 22:03:20
So you probably really want:
SQL> SELECT TO_DATE('19700101','yyyymmdd') + ((1113135689000/1000)/24/60/60) thedate
  2  from dual;

THEDATE
--------------------
10-apr-2005 12:21:29
TTFN
John
439955
Hi John W Spencer,

I really do not understand what is the number '19700101' in your query. It shows something like the below.

SQL> SELECT TO_DATE('19700101','yyyymmdd') from dual;

TO_DATE('1
----------
01/01/1970
John Spencer
19700101 is January 1, 1970. This is the "epoch" for UNIX systems. UNIX systems keep track of time by counting the number of seconds since the "epoch". Since your timestamp value seemed way too far into the future, I assumed that in your case you had milliseconds.

John
Laurent Schneider
it seems unix timestamp is the question of the day! check my other posts...

do not forget the timezone !
select (timestamp '1970-01-01 00:00:00 GMT' +
   numtodsinterval(1113135689000/1000, 'SECOND'))
   at time zone 'Europe/Warsaw'
from dual
/
10.04.2005 14:21:29.000000000 +02:00
202153
That works, thank you very much !
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 19 2005
Added on Apr 21 2005
5 comments
111,969 views