Storing and Using DateTimeOffsets in Lucee

Recently at Impelos I began storing dates using SQL Server's DateTimeOffset field, which allows us to store a date and time with embedded UTC offset. Unfortunately, I ran into several issues with Lucee's support for the data type.

This post details what I did to work around the issues and even details how to format the dates to an ISO 8601 JS-safe format.

Problem One: UTC Offset Truncation

The first issue I ran into with the DateTimeOffset field had to do with losing the UTC offset on insert/update. When using the datetime cfsqltype, the UTC offset is truncated.

So instead of 2019-07-07 13:22:01 -5:00, we get 2019-07-07 13:22:01 +0:00. Notice the UTC offset is changed to UTC +0, but the date is never converted to UTC +0, or GMT. This causes the inserted time to be off by the full value of the UTC offset. Insert a datetime at 7PM UTC +1, and it indicates 7PM GMT, which is actually 8PM at UTC +1.

The workaround I settled on was to use a varchar cfsqltype and manually format the value to fit MSSQL's datetimeoffset format: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm].

Here's a quick example using QueryExecute():

var params = [
    datesaved: {
        value: dateTimeFormat(now(), "YYYY-mm-dd HH:nn:ss XXX"),
        cfsqltype: "varchar"
    },
    title: "My crazy title"
];
queryExecute( "UPDATE blogposts SET title=:title, datesaved=:datesaved", params );

This worked pretty well for me. Note that XXX is a mask sequence currently not documented on CFDocs.org. I found this on the documentation page for the Java SimpleDateFormat class, which Lucee uses under the hood. I'm planning to document that on CFDocs soon!

Problem Two: DateTimeOffset Retrieved as Java Object

Here's the second issue. Querying for a datetimeoffset field or value actually returns a ... wait for it... Java object. Yep.

A simple query like this:

var query = queryExecute("
    SELECT getUTCDate()
    AT TIME ZONE 'Central Standard Time'
");

returns the wonderful little object shown below:

In case you're wondering, serializing this as JSON does not explode spectacularly as I assumed it would (at least in ColdBox. I admit I did not try this with the native serializeJSON().) This Datetime java object renders as the following struct:

"column_0": {
    "Timestamp": "June, 13 2019 07:55:00 -0400",
    "MinutesOffset": 0
}

I tried to force this to a somewhat useable date format using the toString method:

for ( var row in query ) {
    row["datesaved"] = row["datesaved"].toString()
}

but I soon received complaints that the frontend couldn't parse my date strings. In other words row["datesaved"].toString() did not output a JS-safe date.

Formatting Dates in ISO 8601 Format

Just what date format does JS require? According to Stack Overflow:

JavaScript officially supports a simplification of the ISO 8601 Extended Format. The format is as follows: YYYY-MM-DDTHH:mm:ss.sssZ. The letter T is the date/time separator and Z is the time zone offset specified as Z (for UTC) or either + or - followed by a time expression HH:mm. Some parts (e.g. the time) of that format can be omitted.

Answer to "What are valid Date Time Strings in JavaScript?"

The easy way to marshall a date into proper ISO 8601 format would be the following:

DateTimeFormat(now(), "YYYY-mm-dd'T'HH:nn:ss.SSSXXX");

Again, we're using the XXX mask to output a UTC offset like -5:00, and using single quotes to escape the T in the middle of the date string. This should put out a proper ISO 8601 date like so: 2019-07-05T17:16:29.029Z. I'm still a little unsure on the milliseconds, as they don't look right to me, but I don't consider millisecond precision as super important, at least right now.

With that, my date woes are over for now. It’s been helpful to find extra date formatting masks I didn’t know about, and slightly frustrating to find two year-old bugs in the Lucee DateTimeOffset support.

Note: Both issues are mentioned in the Lucee Jira ticket #1680.