Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9894
  • Last Modified:

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...
0
jahooper
Asked:
jahooper
  • 3
  • 3
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
Naveen KumarProduction 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
 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now