SerializeJSON and datetime

dev
1

Hi,

When I do an SQL query that return a datetime column, the value look like that : {ts ‘2021-01-29 11:45:26’}

If I use SerializeJSON on this value, I get “January, 29 2021 11:45:26 -0500”.

Is there a way to tell SerializeJSON to format the datetime in a specific format, like “2021-01-29 11:45:26” (yyyy-mm-dd HH:mm:ss) instead? A setting somewhere?

I know I can use DateFormat and TimeFormat, or format the date directly in the SQL query code, but I have many query results that pass through SerializeJSON and I want to find a global solution without rewriting all the code.

Thank you!

2

Not that I know of with serializeJson() specifically, though you could always write your own custom serializer (see serialize Code Examples and CFML Documentation) and achieve what you’re after, but that would still require some rewrite.

The other option is to modify the Lucee source code for serializeJson() to have it format dates in a manner more to your liking.

All that said… any one of those formats will pass an isDate() check and would work as dates for further processing by ColdFusion thanks to data coercion. You didn’t identify the end point of the data, but assuming it is further internal processing then that format doesn’t pose a problem.

And, lastly, you mention passing queries through serializeJSON() which produces absolutely horrid results that are difficult to work with. Making another assumption here that you’re using those results… in which case best practice these days is to pass an array of structs built from the query results. Lots of ways to do that… in Lucee you can set a returnFormat to queryExecute() for example that returns an array of structs for you. There are also widely available query to array of struct functions floating around the interwebs, such as at cflib.org. Using any of those, you could format dates as you wish when looping over the query data before doing serializeJSON(). Generally, if I’m exposing the JSON to be consumed by the outside world, I format dates as a UTC ISO time string (e.g. 20210130T162210Z) so they are easily consumable by other tech and other timezones.

Again, all of this spells rewrite of some sort, somewhere. Options are limited and building and maintaining an independent version of Lucee for your own flavor of date formatting in SerializeJson() is another challenge in and of itself (or, build it once and forever be stuck with that version of Lucee).

Hard to speak to any specific pattern I might suggest given how little I know about the code you’re working with, but based on your question I’d say some rewrite would both solve the issue and probably be beneficiary to the project in the long run.

HTH

– Denny

1 Like
3

bug filed, should be an easy fix if someone is keen

https://luceeserver.atlassian.net/browse/LDEV-3251

3 Likes
4

Hi @ddspringle

Thank you for the detailed answer.

As I want to keep Lucee up-to-date as much as possible, I’m not gonna modify it’s source code.

My old app is using serializeJSON to send the data over http to a homemade webservice that generate and return an Excel file. But instead of that, I’ll start using cfspreadsheet on Lucee. The date format is better in the Excel file (others columns types too), it will simplified the code and anyway I want to get rid of the webservice as soon as I switch the app on Lucee.

I still need to make sure the date and time format returned by serializeJSON will not affect any others parts of my app.

I like your suggestion @Zackster. PATTERN_ISO8601 = "yyyy-MM-dd'T'HH:mm:ssZ" is better than PATTERN_CF = "MMMM, dd yyyy HH:mm:ss Z". Plus the last one is using the ENGLISH locale. This is probably intended to be as international as possible, but the advantage of ISO8601 is that it is a general standard format without the presence of words in English.

5

I often do is do my own serializer in an Component by overriding the _toJSON function. For example:

Person.cfc:

component accessors=true {

	property name="Name";
	property name="dtLastAccessed";

	function init(String name, Date dtLastAccessed){
		setName(name);
		setdtLastAccessed(dtLastAccessed);
	}


	String function _toJSON(){
		
		var stringVersion = {
			'name':getName(),
			'dtLastAccessed': dateTimeFormat(getDtLastAccessed(),"ISO8601")
		}
		return serializeJSON(stringVersion);
	}
}

Now if I want to call it I can simply treat it like any other serializeable object:

<cfscript>

	Person = new Person("Mark", Now());

	dump(serializeJSON(Person));

</cfscript>

Which would output {"name":"Mark","dtLastAccessed":"2021-02-01T15:26:54+0000"}

As you see, lucee calls the _toJSON method in your object as the serializer. I believe you can also do a _toString() to output your component like:

String function _toString(){
		var name = getName();
		return name;
	}

And now you can just use <cfoutput> or echo() to output your component:

echo(Person);

Which would say “Mark” instead of giving you an error

More about implicit conversions here:

Which means you can do things like

if(Person)

or
if(Person GT 10)

(or whatever you fancy!)

6 Likes
6

Thank you @markdrew! That’s good to know!

1 Like