Skip to main content
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Oguz Demirkapi
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Oguz Demirkapi ( @demirkapi )

DateGetTime() To Get UTC Milliseconds From Date In ColdFusion

By on
Tags:

As of recent updates to Adobe ColdFusion 2021 / 2023, MySQL queries are no longer reporting dates as timestamps. For native ColdFusion logic, this isn't much of a problem. However, in my ColdFusion applications, I'm often preparing data for consumption in JavaScript. And, in those cases, I serialize ColdFusion timestamps as UTC milliseconds using the .getTime() method on the underlying java.util.Date instance. But, now that the MySQL dates are coming back as strings, this .getTime() method isn't available. As such, I wanted to create a more ColdFusion-native way to access this Epoch notation.

To be clear, ColdFusion is still built on top of Java; and, when I'm working with a native ColdFusion timestamp, it's still built on top of the java.util.Date class. As such, in order to get the Epoch milliseconds from a date value, all we have to do is force ColdFusion to cast it to a native timestamp. And, to do that, all we have to do is pass it through a ColdFusion date/time function.

In the following code, I've created a User Defined Function (UDF), dateGetTime(), which takes an input, and either uses the native java.util.Date instance method; or, casts the value to a native timestamp and then uses the native java.util.Date instance method:

<cfscript>

	timestamp = now();
	// In the recent ColdFusion 2021/2023 updates, MySQL query dates are no longer coming
	// back from the database as timestamps - they are coming back as strings. To mock
	// this out, let's format our known valid timestamp as a string.
	mockDateFromDB = timestamp.dateTimeFormat( "yyyy-mm-dd HH:nn:ss" );

	// And, let's output the Epoch milliseconds for each.
	writeOutput("
		<p>
			From time-STAMP: #dateGetTime( timestamp )#
		</p>
		<p>
			From time-STRING: #dateGetTime( mockDateFromDB )#
		</p>
	");

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	/**
	* I return the number of milliseconds since January 1, 1970, 00:00:00 GMT represented
	* by this given date/time value.
	*/
	public numeric function dateGetTime( required any value ) {

		if ( isInstanceOf( value, "java.util.Date" ) ) {

			return( value.getTime() );

		}

		return( dateAdd( "d", 0, value ).getTime() );

	}

</cfscript>

As you can see, the magic sauce here is, dateAdd("d",0). By adding "nothing" to the given string representation of a date, ColdFusion with cast the result to a native java.util.Date instance (or rather, a subclass of it); and, at that point, we can access the .getTime() instance method. As such, when we run this Adobe ColdFusion code, we get the following output:

From time-STAMP: 1709982806634

From time-STRING: 1709982806000

The native timestamp - created via now() - has millisecond precision, which is why its Epoch milliseconds ends in 634. The dateAdd() casted timestamp is parsing a string that doesn't contain a millisecond mask. As such, the subsequent date only as second precision, which is why it ends in 000. But, in both cases, we are able to extract the Epoch milliseconds.

Should I Just Serialize Dates Using ISO Formatting

You may look at my desire to serialize timestamps as UTC milliseconds and wonder if I should just be using an ISO formatted string instead. Yes, I can totally do this:

<cfscript>

	timestamp = now();

</cfscript>
<cfoutput>
	<script type="text/javascript">

		console.log( "From UTC milliseconds:" );
		console.log( new Date( #timestamp.getTime()# ).toString() );

		console.log( "From ISO string:" );
		console.log( new Date( "#timestamp.dateTimeFormat( 'iso' )#" ).toString() );

	</script>
</cfoutput>

As you can see, the first JavaScript Date is created from a .getTime() result. And, the second JavaScript Date is created from a .dateTimeFormat('iso') result. And, when we run this ColdFusion and JavaScript code, we get the following console logging:

From UTC milliseconds:
Sat Mar 09 2024 06:20:41 GMT-0500 (Eastern Standard Time)

From ISO string:
Sat Mar 09 2024 06:20:41 GMT-0500 (Eastern Standard Time)

Both ColdFusion serialization techniques yield the same JavaScript date. So, perhaps the ISO string formatting is something I should be using anyway. After all, ColdFusion will automatically permit the .dateTimeFormat() member method to be called on a string:

<cfscript>

	// STRING version of date.
	mockTimestamp = "2024-03-09";

	// ColdFusion automagically exposes Date member methods.
	writeOutput( mockTimestamp.dateTimeFormat( "iso" ) );

</cfscript>

This results in:

2024-03-09T00:00:00-05:00

This magic resolution is sometimes nice. But, I honestly don't love it because it has to make decisions behind the scenes that I don't control. For example, what happens if I try to call .compare() on a string-representation of a date? Meaning something like this:

"2024-03-09".compare( "2024-03-09 00:00:00" )

In ColdFusion, the compare() function is a String function. But, there is also a date-based function called dateCompare(). Which means, when invoked on a timestamp as a member method, .compare() is a Date function. As such, it becomes unclear if these two calls will result in the same value or a different value:

<cfscript>

	writeDump( "2024-03-09".compare( "2024-03-09 00:00:00" ) );
	writeDump( createDate( 2024, 3, 9 ).compare( "2024-03-09 00:00:00" ) );

</cfscript>

Both invoke .compare() as a member method. But, when we run this ColdFusion code (in either Adobe ColdFusion or Lucee CFML), we get the following output:

-1
0

This is because the first line uses the string-based compare() and the second line uses the dateCompare().

Frankly, I don't love how much ColdFusion code relies on stuff "just working". I prefer to have code that is more explicit in its intent. Which often means explicitly casting from a string to a number; or a string to a date. But, I'll get off my soapbox since we're way off topic at this point.

Ultimately, perhaps this breaking change in ColdFusion is a good reason to start using ISO strings for my date serialization. If nothing else, it will make my serialized data easier to read (while debugging).

Want to use code from this post? Check out the license.

Reader Comments

15,688 Comments

@Chris,

It's funny how much the Anchoring effect plays a role in our lives. In the abstract, I'm 100% in agreement with you. Readability rules everything! This is why I love JSON as a transport format, as opposed to some inscrutable binary format (like protobufs).

But, with regard to anchoring, I think the only real reason I have preferred the UTC milliseconds approach is because it was the first one I found (vis a vis date serialization). I had always seen JavaScript examples of new Date() being called with a milliseconds input. So, I worked backwards from that and figured out how to get milliseconds out of dates on the server. And my problem was solved.

Since then, I've just had it in my mind that this was "the way"; and, any new approach was "other". But, imagine if I had seen new Date() examples with an ISO input. That would have become my anchor; and I'm sure that seeing the use of milliseconds would have seemed strange and unnecessarily opaque.

All to say, I have no logical reason to use milliseconds; though, it's like more performant than string generation and parsing. But, I think that could safely be tucked under the blanket of premature optimization.

So, yes, I think it's time for me to start using ISO as my serialization format for dates.

5 Comments

"In the recent ColdFusion 2021/2023 updates, MySQL query dates are no longer coming back from the database as timestamps"

Are they not coming back from the DB as timestamps, or are they not coming back from the driver as timestamps, or is CF mucking with dates after it gets the data from the driver?

This seems like a pretty egregious change. It can break a LOT of existing code.

Do you know what driver version CF uses in the new update? I've not seen anything in recent driver updates (at least not those managed by Oracle) that discusses this change in behavior.

15,688 Comments

@Scott,

I assume it's something that ColdFusion is doing something under the hood (in between what the Connector/J driver returns and what the Query object presents.

In my local Dockerfile for development (ACF 2021), I've been swapping-in the mysql-connector-java-8.0.22.jar connector for the past few years (or whenever that LocalDateTime issue started breaking for everyone in CF land). So, as far as I know, when I ran the last update or two, the only thing that changed was in the ColdFusion side (not in the connector or the version of MySQL itself).

But yes, I agree, this is sub-optimial 😨

5 Comments

Thanks.

I've done a few Java demos using a later version of the connector and don't recall any issues with dates but now I want to verify.

If it is on the MySQL team, I can reach out to engineering and find out why this was done.

15,688 Comments

@Scott, I don't want to shame anyone on the ColdFusion side, since we all know it's the best language ever invented 😉 but, this issue is almost certainly on the ColdFusion side 🙃

Post A Comment — I'd Love To Hear From You!

Post a Comment

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel