Odd timezone offset when converting

Question

I am trying to use UTC wherever possible. Now I found the following strange behaviour and I really don't understand what ist happening. It would be great, if someone could give advice.

Note: I write my code in the Google Apps Script Editor.

I use the following code to create a date and get the output in my local timezone:

var testDate = Date.UTC(2022,0,1,0,0,0,0);
Logger.log(Utilities.formatDate(new Date(testDate), 'Europe/Berlin', 'dd.MM.yyyy hh:mm'));

The result is 01.01.2022 01:00, as expected, because 'Europe/Berlin' is 1 hour behind UTC. So if I wanted the output to be 01.01.2022 00:00 I would try the following:

var testDate = Date.UTC(2021,11,31,23,0,0,0);
Logger.log(Utilities.formatDate(new Date(testDate), 'Europe/Berlin', 'dd.MM.yyyy hh:mm'));

But the result I get is: 01.01.2022 12:00 Can anybody hint me to why my expectation is wrong?

(I hope my English is okay.)

Answer 1

I don't think we can directly answer the question, because it looks like the issue is that Utilities.formatDate uses 12:00 for midnight (which is one of two valid ways of writing midnight in many systems, especially when using a 12-hour clock, though usually you follow it with something indicating it's 12:00 a.m., not 12:00 p.m.). For instance, here's an example of another formatter doing that because I'm explicitly telling it to use 12-hour time:

const testDate = Date.UTC(2022,0,1,0,0,0,0);
const dateTimeFormat = new Intl.DateTimeFormat("en", {
    hour12: true,
    timeZone: "UTC",
    timeStyle: "short",
}).format;
console.log(dateTimeFormat(testDate));

So you'd have to look at Utilities.formatDate to see if it has an option for 24-hour time or at least using 00:00 for midnight, like some other formatters do.

But more fundamentally, if you're trying to format in UTC, I wouldn't play offset games to do it, not least because Berlin isn't always one hour offset from UTC, sometimes it's two hours offset (daylight saving time).¹ Instead, I'd have a formatter that uses the UTC accessors on Date (getUTCHours, getUTCMonth, etc.) to build the string rather than using the local time version. Either some library, or something that uses Intl.DateTimeFormat. For example:

const testDate = Date.UTC(2022,0,1,0,0,0,0);
const dateTimeFormat = new Intl.DateTimeFormat("de", {
    timeZone: "UTC",
    dateStyle: "medium",
    timeStyle: "short",
}).format;
// Outputs "01.01.2022, 00:00", which is close to but
// not quite the same as your desired format
console.log(dateTimeFormat(testDate));

// Outputs your desired format, but may not be flexible across locales
const dateFormat = new Intl.DateTimeFormat("de", {
    timeZone: "UTC",
    dateStyle: "medium",
}).format;
const timeFormat = new Intl.DateTimeFormat("de", {
    timeZone: "UTC",
    timeStyle: "short",
}).format;
console.log(`${dateFormat(testDate)} ${timeFormat(testDate)}`);

---

¹ Remember that UTC is unchanging, it doesn't have daylight saving time.