Skip to Main Content

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!

Timestamp arithmetic

724771Oct 1 2009 — edited Oct 1 2009
I have a third party system which is supplying timestamps to me. The timestamps are provided in the form YYYYMMDD1nnnnnnn (Date concatenated with 10000000 + (milliseconds since midnight) / 10) and I want to store the data in a TIMESTAMP column.

CREATE TABLE input (ts varchar2(16))
/

INSERT INTO input (ts) VALUES ('2009093014938326');

SELECT to_char(  (to_timestamp(substr(ts, 1, 8), 'YYYYMMDD') 
               + ((to_number(substr(ts, 9, 8)) - 10000000) * 10 / 1000 / (24*60*60))
               ), 'YYYYMMDDHH24:MI:SS.FF6')
FROM   input
/

               ), 'YYYYMMDDHH24:MI:SS.FF6')
                  *
ERROR at line 3:
ORA-01821: date format not recognized
It appears on investigation that Oracle is implicitly converting the timestamp to a date when I try to add the milliseconds part to it. An example using SYSTIMESTAMP:
SELECT SYSTIMESTAMP, dump (SYSTIMESTAMP), SYSTIMESTAMP + 1, dump(SYSTIMESTAMP + 1) from dual
/

SYSTIMESTAMP
---------------------------------------------------------------------------
DUMP(SYSTIMESTAMP)
--------------------------------------------------------------------------------
SYSTIMEST
---------
DUMP(SYSTIMESTAMP+1)
--------------------------------------------------------------------------------
01-OCT-09 10.51.00.216687 +01:00
Typ=188 Len=20: 7,217,10,1,9,51,0,0,12,234,97,152,1,0,5,0,0,0,0,0
02-OCT-09
Typ=13 Len=8: 7,217,10,2,10,51,0,0
Oracle has converted SYSTIMESTAMP + 1 to a date.

So my query is: how can I add a time period to a timestamp and get the result as a timestamp?
This post has been answered by Boneist on Oct 1 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 29 2009
Added on Oct 1 2009
4 comments
1,291 views