expressing date value as milliseconds

[teloch]

I have a requirement to produce URL parameters from values in the db. One of the key fields is a date, and the application that is receiving the URL requires that the date be expressed in milliseconds.

For instance, in the following URL:

xxx.xxx.xxx.xxx/view.jsp?itemTypeID=ADM&itemID=1411&revDate=1061388633000&revNum=

The important part is: revDate=1061388633000

This value is stored in the db as 8/20/2003 09:10 AM

Our application uses a java timestamp object to do the conversion, but I need to somehow duplicate this in PL/SQL.

Any ideas?

 

[SantaMufasa]

Teloch,

Your "date" uses a concept called Universal Time Co-ordinated (UTC). It's basis is January 1, 1970. Actually, I'm a bit puzzled by the values you show. According to my calculations, your value, "1061388633000" yields a date and time that is exactly 5 hours ahead of your value "8/20/2003 09:10 AM":

select to_char(to_date('01.01.70','dd.mm.rr')+1061388633.000/(60*60*24),
'yyyy-mm-dd hh24:mi:ss') from dual;

2003-08-20 14:10:33 <-- Notice 5 hours ahead of your 9:10 time.

Teloch, I am not sure what you need...Are you wondering how you can store Date/Time values in Oracle that are accurrate to the millisecond (which is certainly possible) or are you asking some other question?

Please clarify in an update to this thread.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 19:19 (21Sep04) UTC (aka "GMT" and "Zulu"), 12:19 (21Sep04) Mountain Time)

 

[teloch]

Thanks for the reply - sorry if I didn't make myself clear.

What I need to be able to do is select a date from the db, then spool it out to a file as a text field formatted in milliseconds. The datatype is always going to be date in the db - consider this a read-only process.

The 5-hour offset that you noticed is a time-zone adjustment that our application automatically (and sometimes annoyingly) makes.

I think you've already given me the answer I need just in the way that select statement is laid out. All I need to do is take the date value in our db, subtract 1/1/1970, and convert the result to millis by multiplying by (60*60*24*1000)

Does that sound like a reasonable strategy?

thanks again

 

[SantaMufasa]

Yes, your calculations are correct, but your date still does not truly store milliseconds. To store milliseconds in an Oracle database, you need a column whose data type is "timestamp":

create table times (a date, b timestamp(4));
insert into times values(sysdate, systimestamp);
select to_char(a,'yyyy-mm-dd hh24:mi:ss'), to_char(b,'yyyy-mm-dd hh24:mi:ss.ff3') from times;

2004-09-21 12:53:54 2004-09-21 12:53:54.231

1 row selected.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:54 (21Sep04) UTC (aka "GMT" and "Zulu"), 12:54 (21Sep04) Mountain Time)