using to_timestamp to convert calendar milliseconds?

[Jane Foster]

I am trying to extract a date from some xml and insert it into a column on an Oracle table that is of type Date. The date was created using cal.setTimeInMillis and looks like this after being created:
<theDate>
<time>1261458000000</time>
<timezone>America/Indiana/Petersburg</timezone>
</theDate>

I am trying to extract it like this:
(select to_timestamp(extractValue(xml,'/test/testDate/time/text()'))from dual),

and I get the following error:
ORA-01843: not a valid month

I know that I am getting this error because I haven't supplied a format mask. I am not sure how to do this, can anyone help me figure this out? I am not opposed to changing the way I am creating the date, I am just not sure how to go about this. Am fairly new to this. Thanks in advance -

[John Bengler]

Hi Jane,

I'm afraid with only using to_timestamp it will not be be possible to perform this conversion, because Oracle doesn't supply a direct Date format for this:

DateTime Format Models

However with some extra calculation it is possible to do this. If you know that a numeric value of 1 represents a day in an Oracle date (or timestamp) field you simply can get the Date/Timestamp for your offset (e.g. for 1.1.1970: select to_date('01-JAN-1970','DD-MON-YYYY') from dual and add your milliseconds:

    
select to_date('01-JAN-1970','DD-MON-YYYY') + extractValue(xml,'/test/testDate/time/text()')/(24*60*60*1000) from dual;

for timestamps you can use the function NUMTODSINTERVAL():

    
select to_timestamp('01-JAN-1970','DD-MON-YYYY') + numtodsinterval(extractValue(xml,'/test/testDate/time/text()')/1000,'SECOND') from dual;

John