Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rodrigo.

Asked: February 03, 2017 - 6:22 pm UTC

Last updated: February 03, 2017 - 8:31 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

When I query a simple select for systimestamp I always got only 3 decimals from fractional seconds. See below:

SQL> select systimestamp from dual;

SYSTIMESTAMP                                                                                        
---------------------------------------------------------------------------                         
03-FEB-17 03.59.46.609000 PM -02:00   
                                                              
SQL> /

SYSTIMESTAMP                                                                                        
---------------------------------------------------------------------------                         
03-FEB-17 03.59.51.422000 PM -02:00                                                                 

SQL> /

SYSTIMESTAMP                                                                                        
---------------------------------------------------------------------------                         
03-FEB-17 03.59.52.503000 PM -02:00                                                                 



I've already checked NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT for my session as follow:
SQL> select * from nls_database_parameters where parameter like '%TIMESTAMP%';

PARAMETER                                                                                           
----------------------------------------------------------------------------------------------------
VALUE                                                                                               
----------------------------------------------------------------                                    
NLS_TIMESTAMP_TZ_FORMAT                                                                             
DD-MON-RR HH.MI.SSXFF AM TZR                                                                        
                                                                                                    
NLS_TIMESTAMP_FORMAT                                                                                
DD-MON-RR HH.MI.SSXFF AM                                                                            


And I've tried even to change session format from 'DD-MON-RR HH.MI.SSXFF AM TZR' to 'DD-MON-RR HH.MI.SSXFF9 AM TZR' (SSXFF to SSXFF9 or SSXFF6), but got same results.
I have no clue on what to do to have all 9 or 6 decimals for fraction seconds.

I went to Oracle VM 7.2 to test it and there I got 6 digits but can't get 9.

Can you help me?

and Connor said...

We ask for the timestamp from the OS. Whatever it can provide, we use. Typically Windows gives us 3, most Unix's will give us 6.

I'm unware of any OS's that will give 9 currently.

Is this answer out of date? If it is, please let us know via a Comment