Oh, it just gets better.
ColdFusion (and, to be fair, Railo too) mess up dates when (de)serialising JSON too. This is on CF9.0.1 and Railo 4.1.
Code:
<cfscript>
dates = queryNew("");
queryAddColumn(dates, "id", [1,2,3,4,5,6,7]);
queryAddColumn(dates, "dob", [
createDate(1933,2,20),
createDate(1933,11,6),
createDate(1959,9,1),
createDate(1961,7,12),
createDate(1970,2,17),
createDate(1990,10,5),
createDate(2011,3,24)
]);
json = serializeJson(dates, true);
deserialised = deserializeJson(json, false);
</cfscript>
<cfquery name="filteredDates" dbtype="query">
SELECT *
FROM dates
WHERE dob > <cfqueryparam value="#createDate(1958,10,18)#" cfsqltype="CF_SQL_TIMESTAMP">
</cfquery>
<cfquery name="filteredDeserialisedDates" dbtype="query">
SELECT *
FROM deserialised
WHERE dob > <cfqueryparam value="#createDate(1958,10,18)#" cfsqltype="CF_SQL_TIMESTAMP">
</cfquery>
<cfdump var="#[
dates,
filteredDates,
json,
deserialised,
filteredDeserialisedDates
]#">
This code:
- creates a query with some family birthdays in it (in case yer interested: Dad, Mum, big bro & sis, me, my niece and my son);
- serialises & deserialises it back into a query;
- does a QoQ to get the people born after M&D got married;
- for both the original query;
- and the deserialised one.
| array | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 |
| |||||||||||||||||||||||||||
| 2 |
| |||||||||||||||||||||||||||
| 3 | {"ROWCOUNT":7,"COLUMNS":["ID","DOB"],"DATA":{"ID":[1,2,3,4,5,6,7],"DOB":["February, 20 1933 00:00:00","November, 06 1933 00:00:00","September, 01 1959 00:00:00","July, 12 1961 00:00:00","February, 17 1970 00:00:00","October, 05 1990 00:00:00","March, 24 2011 00:00:00"]}} | |||||||||||||||||||||||||||
| 4 |
| |||||||||||||||||||||||||||
| 5 |
| |||||||||||||||||||||||||||
(Railo's is pretty much the same, just eye-witheringly garish in its colour-scheme).
This dump is:
- the original query. Note the dates are ColdFusion dates;
- the original query having been filtered, just showing the kids & grand-kids (correctly);
- the JSON. Note the dates have been serialised into a JS-friendly format: "MMMM, DD YYYY HH:NN:SS";
- the deserialised query (note the dates have not been converted back to CF dates);
- the failed QoQ, because QoQ doesn't understand the format the dates have come back in.
Some observations here:
- I can't help but think a more standards-friendly format for the date serialisation might have been helpful here. The format used is - as far as I know - very Javascript-specific. And whilst JSON is a Javascript thing, it's kinda outgrown that usage. And more portable formats like "YYYY-MM-DD HH:NN:SS" still parse as dates in Javascript, plus have the benefit of being recognised as dates by QoQ too, when they come back from being serialised.
-
That said, if I extend the code a bit to include this:
<cfoutput> Value: #deserialised.dob[1]#<br> isDate: #isDate(deserialised.dob[1])#<br> dateAdd: #dateAdd("d", 0, deserialised.dob[1])#<br> </cfoutput>
Then I see that ColdFusion thinks that date-string-format is just fine:
Value: February, 20 1933 00:00:00
isDate: YES
dateAdd: {ts '1933-02-20 00:00:00'} - so the problem here is a query-of-query one, not an intrinsic problem with the way dates are (de)serialised.
- However. I think ColdFusion's date deserialisation is being a bit slack here. Reading up on the general tenets of how to deal with dates with JSON (which has been identified as a shortcoming of sorts), then the general gist is to decide/agree on an interchange format (fine), but then actually do the interchange. So whilst CF is doing an adequate job of the serialisation, it doesn't actually bother to do the deserialisation. Having deserialised a previously serialised date, one should end up with a ColdFusion date, not still have the serialised string.
Sigh.
--
Adam
