Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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#
Copy link to clipboard
Copied
oracle.sql.TIMESTAMP
When I do a CFDUMP of the query, I get an object of that same type in each field.
Copy link to clipboard
Copied
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).
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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