Monday 1 July 2013

ColdFusion vs Query of Query: Dead Dates

G'day:
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.
And the result:


array
1
query
DOBID
1{ts '1933-02-20 00:00:00'}1
2{ts '1933-11-06 00:00:00'}2
3{ts '1959-09-01 00:00:00'}3
4{ts '1961-07-12 00:00:00'}4
5{ts '1970-02-17 00:00:00'}5
6{ts '1990-10-05 00:00:00'}6
7{ts '2011-03-24 00:00:00'}7
2
query
DOBID
1{ts '1959-09-01 00:00:00'}3
2{ts '1961-07-12 00:00:00'}4
3{ts '1970-02-17 00:00:00'}5
4{ts '1990-10-05 00:00:00'}6
5{ts '2011-03-24 00:00:00'}7
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
query
DOBID
1February, 20 1933 00:00:001
2November, 06 1933 00:00:002
3September, 01 1959 00:00:003
4July, 12 1961 00:00:004
5February, 17 1970 00:00:005
6October, 05 1990 00:00:006
7March, 24 2011 00:00:007
5
query
DOBID

(Railo's is pretty much the same, just eye-witheringly garish in its colour-scheme).

This dump is:
  1. the original query. Note the dates are ColdFusion dates;
  2. the original query having been filtered, just showing the kids & grand-kids (correctly);
  3. the JSON. Note the dates have been serialised into a JS-friendly format: "MMMM, DD YYYY HH:NN:SS";
  4. the deserialised query (note the dates have not been converted back to CF dates);
  5. the failed QoQ, because QoQ doesn't understand the format the dates have come back in.

Some observations here:
  1. 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.
  2. 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'}
  3. so the problem here is a query-of-query one, not an intrinsic problem with the way dates are (de)serialised.
  4. 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.
OK, I leave this one here. I'm now back on deck with my day job (it's 9:05am), and whilst this is all related to my current work task, I should be working on a fix for it, not just yabbering about it.

Sigh.

--
Adam