Skip navigation
Schworak
Currently Being Moderated

Oracle's timestamp field is being damaged by Coldfusion

Apr 12, 2012 2:05 PM

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?

 
Replies
  • Currently Being Moderated
    Apr 12, 2012 2:49 PM   in reply to Schworak

    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#

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2012 4:13 PM   in reply to Schworak

    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).

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 23, 2012 12:46 AM   in reply to Schworak

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 23, 2012 1:07 AM   in reply to Schworak

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points