nls_timestamp_format and systimestamp

Syed Ullah
    Just wondering why systimestamp output is not influenced by the nls_timestamp_format session variable. sysdate output seems to change when nls_date_format is changed. Anyone knows why the behavior differs?
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE    11.2.0.2.0      Production
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    
    SQL> SELECT SYSDATE FROM DUAL;
    
    SYSDATE
    ---------
    02-FEB-12
    
    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
    
    Session altered.
    
    SQL> SELECT SYSDATE FROM DUAL;
    
    SYSDATE
    ----------
    2012-02-02
    
    SQL> SELECT SYSTIMESTAMP FROM DUAL;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    02-FEB-12 02.03.13.441081 PM -06:00
    
    SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
    
    Session altered.
    
    SQL> SELECT SYSTIMESTAMP FROM DUAL;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    02-FEB-12 02.03.14.785289 PM -06:00
      • 1. Re: nls_timestamp_format and systimestamp
        Rene Argento
        When you select SYSTIMESTAMP the data type returned is TIMESTAMP WITH TIME ZONE.

        You need to alter the NLS_TIMESTAMP_TZ_FORMAT to see the changes.
        SELECT systimestamp FROM dual;
        
        SYSTIMESTAMP
        ---------------------------------------------------------------------------
        02/02/12 18.21.15,541000 -02:00
        
        
        ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
        
        
        SELECT systimestamp FROM dual;
        
        SYSTIMESTAMP
        ---------------------------------------------------------------------------
        2012-02-02 06.21.41.355289
        • 2. Re: nls_timestamp_format and systimestamp
          908002
          ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';

          Session altered.
          • 3. Re: nls_timestamp_format and systimestamp
            Centinul
            It's because (per the docs) the return type of SYSTIMESTAMP is not a TIMESTAMP, but:
            SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.
            SQL> SELECT systimestamp FROM DUAL;
            
            SYSTIMESTAMP
            ---------------------------------------------------------------------------
            02-FEB-12 03.24.37.812747 PM -05:00
            
            SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SSXFF TZR';
            
            Session altered.
            
            SQL> SELECT systimestamp FROM DUAL;
            
            SYSTIMESTAMP
            ---------------------------------------------------------------------------
            2012/02/02 15:25:17.039748 -05:00
            • 4. Re: nls_timestamp_format and systimestamp
              Syed Ullah
              Makes sense. Thanks!