6 Replies Latest reply: Apr 23, 2012 1:07 AM by Adam Cameron. RSS

    Oracle's timestamp field is being damaged by Coldfusion

    Schworak Community Member

      This was a shocker but it is totally reproducable. We have Oracle 10g and Coldfusion

       

       

       

       

      <cfquery name="getTS" datasource="oracle_dsn">

          select to_timestamp('12-APR-12 11.41.17.012000000 AM') as ts from dual

      </cfquery>

       

      <cfoutput>#getTS.ts#</cfoutput>

       

       

      You will find that the output doesn't match the timestamp. If you run the SQL directly against Oracle there will be a zero just after the decimal point just like it is in the query. But when it gets displayed to the screen or accessed in any way by Coldfusion, the zero is stripped out. The code above produces this result:

       

       

      Oracle: 12-APR-12 11.41.17.012000000 AM

       

      Coldfusion: 2012-04-12 11:41:17.12

       

       

       

      Of course, the trailing zeros in CF are not the issue. the fact that CF has dropped the zero after the decimal point is a very serious issue. I have tried this with many timestamps and any time a zero is after the decimal point, Coldfusion damages the data.

       

      It doesn't matter how many zeros are after the decimal point. All of them are stripped until the first non-zero digit is reached. Here is another example:

       

      <cfquery name="getTS" datasource="cf_oracle_crossdb_ru_dev">

          select

              to_timestamp('12-APR-12 11.41.17.9 AM') as ts1,

              to_timestamp('12-APR-12 11.41.17.09 AM') as ts2,

              to_timestamp('12-APR-12 11.41.17.009 AM') as ts3,

              to_timestamp('12-APR-12 11.41.17.0009 AM') as ts4,

              to_timestamp('12-APR-12 11.41.17.00009 AM') as ts5,

              to_timestamp('12-APR-12 11.41.17.000009 AM') as ts6

          from dual

      </cfquery>

       

      <cfoutput>

          #getTS.ts1#<br>

          #getTS.ts2#<br>

          #getTS.ts3#<br>

          #getTS.ts4#<br>

          #getTS.ts5#<br>

          #getTS.ts6#<br>

      </cfoutput>

       

      Coldfusion kicks out this...

       

      2012-04-12 11:41:17.9

      2012-04-12 11:41:17.9

      2012-04-12 11:41:17.9

      2012-04-12 11:41:17.9

      2012-04-12 11:41:17.9

      2012-04-12 11:41:17.9

       

       

      OH MY!

       

      Any thoughts?

        • 1. Re: Oracle's timestamp field is being damaged by Coldfusion
          Schworak Community Member

          As a work around I have turned all the TIMESTAMP fiels into strings using TO_CHAR on the Oracle side. This fixes my issue but it pretty much means that working with timestamps that come out of Oracle is very dangerous if you count on the data being correct and actually working as if it was a a real timestamp.

           

           

          Revised code:

           

          <cfquery name="getTS" datasource="cf_oracle_crossdb_ru_dev">

           

              select

                  to_char(to_timestamp('12-APR-12 11.41.17.9 AM')) as ts1,

                  to_char(to_timestamp('12-APR-12 11.41.17.09 AM')) as ts2,

                  to_char(to_timestamp('12-APR-12 11.41.17.009 AM')) as ts3,

                  to_char(to_timestamp('12-APR-12 11.41.17.0009 AM')) as ts4,

                  to_char(to_timestamp('12-APR-12 11.41.17.00009 AM')) as ts5,

                  to_char(to_timestamp('12-APR-12 11.41.17.000009 AM')) as ts6

              from dual

          </cfquery>

           

          <cfoutput>

              #getTS.ts1#<br>

              #getTS.ts2#<br>

              #getTS.ts3#<br>

              #getTS.ts4#<br>

              #getTS.ts5#<br>

              #getTS.ts6#<br>

          </cfoutput>

           

           

           

          Coldfusion kicks out:

           

          12-APR-12 11.41.17.900000000 AM

          12-APR-12 11.41.17.090000000 AM

          12-APR-12 11.41.17.009000000 AM

          12-APR-12 11.41.17.000900000 AM

          12-APR-12 11.41.17.000090000 AM

          12-APR-12 11.41.17.000009000 AM

          • 2. Re: Oracle's timestamp field is being damaged by Coldfusion
            -==cfSearching==- Community Member

            I do not typically use Oracle. But out of curiousity, what is the data type returned by the first query?

             

                       ie  #getTS.ts2[1].getClass().name#

            • 3. Re: Oracle's timestamp field is being damaged by Coldfusion
              Schworak Community Member

              oracle.sql.TIMESTAMP

               

               

              When I do a CFDUMP of the query, I get an object of that same type in each field.

              • 4. Re: Oracle's timestamp field is being damaged by Coldfusion
                -==cfSearching==- Community Member

                Well there goes my half-baked theory.

                 

                 

                Though in general, I would look at the underlying timestamp / numeric value, rather than just the friendly string representation, which is sometimes different (depending on the object).

                • 5. Re: Oracle's timestamp field is being damaged by Coldfusion
                  BKBK MVP

                  Schworak wrote:

                   

                  This was a shocker but it is totally reproducable. We have Oracle 10g and Coldfusion

                   

                   

                   

                   

                  <cfquery name="getTS" datasource="oracle_dsn">

                      select to_timestamp('12-APR-12 11.41.17.012000000 AM') as ts from dual

                  </cfquery>

                   

                  <cfoutput>#getTS.ts#</cfoutput>

                   

                   

                  You will find that the output doesn't match the timestamp. If you run the SQL directly against Oracle there will be a zero just after the decimal point just like it is in the query. But when it gets displayed to the screen or accessed in any way by Coldfusion, the zero is stripped out. The code above produces this result:

                   

                   

                  Oracle: 12-APR-12 11.41.17.012000000 AM

                   

                  Coldfusion: 2012-04-12 11:41:17.12

                   

                   

                   

                  Of course, the trailing zeros in CF are not the issue. the fact that CF has dropped the zero after the decimal point is a very serious issue. I have tried this with many timestamps and any time a zero is after the decimal point, Coldfusion damages the data.

                   

                  It doesn't matter how many zeros are after the decimal point. All of them are stripped until the first non-zero digit is reached. Here is another example:

                   

                  <cfquery name="getTS" datasource="cf_oracle_crossdb_ru_dev">

                      select

                          to_timestamp('12-APR-12 11.41.17.9 AM') as ts1,

                          to_timestamp('12-APR-12 11.41.17.09 AM') as ts2,

                          to_timestamp('12-APR-12 11.41.17.009 AM') as ts3,

                          to_timestamp('12-APR-12 11.41.17.0009 AM') as ts4,

                          to_timestamp('12-APR-12 11.41.17.00009 AM') as ts5,

                          to_timestamp('12-APR-12 11.41.17.000009 AM') as ts6

                      from dual

                  </cfquery>

                   

                  <cfoutput>

                      #getTS.ts1#<br>

                      #getTS.ts2#<br>

                      #getTS.ts3#<br>

                      #getTS.ts4#<br>

                      #getTS.ts5#<br>

                      #getTS.ts6#<br>

                  </cfoutput>

                   

                  Coldfusion kicks out this...

                   

                  2012-04-12 11:41:17.9

                  2012-04-12 11:41:17.9

                  2012-04-12 11:41:17.9

                  2012-04-12 11:41:17.9

                  2012-04-12 11:41:17.9

                  2012-04-12 11:41:17.9

                   

                   

                  OH MY!

                   

                  Any thoughts?

                  Possibly a bug! You should report it.

                  • 6. Re: Oracle's timestamp field is being damaged by Coldfusion
                    Adam Cameron. Community Member

                    This was a shocker but it is totally reproducable. We have Oracle 10g and Coldfusion

                     

                    Which version of CF?  Have you tested this on the JDBC drivers that ship with CF10?

                     

                    --

                    Adam