/t5/coldfusion-discussions/mysql-timestamp-data-type-issue/td-p/2283930Nov 05, 2009
Nov 05, 2009
Copy link to clipboard
Copied
Using ColdFusion 8 and MySql 5.0.77
For any of my innoDB tables with columns of data type "timestamp" with or without default values, I get a .0 concatenated to the end. This does not display when viewing data from within MySql Administrator only once it's in ColdFusion.
Example:
2009-11-04 17:39:35.0
it should be:
2009-11-04 17:39:35
I've done some googling but can't seem to find what is causing this.
I'm partial to Date/Time formation functions in CF but thought have, on occasion, opted to do this stuff in the DB and thought I'd post the suggestion in case it helps you.
Formatting at the app level is how I currently remedy this, but it seems silly to me to have to hand hold a distinct data type such as timestamp. I am also fully aware that it may be my setup causing this. We run CF8 on linux redhat with JBoss as our server.
Example usage:
I have CF DAO's which query all fields using the same extended read method from an abstract object. I then use a single RDC method to return the query object and convert it to a JSON object (used with Extjs, the native returnFormat="json" does not work) and pass it along to the client. In this JSON conversion I check the columns getMetaData().getColumnTypeName(), if it's timestamp, then I format it. However, if the value that coldfusion return for timestamps does not have the superfulous ".0" concatenated, Extjs (and I assume other AJAX libs) can deal with the formatting on their own, which is obviously the most desirable. So what I'm really pinging this forum for is: Why does CF do this? Or better yet, is it a JDBC issue? Is it my configuration causing the problem? How can I get CF to return the value IN the database and not what it is currently returning.
I think it might be a driver issue. I just tried a test in ColdFusion 8, ColdFusion 9, Railo 3.1.1 and OpenBD 1.1. CF8, CF9, and OpenBD all produce similar output to one another with CF8 and 9 producing identical results (.0 appended) while OpenBD appends three zeros (.000) to the output/timestamp.
Railo, on the other hand, displays the content just as it is in the MySQL database. I can't find confirmed details but it does appear that Railo uses a different JDBC driver for MySQL than Adobe CF and OpenBD. This may be off but I'm pressed to find an alternate explanation (other than the driver) why Railo returns the timestamp 'correctly' and the other engines append a decimal point and zeros to the end.
How can I get CF to return the value IN the database and not what it is currently returning.
I think you're making some false assumptions here.
What's stored in the DB is not a sequence of characters YYYY-MM-DD HH:MM:SS, it'll be some binary data that is a "datetime" according to how the developers of MySQL decided to store it. It's probably stored as two integers, which act as a date & time offset from some epoch date.
What you're seeing on the screen of you MySQL client application is just how that application chooses to render it for human eyes. Another MySQL client might display them differently (indeed, there might be a setting in your MySQL client to vary this).
Internally, MySQL might store a timestamp only down to the accuracy of SS (although this seems pathetically unlikely to me), so the human-readable representation is as accurate as MySQL can express.
On the CF side of things, a datetime object is accurate to the millisecond. When one casts a CF datetime object to a string implicitly, eg:
<cfoutput>#now()#</cfoutput>
The ms part is not represented, but one can extract it with timeFormat():
Similarly other external systems like SQL Server will store a datetime down to the ms, and indeed JDBC will handle the interchange of this data between the external system and CF. If one outputs a datetime object queried from SQL Server, one might get this:
2009-11-06 08:17:53.397
(this is not how it's actually stored, this is just the result of an implicit casting to a string so there's character data to display).
Note that the millisecond part is - predictable - expressed as a decimal fraction of the seconds value.
So what you are seeing when you have your date like 2009-11-04 17:39:35 is just JDBC's most accurate representation of the value the DB passed. The DB is not passing a ms value, so the ms part of the JDBC datetime object is 0. Remember that JDBC is not MySQL-specific,it's a standardised interface between Java and database systems, so JDBC's level of accuracy in its datatypes might be to a finer grain than the DB is capable of. By JDBC needs to expose a standardised interface to the the Java application. Just because MySQL doesn't pass a ms value doesn't mean JDBC doesn't need to.
Now... your specific problem... I presume this is only an issue when you output the value to a human? When outputting datetime value to humans, it's an idea to specify the format you want, rather than just having CF implicitly cast the thing to a string, because you will get varying results depending on what specific type of datetime object it is. However dateFormat() and timeFormat() (and their LS equivalents) will take any type of datetime object and reformat them as a string int he specified format. This is how you should output dates to humans.
/t5/coldfusion-discussions/mysql-timestamp-data-type-issue/m-p/2283937#M109367Nov 05, 2009
Nov 05, 2009
Copy link to clipboard
Copied
sing ColdFusion 8 and MySql 5.0.77
For any of my innoDB tables with columns of data type "timestamp" with or without default values, I get a .0 concatenated to the end. This does not display when viewing data from within MySql Administrator only once it's in ColdFusion.
Example:
2009-11-04 17:39:35.0
it should be:
2009-11-04 17:39:35
I've done some googling but can't seem to find what is causing this.
Nicora, hang on to your instincts. I do believe you are right. It looks fishy. Coldfusion does something similar with
<cfset x.y=1> <cfdump var="#serializeJSON(x)#">
where it returns the value 1.0. I would call it a Coldfusion bug. If the database stores the timestamp as 2009-11-04 17:39:35, Coldfusion has no business casting the value to 2009-11-04 17:39:35.0.
If the database stores the timestamp as 2009-11-04 17:39:35, Coldfusion has no business casting the value to 2009-11-04 17:39:35.0.
But I have never seen a database store a timestamp data type as '2009-11-04 17:39:35' I have only seen that when date and|or time values are stored as a string, which is usually a bad practice. For date, time and timestamp date types, the actual data is usally something akin to 235798454. This is then cast into a string like "2009-11-04 17:39:35" when humans are looking at it.
If the database stores the timestamp as 2009-11-04 17:39:35, Coldfusion has no business casting the value to 2009-11-04 17:39:35.0.
But I have never seen a database store a timestamp data type as '2009-11-04 17:39:35' I have only seen that when date and|or time values are stored as a string, which is usually a bad practice. For date, time and timestamp date types, the actual data is usally something akin to 235798454. This is then cast into a string like "2009-11-04 17:39:35" when humans are looking at it.
Yup. It seems people are not distinguishing between "what's being displayed on the screen" and "what's being stored under the hood".
However if I look at the table data using MS SQL Server Management Studio as my client, I see this:
6/11/2009 9:51:45 a.m.
This doesn't mean that SQL Server is storing the data as "6/11/2009 9:51:45 a.m." and CF is somehow a) getting it wrong; b) making up the millseconds out of thin air, it just means thatManagement Studio is choosing to display the data like that, and CF is chosing to display it differently.
Thanks all for the responses, it's very much appreciated.
It seems clear, to me at least, that ColdFusion is getting the exact database value and not the interpreted version that my UI client (navicat) is displaying. This is a good thing.
However, in my application ColdFusion is NOT the place for value formatting, this is something that should be handled in the UI. When I query a database and convert that object to JSON sans formatting my UI breaks because the library I use does not recognize "2009-11-06 09:51:45.527" as a date when it tries to parse it. Maybe this *is* a standard format and Extjs has dropped the ball, I'm not sure yet and I will investigate (if anyone knows off the top of their head what standard it is, let me know).
So, my solution, at least for now, has been to extend Extjs's date parsing object to split on the decimal. And that's fine, the next step is to find out why Extjs can't parse "2009-11-06 09:51:45.527".
I'll update with what I find out in case someone else runs into this. Thanks again for the help.
**edit: @BKBK
I'm not using serializeJson, I instead build it manually, so the .0 exists at or before the query object level.
That's fine you don't want to format the date in ColdFusion since you are using it as a middleware back end to your User Inteface.
But you then contradict your self when you self when you say your UI does not recognize "2009-11-06 09:51:45.527" as a date. That is a formated date as is "2009-11-06 09:51:45". If your UI is expecting a formated date, then that is probably what you need to provide it.
If your UI is expecting a date data type, then it is expecting one or more integers that precicily define a date from some starting point. The problem being that different systems define different starting points and different ways to define date data types from that point.
And, as I expect you are learning, date time is a complicated topic.
However, in my application ColdFusion is NOT the place for value formatting, this is something that should be handled in the UI. When I query a database and convert that object to JSON sans formatting my UI breaks because the library I use does not recognize "2009-11-06 09:51:45.527" as a date when it tries to parse it. Maybe this *is* a standard format and Extjs has dropped the ball, I'm not sure yet and I will investigate (if anyone knows off the top of their head what standard it is, let me know).
Not a place for formatting for HUMAN consumption, no. But it's clearly the place for formatting it for passing to some other external system. There's nothing wrong - and it's entirely appropriate, given CF is acting as a broker here - for CF to handle the reformatting of the data received from the back-end system before passing it on to the front-end system. It's its job.
That said, if CF is just passing the data onwards without doing anything with it itself, I would probably do the reformatting @ DB level, rather than CF level. Fewer moving parts.
@skinner: I'm not aware of any contradition, I may not be explaining this very well.
Basically, I want to use CF (Java) to query MySql and convert the query objects to JSON, I'm doing this exclusively via RPC (*typo, changed from RDC). I would prefer to avoid any formatting in this middle-ware process. This leaves me with two options, convert the date to a consumible format on either the database, or the UI. I am chosing the UI because the library I use parses dates automatically. I simply give it a schema and it does the rest.
EX:
schema = {
fields: [{
header: "Column 1",
dataIndex: "col1",
type: "date",
format: "Y-m-d g:i:s A"
}],
data: [["2009-11-06 09:51:45.527"]]
/*
this value is a string as far as Javascript is concerned and that is what it is expecting,
the actual database field data type is timestamp. Extjs sees that the schema defines this
value as a date and uses its Date.parseDate() method to create a date object and then
uses the scehma's format property to diplay it all pretty like to the user.
*/
};
Next I pass this schema to a data store object and now I can plug it in to any number of UI components (grid, lists etc). If I chose to do this on the database side, I'm not aware of any way to have MySql automattically format all date-type fields, I'd have to do it manually (yes, I'm lazy). The problem with the above snippet is that it breaks because my UI can't parse "2009-11-06 09:51:45.527", it can however, parse "2009-11-06 09:51:45". Strange.
@Adam: You mentioned doing this on the database side, and that is certainly a good place to do it, and I'd agree that it would also be acceptable to have CF do it. But basically, I find it to be much less work if I can get the UI to do ALL (well, most anyway) of the formatting. So like I said, I'm going to sift around the Extjs forum and see why the Date object can't parse "2009-11-06 09:51:45.527". If that is working properly, then this problem is solved without any modifications to the database or CF.
@Ian Skinner But I have never seen a database store a timestamp data type as '2009-11-04 17:39:35' I have only seen that when date and|or time values are stored as a string, which is usually a bad practice.
No, it isn't bad practice. More on that in a sec. Nicora actually gives you an example of one such database, a popular one at that, MySQL.
@Adam Cameron This is a non-issue.
The argument you've used to arrive at this conclusion compares apples with apple-sauce. The subject is a comparison of timestamp with timestamp, not timestamp with datetime.
@Ian Skinner For date, time and timestamp date types, the actual data is usally something akin to 235798454. This is then cast into a string like "2009-11-04 17:39:35" when humans are looking at it.
Comes close to the real thing, at least, as I understand it. Under the hood, timestamp is an integer. However, that integer may represent the number of seconds, the number of tenths of seconds, the number of hundredths of seconds, the number of thousandths of seconds, and so on, depending on the adopted system of representation. Coldfusion or any client should be faithful to that representation.
The argument you've used to arrive at this conclusion compares apples with apple-sauce. The subject is a comparison of timestamp with timestamp, not timestamp with datetime.
No, the underlying "issue" is people confusing how these things are stored with how they are presented, and to a lesser extent how this sort of data is interchanged via intermediary agents (just as JDBC). The same applies equally for timestamps or datetimes.
The problem ended up being my error, as these things often are. I had given the UI's date parser the wrong format definition. Once I gave it the correct one: "Y-m-d H:i:s.u" it all worked.
Sorry for the tail chasing but maybe something in here can help someone else at a later date. I used to post here (by here, I mean macromedia) often under the moniker "nicorawa" but then quit with my new job once I started programming solely in Java. Now that I'm back in the CF world, it's nice to see that the community is still active.
Still: it's been an interesting discussing with some even more "interesting" points of view.
And might help someone understand some stuff, in future. As Ian said: date-handling challenges a lot of people. All of us, at some point or another, I'd say.
18
Replies
AdChoices