Oracle's timestamp field is being damaged by Coldfusion

[Schworak]

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?

[Schworak]

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

[-__cfSearching__-]

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#

[Schworak]

oracle.sql.TIMESTAMP

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

[-__cfSearching__-]

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

[BKBK]

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.

[Adam Cameron.]

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