Tags: 01-jan-1970, adding, database, fractional, loose, milliseconds, mysql, number, oracle, secondsselect, sql, timestamp, to_timestamp

adding milliseconds to a timestamp

On Database » Oracle

822 words with 2 Comments; publish: Sun, 10 Feb 2008 22:57:00 GMT; (25062.01, « »)

Is there a way to add a number of milliseconds to a timestamp and not loose fractional seconds?

select to_timestamp('01-jan-1970 00:00:00.000', 'dd-mon-yyyy hh24:mi:ss.ff') + 1068568161783/(24*60*60*1000) as gmt_time from dual;

Running the query above results in rounding to the nearest second. From the docs, I learned that oracle implicitly casts to date on arithmetic operations. So how can this be accomplished?

All Comments

Leave a comment...

  • 2 Comments
    • Use an interval (day to second) to add the milliseconds:

      SQL> select systimestamp,

      2 systimestamp+to_dsinterval('3650 12:10:23.6789')

      3 from dual;

      SYSTIMESTAMP

      ----

      SYSTIMESTAMP+TO_DSINTERVAL('365012:10:23.6789')

      ----

      11-NOV-03 10.04.48.000000 PM -06:00

      09-NOV-13 10.15.11.678900000 AM -06:00

      #1; Fri, 22 Feb 2008 02:48:00 GMT
    • That will do it. Thanks!
      #2; Fri, 22 Feb 2008 02:49:00 GMT