DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 12

Thread: Timestamp format

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Timestamp format

    How can I have the default for a TIMESTAMP datatype attribute inserted in the following format?
    2006-08-21-16.37.30.459999

    When I use systimestamp, I get the following timestamp format:
    21-AUG-06 05.19.22.562000 PM +05:30

    Thanks in advance.

  2. #2
    Join Date
    Mar 2006
    Posts
    74
    erm..

    insert into timestamp_table(timestamp_column)
    select to_timestamp('2006-08-21-16.37.30.459999', 'YYYY-MM-DD-HH24.MI.SS.FF6') from dual

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Quote Originally Posted by cjard
    select to_timestamp('2006-08-21-16.37.30.459999', 'YYYY-MM-DD-HH24.MI.SS.FF6') from dual

    The above give timestamp values in following format:
    31-DEC-99 11.59.59.999999000 PM

    However, the requirement is to have timestamp in the following format:
    9999-12-31-23.59.59.999999

    How can this be done without making changes to NLS_TIMESTAMP_FORMAT?

    Thanks.
    Last edited by ggnanaraj; 08-22-2006 at 01:59 AM.

  4. #4
    Join Date
    May 2005
    Location
    France
    Posts
    34
    Quote Originally Posted by ggnanaraj
    The above give timestamp values in following format:
    31-DEC-99 11.59.59.999999000 PM

    However, the requirement is to have timestamp in the following format:
    9999-12-31-23.59.59.999999

    How can this be done without making changes to NLS_TIMESTAMP_FORMAT?

    Thanks.
    As cjard said, you have to understand that dates and timestamps are stored in an internal format, NOT STRINGS, strings are just for DISPLAY. SO ALWAYS CONVERT EXPLICITLY :

    - a DATE or TIMESTAMP TO STRING using TO_CHAR when you want to display it
    - a STRING to DATE or TIMESTAMP using TO_DATE or TO_TIMESTAMP when you want to insert/update/compare dates ...

    You can have a look here if you want to know how dates are stored internally. (excerpt from the OCCI documentation).

    HTH & Regards,

    rbaraer

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Quote Originally Posted by RBARAER
    As cjard said, you have to understand that dates and timestamps are stored in an internal format, NOT STRINGS, strings are just for DISPLAY. SO ALWAYS CONVERT EXPLICITLY :
    Thanks for the input.

    The idea is to maintain the storage/display formats of TIMESTAMP - externally - without change to already existing application code, which is to be moved to work on Oracle. I'm not concerned about how it is stored internally. The timestamp format expected when doing a SELECT or INSERT is 2006-08-21-16.37.30.459999.

    The 'definition of the table can be altered to a certain extent with respect to defaults' and so can the 'session settings' but code is to be changed only if the previous 2 cannot handle the requirement.

    Given this requirement, the issue can be resolved only by use of session applicable code as follows:

    alter session SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';

    Thanks & Regards.

    ggnanaraj

  6. #6
    Join Date
    May 2005
    Location
    France
    Posts
    34
    ALTER SESSION might match your needs for now, but it is a very bad habit IMO to rely on the session date format. I'm pretty sure you will regret it in the long term. So as a short-term solution that can be OK, but keep in mind that this should be changed in the future.

    What we do in order to use the same format everywhere we need to is using a date format as a package variable and use it instead of hard-coded string format. For example :

    Code:
    CREATE OR REPLACE PACKAGE PKG_Dates AS
    
        GstrDateFormat VARCHAR2(25) := 'YYYY/MM/DD HH24:MI:SS';
        GstrTimestampFormat VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS.FF6';
    
    END PKG_Dates;
    /
    This does not work in pure SQL since package variables cannot be called from there, but works perfectly in all PL/SQL code. For example :

    Code:
    rbaraer@Ora10g> var rc REFCURSOR
    rbaraer@Ora10g> BEGIN
        OPEN :rc FOR
            SELECT TO_CHAR(SYSDATE, PKG_Dates.GstrDateFormat),
                TO_CHAR(SYSTIMESTAMP, PKG_Dates.GstrTimestampFormat)
            FROM DUAL;
    END;  2    3    4    5    6
      7  /
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g> PRINT rc
    
    TO_CHAR(SYSDATE,:B2)
    ---------------------------------------------------------------------------
    TO_CHAR(SYSTIMESTAMP,:B1)
    ---------------------------------------------------------------------------
    2006/08/22 10:49:54
    2006/08/22 10:49:54.949515
    
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer

  7. #7
    Join Date
    Mar 2006
    Posts
    74
    what you need to understand is that both dates and timestamps are internally stored as numbers - a value of the number of days and fractional days since a specific point in time. if used in a query, they are to_char() automatically with a certain format, if inserted, they are to_date() or to_timestamp() also with that certain format. I dont know if enough of the internals are revealed to e.g. allow you to insert the number 1.5 into a date column (thereby giving you a date that is 1.5 days after the epoch) so all insertion into, modification of and selection from date and time type columns must be done with sending in a representation of the time in format X and addionally a description of that format X in oracle compliant formatting

  8. #8
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Thanks for the input.

    The issue was resolved by also setting NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';

    Otherwise get the error:
    SQL> INSERT INTO ADMIN.RESUEST VALUES(1, 1, DEFAULT, 1, DEFAULT, DEFAULT);
    INSERT INTO ADMIN.RESUEST VALUES(1, 1, DEFAULT, 1, DEFAULT, DEFAULT)
    *
    ERROR at line 1:
    ORA-01847: day of month must be between 1 and last day of month
    SQL>

    Thanks once again.

  9. #9
    Join Date
    Mar 2006
    Posts
    74
    indeed... you can set the system wide default format for a timestamp to be that.. but bear in mind then that oracle will only thus go and do an implicit TO_CHAR or TO_TIMESTAMP using that format string..

    In all programming situations, it pays to be explicit rather than implicit; assuming a computer can think gets you into trouble.

    additionally, when deviating from the default values, you should be aware that your change may break other code that also does its work implicitly with the old format. If you ahve a legacy app that is inserting or selecting timestamp data and is expecting it to be in some particular format, you potentially just broke it

    The solution? Dont change defaults. Dont rely on implicit conversions. Be thorough now to avoid debugging later

  10. #10
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Quote Originally Posted by cjard
    indeed... you can set the system wide default format for a timestamp to be that..
    The change was made for the session not system wide...

    alter session SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width