How to Offset a Server's Timezone to Store a UTC Datetime

TLDR; the Javascript Date object is nasty. This is how I stored a UTC time in a Postgres database using the Javascript Date object.

Problem

As part of building a microservice responsible for handling bookings between 2 users, each of whom could be in any timezone, and also travel into any timezone between making the booking and fulfilling the booking, I stumbled across a little issue when it came to storing the UTC time.

For this microservice, I'm using Typescript, Koa, Prisma and Postgres. It's out of the scope of this article to talk through the entire infrastructure or, indeed, how the whole microservice works, but here's what the relevant part of my Prisma schema looks like:

model Booking {
  bookingId             String        @id @default(uuid())
  user1Id               String
  user2Id               String
  lengthMins            Int
  startDateTimeUTC      DateTime
  dateTimeCreated       DateTime      @default(now())
  dateTimeUpdated       DateTime?
}

Here's how we could create a new row in our Booking table:

await db.booking.create({
  user1Id: `user_1`,
  user2Id: `user_2`,
  lengthMins: 60,
  startDateTimeUTC: new Date(`2022-04-10T14:00:00`),
});

All we're trying to do here is, using Prisma, store a new row in our Booking table that represents a booking between user_1 and user_2 at 14:00 on 10th April 2022, UTC. When running a function like this (albeit this would exist inside a try/catch block in a re-usable function), I naively expected the time to be stored as... you know.... the time I passed into the Date constructor.

Unfortunately, this didn't happen. What happens is the time gets converted into the server's local time. I was running this code locally in BST (UTC + 1), so, in this example, the time would have been stored as 15:00.

Solution

My solution for the entire system was to store all datetime information in the database in UTC, then use the user's device location (with permission, of course) to convert the stored time into the user's local time.

My solution for this specific issue (without the use of any 3rd party libraries, and without having to store extra information like the server's timezone) was to create a function that used Javascript's Date object to offset the server's timezone before we store it.

This looked like the following:

function getUTCTime(dateTimeString: string): Date {
  const dateTime = new Date(dateTimeString);
  const dateTimeNumber = dateTime.getTime();
  const dateTimeOffset = dateTime.getTimezoneOffset() * 60000;
  const dateTimeUTC = new Date();
  dateTimeUTC.setTime(dateTimeNumber - dateTimeOffset);

  return dateTimeUTC;
}

What we're doing here is:

• Creating a new Date object (as we did in the first example) using a UTC datetime string that we can pass into the function.
• Getting the datetime in number format (or, the number of milliseconds from midnight on January 1, 1970 to the datetime we pass in).
• Using the .getTimezoneOffset() method to get the difference, in minutes, between the date as evaluated in the UTC time zone, and the same date as evaluated in the local time zone. We multiply this value by 60,000 to convert this number into milliseconds to make it more useful to use later.
• Creating a new Date object (with new Date()) which will be instantiated with the current datetime.
• Assigning the value of our new Date object to the datetime we passed into the function, minus our server's timezone offset (datetimeNumber - dateTimeOffset).
• Returning the Date object that we've now successfully set to the dateTimeString we originally passed the function!

Now when we add a row into our table, we can do something like this:

await db.booking.create({
  user1Id: `user_1`,
  user2Id: `user_2`,
  lengthMins: 60,
  startDateTimeUTC: getUTCTime(`2022-04-10T14:00:00`),
});

Peace Out

There are libraries that cater for this kind of thing very well. The main one I use being date-fns, but this is a fairly easy manual solution which solved my problem. This will work anywhere around the world too, as it just offsets the timezone of the server you're running the code on.

As always, I hope this helped you in some way, or you at least found it an interesting read.

I would love to hear any and all timezone puzzles you may have been a part of solving. I've found that timezones are one of those annoying rabbit holes that are really easy to make mistakes with. Fingers crossed, this isn't one of them!