Adam Cameron's Dev Blog: What you see is not necessarily what you get

G'day
This is another case of me not having enough space on Stack Overflow to articulate myself properly, so I'm creating a blog article and will cross reference my answer to here. With enough of a summary on S/O to try to avoid snippy comments from the self-appointed sheriffs.

The question I'm looking at is this:

If I run the query:
<cfquery name="gGet" datasource="#application.datasource#">
   select dateuploaded from table
</cfquery>
I get a lovely datetime as follows:

2012-12-01 12:46:00

But, if I create a new query object and insert the datetime into it
<cfset qReturn = queryNew("deployDate", "Date")>
<cfoutput query="qGet" startrow="1" maxrows="7">
    <cfset queryAddRow(qReturn)>
    <cfset querySetCell(qReturn,"deployDate",qGet.dateuploaded)>
</cfoutput>
I get this awful timestamp rubbish {ts '2012-12-01 12:46:00'}

What should I do to retain the datetime?

Alternatively, how would I re-format the timestamp into a datetime?

I hate to say it, but the perceived problem is borne of ignorance. There is nothing wrong with ignorance (and, indeed, years ago I asked exactly the same sort of question), but it's important tobe disabused of it.

Here we go...

What the person is looking at is the result of ColdFusion converting a date/time object to a string so it can output it. This is likely effected by calling the object's toString() method.

So how a given class decides to implement its toString() method is neither here nor there as far as what the actual underlying datetype is.

This code demonstrates:

<cfscript>
    dbData = new Query(sql="SELECT * FROM tbl_dates", datasource="scratch_mysql").execute().getResult();
    results = {
        now                    = valueAndType(now()),
        createDate            = valueAndType(createDate(2012, 12, 1)),
        createDateTime        = valueAndType(createDateTime(2012, 12, 1, 9, 15, 15)),
        createOdbcDateTime    = valueAndType(createOdbcDateTime(now())),
        parseDateTime        = valueAndType(parseDateTime("2012-12-01 09:17:18")),
        lsParseDateTime        = valueAndType(lsParseDateTime("2012-12-01 09:17:18")),
        dateTimeCol            = valueAndType(dbData.dateTimeCol[1]),
        dateCol                = valueAndType(dbData.dateCol[1]),
        timeCol                = valueAndType(dbData.timeCol[1]),
        timestampCol        = valueAndType(dbData.timestampCol[1])
    };
    writeDump(results);
    
    struct function valueAndType(obj){
        return {
            value    = obj,
            class    = obj.getClass().getName()
        };
    }
</cfscript>

struct

CREATEDATE

struct
CLASS	coldfusion.runtime.OleDateTime
VALUE	{ts '2012-12-01 00:00:00'}

CREATEDATETIME

struct
CLASS	coldfusion.runtime.OleDateTime
VALUE	{ts '2012-12-01 09:15:15'}

CREATEODBCDATETIME

struct
CLASS	coldfusion.runtime.OleDateTime
VALUE	{ts '2012-12-01 09:33:38'}

DATECOL

struct
CLASS	java.sql.Date
VALUE	{ts '2012-12-01 00:00:00'}

DATETIMECOL

struct
CLASS	java.sql.Timestamp
VALUE	2012-12-01 09:24:35.0

LSPARSEDATETIME

struct
CLASS	coldfusion.runtime.OleDateTime
VALUE	{ts '2012-12-01 09:17:18'}

NOW

struct
CLASS	coldfusion.runtime.OleDateTime
VALUE	{ts '2012-12-01 09:33:38'}

PARSEDATETIME

struct
CLASS	coldfusion.runtime.OleDateTime
VALUE	{ts '2012-12-01 09:17:18'}

TIMECOL

struct
CLASS	java.sql.Time
VALUE	{ts '1970-01-01 09:24:35'}

TIMESTAMPCOL

struct
CLASS	java.sql.Timestamp
VALUE	2012-12-01 09:24:35.0

Note that all CF-originated date/time type objects are of type coldfusion.runtime.OleDateTime (which is a bit of a misnomer, as I doubt these things have anything to do with OLE, but are just a hangover of CF using OLEDB drivers, over a decade ago), and the various columns in the DB are reflected by various Java date / time types (as prescribed by JDBC). Oh, the DB table I am querying has this schema:


delimiter $$

CREATE TABLE `tbl_dates` (
  `id` int(11) NOT NULL,
  `dateTimeCol` datetime DEFAULT NULL,
  `dateCol` date DEFAULT NULL,
  `timeCol` time DEFAULT NULL,
  `timestampCol` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

The bottom line is all the various date/time object types are interchangeable in CFML (obviously giving consideration to whether they have time components or not), so there is no issue here.

Aside:
For the sake of completeness I ran the same code on Railo, and all the variables were of type railo.runtime.type.dt.DateTimeImpl. See Andrew's comment below for and my response for some discussion on this

I tried to run the code on OpenBD, but it errored (OpenBD does not support that query syntax), and I could not be bothered updating my code just to make OpenBD work - catch up, guys, will ya? - so didn't test.

If the person asking is using this data in such a way that the string representation of the object matters, I'd say they are almost certainly doing something wrong.

If they need the values to all be the same time, then there are probably constructors or methods on each class which will convert one to the other. I'll leave that investigation up to them.

Right: breakfast. More importantly: coffee.

--
Adam

Saturday, 1 December 2012

What you see is not necessarily what you get