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.
Top comments (0)