-1

I am using the following, where I am converting NUMBER variable to Datetime stamp.

to_char(
 (to_timestamp('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS') + 
 (END_SAMPLE_TS_5MIN_MS/(1000*60*60*24)),'DD-MON-YYYY HH24:MI:SS')

But I notice the output resulting from above is behind by 1 second from actual value. Say for example if END_SAMPLE_TS_5MIN_MS=1388984699999. Instead of showing : 06Jan2014 05:04:59 It is showing it as 06Jan2014 05:05:00 The milliseconds are rounded to nearest second. Please help!

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Mahesh Gandhi
  • 1

2 Answers2

1

You can use numtodsinterval, something like:

select to_timestamp('01-JAN-1970 00:00:00.000','DD-MON-YYYY HH24:MI:SS.FF3')  +
    numtodsinterval(1388984699.999, 'SECOND')
    from dual;

Output:

1/6/2014 5:04:59.999000000 AM

Hope that helps

tbone
  • 15,107
  • 3
  • 33
  • 40
0

If you're not interested in utilizing the milliseconds to round up, you can effectively take the "floor" of your epoch with a substring:

select to_char((to_timestamp('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS')+(substr(1388984699999,1,length(1388984699999)-3)/(60*60*24))),'DD-MON-YYYY HH24:MI:SS') from dual

We're really just taking the substring of our string minus the last 3 digits (milliseconds in this unix timestamp format) and then adding it to the epoch to get your time.

clesiemo3
  • 1,099
  • 6
  • 17