We have a form where a user provides a date and time. The form is for a New-York-based business, so users will enter time for the America/New_York
timezone. Our servers are not in the New York timezone, and our database stores datetime values in UTC
. So the goal is to take the user's date & time, treat it like America/New_York
, and convert it to UTC
.
We're currently running ColdFusion 2018, so I wanted to tap into the underlying Java. Most CF-related solution are pre-java.time
, so I pieced it together myself. Hoping someone with more Java knowledge can review this solution. This is in CF Script, but it should be pretty self-documenting. Does this appear to be both solving the problem and as efficient as possible?
LocalDateTime = createObject('java', 'java.time.LocalDateTime');
ZoneID = createObject('java', 'java.time.ZoneId');
ZoneOffset = createObject('java', 'java.time.ZoneOffset');
// Assume the date string is pieced together from the user's date and time form inputs
ldt = LocalDateTime.parse('2022-06-29T14:30:00');
nyZone = ZoneID.of('America/New_York');
odt = ldt.atOffset(nyZone.getRules().getOffset(ldt));
utcDateTime = odt.withOffsetSameInstant(ZoneOffset.UTC).toLocalDateTime();```
CodePudding user response:
You neglected to mention your database engine and the precise type of your column. So we cannot give a full answer. I can address part of it.
So the goal is to take the user's date & time, treat it like America/New_York, and convert it to UTC.
DateTimeFormatter fDate = DateTimeFormatter. … ; // Define a formatter to match the format used by your user.
LocalDate ld = LocalDate.parse( inputDate , fDate ) ;
Trap for DateTimeParseException
to detect faulty inputs.
Do similarly for LocalTime
.
DateTimeFormatter fTime = DateTimeFormatter. … ; // Define a formatter to match the format used by your user.
LocalTime lt = LocalDate.parse( inputTime , fTime ) ;
Combine with time zone to determine a moment.
ZoneId z = ZoneId.of( "America/New_York" ) ;
ZonedDateTime zdt =
ZonedDateTime.of( ld , lt , z ) ;
Adjust to UTC.
Instant instant = zdt.toInstant() ;
All of this has been covered many many times on Stack Overflow. Search to learn more.
CodePudding user response:
Here’s what I do:
Like you, I store timestamps in UTC. Then I output them into the DOM in ISO-8601 format wrapped in a element so I can then find them and and convert them in the browser using any number of JavaScript libraries. I use sugar.js, which I love.
The advantage to doing it this way is that because the timestamp is rendered in the browser, it will use the current time and time zone that is set on that computer. The result is that the timestamp will be rendered locally no matter what timezone they are in.
For example, on the server in ColdFusion, the following code converts your local server time to UTC. Even if your server is in UTC, I think you should do this just to always be sure.
utc = DateConvert(“Local2UTC”, now());
Then, output the date in ISO where it goes in the DOM:
writeOutput('<span >#DateFormat(utc, "YYYY-MM-DD")#T#TimeFormat(utc, "HH:mm:ss.l")#Z</span>');
The browser will get:
<span >2022-06-29 15:00:00.000Z</span>
Then write some JavaScript code to find those isodate elements and convert them. In sugar.js it looks something like (I'm using jquery just to make it easy to understand):
$('span.isodate').each(function(ix,elem) {
var iso = $(elem).text(),
d = Date.create(iso),
localDate = d.format('{Weekday}, {Month} {d}, {yyyy} at {h}:{mm} {tt} (' d.relative() ')');
$(elem).text(localDate);
});
Of course you can use whatever format you want, or whatever Javascript library you want to convert the date. momentjs.com is another one, but a little more complex. Sugar.js is super lightweight and easy to use and has great date support.
These code snippets above demonstrate the pieces of the puzzle and have not been tested. You could make it all work however you want... for example, I have a ColdFusion function that generates an ISO-8601 string for given a date. I've also got javascript functions that make it easy to find and convert these elements to local time. Good luck!