6 Replies Latest reply on Jan 22, 2018 3:08 AM by Gaz in Oz

    Unit Test: Can not add formulas for timestamp with time zone defined datatype

    Paul Simmons

      It appears that the input field for parameters in unit tests have implicit datatype conversion tests.  This is causing problems when adding formulas to fields.  I have found this significantly in fields that are defined as the datatype timestamp with time zone.  It raises the following error.

       

      unittest_timestamp_parameters.png

       

      Database: OracleXE v11.2.0.2.0

      SQL Developer: 17.4.0.355

       

      Thanks in advance.

        • 1. Re: Unit Test: Can not add formulas for timestamp with time zone defined datatype

          It appears that the input field for parameters in unit tests have implicit datatype conversion tests.  This is causing problems when adding formulas to fields.  I have found this significantly in fields that are defined as the datatype timestamp with time zone.

          Look at the datatypes in what you posted.

           

          The parameter is timestamp with time zone but you are providing a timestamp by using the to_timestamp function.

           

          A timestamp does NOT have a time zone but a timestamp with time zone requires one.

           

          Use the TO_TIMESTAMP_TZ function to provide the timezone.

          https://docs.oracle.com/database/121/SQLRF/functions230.htm#SQLRF06143

          • 2. Re: Unit Test: Can not add formulas for timestamp with time zone defined datatype
            Paul Simmons

            my assertion still remains true, even if I use systimestamp

            unittest_timestamp_parameters2.png

            • 3. Re: Unit Test: Can not add formulas for timestamp with time zone defined datatype

              And my answer is the same

              Use the TO_TIMESTAMP_TZ function to provide the timezone.

              • 4. Re: Unit Test: Can not add formulas for timestamp with time zone defined datatype
                Paul Simmons

                systimestamp provides timezone but to your point ...

                 

                select to_timestamp_tz(sysdate+1/1440) from dual;

                 

                TO_TIMESTAMP_TZ(SYSDATE+1/1440)   

                -----------------------------------

                2018-01-22 07:49:54.000000000+08:00

                 

                unittest_timestamp_parameters3.png

                • 5. Re: Unit Test: Can not add formulas for timestamp with time zone defined datatype
                  Gaz in Oz

                  Paul wrote:

                   

                  systimestamp provides timezone but to your point ...

                   

                  select to_timestamp_tz(sysdate+1/1440) from dual;

                   

                  TO_TIMESTAMP_TZ(SYSDATE+1/1440)  

                  -----------------------------------

                  2018-01-22 07:49:54.000000000+08:00

                  You should not use "TO_TIMESTAMP_TZ(char, fmt [, nls_parm])" that way, as the first arg is supposed to be a char. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions194.htm

                  You can, how ever, insert "SYSTIMESTAMP", so this could very well be a bug.

                  Raise an SR with support.oracle.com

                   

                  NOTE: A workaround may be to use sysdate and allow implicit conversion to "TIMESTAMP(6) WITH TIME ZONE".

                  OR use TO_TIMESTAMP_TZ (as rp0428 suggests) properly. Of course, using DATE datatype conversion, you will always get 0 for fractions of a second as DATE goes down to seconds.

                  SQL> select * from v$version where rownum = 1;
                  
                  BANNER
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
                  
                  SQL>
                  SQL> CREATE TABLE d_ts_tstz (d DATE, ts TIMESTAMP, tstz TIMESTAMP WITH TIME ZONE)
                    2  /
                  
                  Table created.
                  
                  SQL> desc d_ts_tstz
                  Name                                      Null?    Type
                  ----------------------------------------- -------- ----------------------------
                  D                                                  DATE
                  TS                                                 TIMESTAMP(6)
                  TSTZ                                               TIMESTAMP(6) WITH TIME ZONE
                  
                  SQL> insert into d_ts_tstz values(sysdate, sysdate, sysdate);
                  
                  1 row created.
                  
                  SQL> insert into d_ts_tstz values(sysdate, sysdate, to_timestamp_tz(sysdate + 1/1440));
                  insert into d_ts_tstz values(sysdate, sysdate, to_timestamp_tz(sysdate + 1/1440))
                                                                                        *
                  ERROR at line 1:
                  ORA-01840: input value not long enough for date format
                  
                  
                  SQL> insert into d_ts_tstz values(sysdate, sysdate, sysdate + 1/1440);
                  
                  1 row created.
                  
                  SQL> insert into d_ts_tstz (d, ts, tstz) values (sysdate, sysdate, systimestamp);
                  
                  1 row created.
                  
                  SQL> insert into d_ts_tstz (d, ts, tstz) values (
                     2    sysdate, 
                     3    sysdate, 
                     4    to_timestamp_tz(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
                     5 );
                  
                  1 row created.
                  
                  SQL>
                  

                  Note line 24 INSERT actually throws ORA-01840

                  • 6. Re: Unit Test: Can not add formulas for timestamp with time zone defined datatype
                    Gaz in Oz

                    ...or

                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  insert into  d_ts_tstz (d, ts, tstz) values (
                      2    sysdate,
                      3    sysdate,
                      4    to_timestamp_tz(to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff6'), 'yyyy-mm-dd hh24:mi:ss.ff6')
                      5* )
                    SQL> /
                    
                    1 row created.
                    
                    SQL>
                    

                    to preserve the fractions of a second.

                     

                    Try that. (to_timestamp_tz(to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff6'), 'yyyy-mm-dd hh24:mi:ss.ff6')).