Re: converting milliseconds to Oracle DATE

From: Vladimir M. Zakharychev <bob--nospam--_at_dynamicpsp.com>
Date: Mon, 22 May 2006 13:03:08 +0400
Message-ID: <e4ruod$223s$1@hypnos.nordnet.ru>

"Vladimir M. Zakharychev" <bob--nospam--_at_dynamicpsp.com> wrote in message news:e4rtpl$21pp$1_at_hypnos.nordnet.ru...
>
> <laredotornado_at_zipmail.com> wrote in message
> news:1148068468.365556.217170_at_u72g2000cwu.googlegroups.com...
>> Hello,
>>
>> Using Oracle 9i on Solaris, how do I convert a numeric data column
>> (which represents milliseconds passed since Jan, 1, 1970 midnight) to a
>> DATE column?
>>
>> Thanks, - Dave
>>
> select timestamp '1970-01-01 00:00:00.00 +00:00' +
>
> numtodsinterval(to_number(:unixstamp,'9999999999D999','NLS_NUMERIC_CHARACTERS=''.
> '''),'SECOND')) at local
>
>

Sorry, hit the wrong button. :)

select timestamp '1970-01-01 00:00:00.00 +00:00' +   numtodsinterval(

     to_number(:unixstamp,'9999999999D999',
                       'NLS_NUMERIC_CHARACTERS=''. '''),

    'SECOND')) at local
from dual

The above expression assumes that :unixstamp is a string, as usually stored in the logs, with timestamp represented as fractional seconds (for example, we used this expression to parse Squid logs.)

If :unixstamp is number of milliseconds, then it's simply

select
timestamp '1970-01-01 00:00:00.00 +00:00' +   numtodsinterval(:unixstamp/1000,'SECOND') at local from dual

In both cases, resulting timestamp is in UTC and AT LOCAL converts it to local (session) time zone. If you assign resulting timestamp value to a DATE variable, Oracle will throw away time zone information and milliseconds.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon May 22 2006 - 04:03:08 CDT