Yet another DBA blog: Storing milliseconds in Oracle

I got an interesting question from someone in my team today - does timestamp column store the time in milliseconds? If not, is it possible?

This is something I never bothered to think about so far...so I had to do some quick research...and eventually found that Oracle does not store millisecond directly but it does store seconds with a fraction component which can help you to get the milliseconds. Not only millisecond rather you can get to microseconds too! In fact you can specify the number of digits that oracle stores in the fractional part of seconds i.e. the precision part for TIMESTAMP datatype. The default is platform dependant, on UNIX it defaults to 6 and on windows it defaults to 3. The valid range is [0-9].

Here is how the fractional component is shown, which can be used to derive milliseconds :-

SIPRAS@orademo> select time_in_ms from test_tab;

TIME_IN_MS
------------------------------------------------
23-MAY-08 01.29.59.008864 PM
23-MAY-08 11.11.11.100000 AM

You can insert milliseconds too! here is an example :-

SIPRAS@orademo> create table test_tab (time_in_ms timestamp);

Table created.

SIPRAS@orademo> insert into test_tab values(systimestamp);

1 row created.

SIPRAS@orademo> insert into test_tab values('23-MAY-2008 11:11:11.1');

1 row created.

SIPRAS@orademo> commit;

Commit complete.

SIPRAS@orademo> select * from test_tab;

TIME_IN_MS
---------------------------------------------------------------------------
23-MAY-08 01.29.59.008864 PM
23-MAY-08 11.11.11.100000 AM