Oracle SYSTIMESTAMP

"SELECT TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY HH24:MI:SS.FF6') FROM DUAL" is returning 10/02/2007 14:30:49.646000.  Why am I only getting milliseconds, when I'm asking for microseconds?  This is Oracle10g on a Windows server...
jahooperAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
I think that tool is rounding or truncating and showing the numbers to you because in toad i  am able to get correct output.

Thanks
0
 
sventhanCommented:
Simple examples to illustrate the use of the datetime types:

   SQL> CREATE TABLE dt
          ( d_date                 DATE,
            d_ts                   TIMESTAMP(6),
            d_tstz                 TIMESTAMP(6) WITH TIME ZONE,
            d_tsltz                TIMESTAMP(6) WITH LOCAL TIME ZONE,
            d_ymint                INTERVAL YEAR(2) TO MONTH,
            d_dsint                INTERVAL DAY(2) TO SECOND(6));


   SQL> DESCRIBE dt
   Name                     Null?    Type
   ------------------------ -------- --------------------------------
    D_DATE                           DATE
    D_TS                             TIMESTAMP(6)
    D_TSTZ                           TIMESTAMP(6) WITH TIME ZONE
    D_TSTLZ                          TIMESTAMP(6) WITH LOCAL TIME ZONE
    D_YMINT                          INTERVAL YEAR(2) TO MONTH
    D_DSINT                          INTERVAL DAY(2) TO SECOND(6)


   SQL> INSERT INTO dt VALUES
          ( DATE'1999-12-07',
            TIMESTAMP'1999-12-07 15:50:30.123456',
            TIMESTAMP'1999-12-07 15:50:30.123456-05:00',
            TIMESTAMP'1999-12-07 15:50:30.123456-05:00',        
            INTERVAL'12-01' YEAR TO MONTH,
            INTERVAL '50 10:20:20.123' DAY(2) TO SECOND(3));


   SQL> SELECT d_tstz FROM dt;
   D_TSTZ
   ------------------------------------------
   07-DEC-99 03.50.30.123456 PM -05:00

   SQL> SELECT SYS_EXTRACT_UTC(d_tstz) FROM dt;
   SYS_EXTRACT_UTC(D_TSTZ)
   ------------------------------------------
   07-DEC-99 08.50.30.123456 PM

   SQL> SELECT EXTRACT(HOUR FROM d_tstz) FROM dt;
   EXTRACT(HOURFROMD_TSTZ)
   -----------------------
                        20

   SQL> SELECT EXTRACT(MINUTE FROM d_tstz) FROM dt;
   EXTRACT(MINUTEFROMD_TSTZ)
   -------------------------
                          50

   SQL> SELECT CURRENT_TIMESTAMP(6) FROM DUAL;
   CURRENT_TIMESTAMP(6)
   -------------------------------------------------------------------------
   07-DEC-99 11.09.13.368065 PM +00:00

   (database in the West Coast)

   SQL> ALTER SESSION SET TIME_ZONE='-8:00';
   Session altered.

  (This ALTER SESSION is not required in since CURRENT_TIMESTAMP defaults
   to the new datatype : TIMESTAMP WITH LOCAL TIME ZONE)


   SQL> SELECT CURRENT_TIMESTAMP(6) FROM DUAL;
   CURRENT_TIMESTAMP(6)
   -------------------------------------------------------------------------
   07-DEC-99 03.10.58.573293 PM -08:00
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
SELECT TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY HH24:MI:SS.FF6') FROM DUAL;

FF [ 1 - 9 ] --> This format is to get the Fractional seconds. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned.

I am not clear as to what do you mean by milliseconds and microseconds in the output ? what is the output are you expecting from above command.

Thanks
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
jahooperAuthor Commented:
milliseconds = thousandths of a second (e.g., 1.123 seconds)
microseconds = millionths of a second (e.g., 1.123456 seconds)

So, the FF6 should give me microseconds, yet it's only giving me milliseconds...  Referring back to my original question, the output I got was "10/02/2007 14:30:49.646000"...  The output I expected was "10/02/2007 14:30:49.646123"...  In other words, I always get "000" in the last three decimal places, when I should be getting actual values there...
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Ok.. i understand your question now. Did you execute the select in sql*plus or toad or any other tool ?

I am able to see the below :

19:09:32 SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY HH24:MI:SS.FF6') x FROM DUAL;
x
-----------------------------
10/08/2007 12:09:32.795395   -- i am getting output as we expected.
1 row selected.
Elapsed: 00:00:00.05

Thanks
0
 
jahooperAuthor Commented:
I executed in PL/SQL Developer, which is the only tool my client uses for its Oracle database developers...
0
 
jahooperAuthor Commented:
According to the DBA here and some other posts I ran across, this is a limitation of Windows, not the tool I'm using...  But nav_kum_v gets the points for the attempt...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.