Friday 20 September 2013

Expectations management: date casting in QoQ

G'day:
Just a quick one today. I camne across some unexpected (to me) and pretty unhelpful behaviour in QoQ yesterday, with the cast() function.

Here's some code:

someDates = queryNew("");
queryAddColumn(someDates, "event", "timestamp", [
    now(),
    createDateTime(1997, 8, 31, 0, 23, 0),
    createDate(1959, 2, 3)
]);
asDate = new Query(
    dbtype="query",
    someDates=someDates,
    SQL="
    SELECT    CAST(event AS DATE) AS event_as_date
    FROM    someDates
").execute().getResult();

asTime = new Query(
    dbtype="query",
    someDates=someDates,
    SQL="
    SELECT    CAST(event AS TIME) AS event_as_time
    FROM    someDates
").execute().getResult();

asTimestamp = new Query(
    dbtype="query",
    someDates=someDates,
    SQL="
    SELECT    CAST(event AS TIMESTAMP) AS event_as_timestamp
    FROM    someDates
").execute().getResult();

writeDump([someDates,asDate,asTime,asTimestamp]);

Basically I'm using the cast() function to cast some dates to each of DATE, TIME and TIMESTAMP.

Before running that (note: for differing reasons, this code will not run on cflive.net on either CF or Railo, sorry), try to guess what the results will be.

My expectation that the first one would return 2013-09-20, 1997-08-31 and 1959-02-03; the second 08:52:32, 00:23:00 and 00:00:00; the third 2013-08-31 08:52:32, 1997-08-31 00:23:00 and 1959-02-03 00:00:00.IE: when I ask for a date, I get a date, when I ask for a time I get a time, and when I ask for a timestamp, I get a timestamp. Crazy, I know.

However what I get is this:

array
1
query
EVENT
1{ts '2013-09-20 08:47:47'}
2{ts '1997-08-31 00:23:00'}
3{ts '1959-02-03 00:00:00'}
2
query
EVENT_AS_DATE
1{ts '2013-09-20 08:47:47'}
2{ts '1997-08-31 00:23:00'}
3{ts '1959-02-03 00:00:00'}
3
query
EVENT_AS_TIME
1{ts '2013-09-20 08:47:47'}
2{ts '1997-08-31 00:23:00'}
3{ts '1959-02-03 00:00:00'}
4
query
EVENT_AS_TIMESTAMP
12013-09-20 08:47:47.739
21997-08-31 00:23:00.0
31959-02-03 00:00:00.0

(Railo is the same, for all intents and purposes. Just in achromotopsaic colours).

The only bloody difference is that the last one returns the thing as a string, and the the other two just return date/times. So what's the bloody point of offering DATE, TIME and TIMESTAMP casting options if they don't actually do what they claim to do?

I'll raise a bug for this: 3636794 / RAILO-2616.

--
Adam
at