How do I properly store UTC date in database from user date and timezone input?

Question

my javascript app must create events, which will be stored as UTC in the database, so that they can be shown afterwards in 3 different timezones.

the tricky part that i found difficult to figure out is that, when creating the event, the user must select a timezone along with the date.

The idea is: - user selects date + desired timezone from an additional dropdownlist with timezones. - i store as UTC in database - all users see the date in 3 different timezones.

You might ask why would anyone need that additional dropdownlist to select another timezone, when selecting the date with a datepicker ALREADY contains a timezone by default.

Example: Let be Jim, US citizen, who planned events all his life using EDT Washington time; he is visiting China, enters a chinese internet cafe, and wants to plan an event using this app. the datepicker will choose the local timezone which is China Standard Time. But Jim wants to plan by EDT and to be sure that the app handles everything properly.

Therefore he must specifically choose from an additional dropdownlist, the desired timezone.

So my question is. because i allow the user to select the desired timezone, do i first have to CONVERT the date the user entered, to THAT timezone, before converting it to UTC and only then afterwards store it? Or am I not interested in the timezone conversion at all, when saving the event in the database?

So which step is correct: - get local date + selected timezone - convert local date to user selected timezone - convert date to UTC - store to DB - when reading, convert in 3 timezones by using the selected timezone

or - get local date + selected timezone - convert date to UTC, disregard timezone - store to DB - when reading, convert in 3 timezones by using the selected timezone

---

later edit - i do this in meteor , so javascript server side. DB is mongodb and therefore for performance reasons the date must be saved as a JS date object (in utc ofcourse).

---

2nd edit

Below is the implementation i tried (it does not work, as in when I enter an event date of 07:00 AM KST, when outputing the end result read back from the database and converted back in that KST timezone shows anything but 07:00 AM)

it all starts here - this is a server side method which reads the date from a datepicker, the time from a timepicker, and the timezone from a dropdownlist:

var pStartDate = GetDateAndTimeFromPostData(eventAttributes.startDate, eventAttributes.startTime, eventAttributes.timezone);

here I attempt to build the selected date from the different controls (datepicker, timepicker, timezone ddl):

function GetDateAndTimeFromPostData(dt, tm, timezone)
    {
        var t = tm.split(":");
        var hour =  t[0];
        var min = t[1];

        var finalDate = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate(), hour, min);
        var utcConverted =  ConvertUserTimezoneToServerTimezone(finalDate, timezone);

        return utcConverted;
    }

here i attempt the timezone conversion:

function ConvertUserTimezoneToServerTimezone(dateToConvert, tz)
    {
        var userTimezonedDate;

        switch(tz)
        {
            case "EDT":
            {
                userTimezonedDate = moment.tz(dateToConvert, "America/New_York");
                break;
            }
            case "CEST":
            {
                userTimezonedDate = moment.tz(dateToConvert, "Europe/Berlin");
                break;
            }
            case "KST":
            {
                userTimezonedDate =  moment.tz(dateToConvert, "Asia/Seoul");
                break;
            }
            case "CST":
            {
                userTimezonedDate = moment.tz(dateToConvert, "Asia/Shanghai");
                break;
            }
        }

        var utcDateFromUserTimezonedDate = userTimezonedDate.utc().toDate();
        return utcDateFromUserTimezonedDate; 
    }

The fault is already in the code above, as in the utc date is not saved as KST, but as GMT (my local timezone).

As a side note, i dont quite understand how moment timezone converts; when I go on the moment timezone website and I write in chrome dev tools this:

var x = new Date();
var y = moment.tz(x, "Asia/Seoul");
y.utc().toDate()

i actually expect back a date object that shows KST right? but it shows GMT+2, my local tz.

Mon Sep 08 2014 23:44:05 GMT+0200 (Central Europe Daylight Time)

I also tried thinking this backwards, like how should the stored date object look like but thats also confusing - should it be saved as the selected timezone? e.g. Mon Sep 08 2014 23:44:05 GMT+0900 (KST) If not, how must my stored date object look like?

again, i use javascript server side with meteor and mongoDB as the database.

many thanks,

Answer 1

Neither of the choices you provided are appropriate.

• If you're allowing the user to schedule an event in US Eastern Time, then he will be entering the time in that time zone. The fact that he's currently in China is irrelevant. So converting from his local China Standard Time to UTC is not something you'd want to do.

• Since you are storing future events, storing only a UTC value is not the best advice. You should store the original entered value, and the original selected time zone. You can store the UTC time as well, but you should be prepared to recalculate it at any time.

  This is important, because time zone rules might change between the time the event was entered and the actual time of the event. For example, perhaps instead of the USA, the user is in Russia. There are changes coming this year (2014), and will require an update to your time zone data. If the event was scheduled before you applied the data update, you will have used the old rules to compute the UTC value. If the event falls after this change (say, November 2014), then the event time will change erroneously as soon as you apply the update.

If you want to do this all in JavaScript, there are several libraries for working with time zones in JavaScript. However, I recommend this primarily when the back-end is in JavaScript, such as with Node.js applications.

This type of problem usually is easier with server-side code. You should probably investigate what options there are for working with time zones in the language you are using in your back-end code.

For further reading, I've written about this several times:

• in PHP (recently)
• in PHP (more detailed)
• in PHP (yet again)
• in Java
• in Java (again)
• in .NET

Regarding your edits - I can offer the following advice about the code you've presented:

• Never try to map a time zone abbreviation to a particular time zone. As you can see in this list on Wikipedia, there are just too many ambiguities. Even in your own list of 4 time zones there are some problems. Specifically, EDT is only used for part of the year in America/New_York - the other part of the year is EST. And while you have CST for Asia/Shanghai, it could also apply for America/Chicago, and several other places. (CST has 5 different meanings.)

• Instead of a dropdown list of time zone abbreviations, there are a few other options:

  • If you only need to handle a few time zones, you can provide a single drop down. Just use the time zone id in the value, and the full name of the time zone in text. For example:

    <select name="tz">
        <option value="America/New_York">Eastern Time (North America)</option>
        <option value="Europe/Berlin">Central European Time</option>
        <option value="Asia/Seoul">Korean Standard Time</option>
        <option value="Asia/Shanghai">China Standard Time</option>
    </select>
    

  • If you want to list all time zones of the world, you may find it too long to put in a single dropdown list. In that case, provide two drop down lists. The first would select a country, and then the second would select the time zone within the selected country. Since many countries only have a single time zone, some users will not have to make a selection from the second list at all.

  • If you want a more interactive approach, consider a map-based time zone picker control, such as this one, or this one.

• In your GetDateAndTimeFromPostData function, you construct a Date object. You need to keep in mind that the Date object is always UTC internally, but takes on the behavior of the local time zone for most input and output. By local, I mean local to the computer where the code is running. In a server-side function, that's going to be the time zone of your server - which is not appropriate in this case. There's no need to use a Date object, since you're already using moment.js.

• Make sure you understand that there is a difference between calling .tz(zone) on an existing moment object, vs calling moment.tz(value, zone). The prior adjusts a moment in time to a particular time zone, and the latter creates a new moment in time that's already represented in a particular time zone.

• Regarding your side note, you are defeating the purpose of moment and moment-timezone:

  • Since x represents the current date and time, then moment.tz(x, "Asia/Seoul") is just the same as moment().tz("Asia/Seoul")

  • y.utc() is converting the value back to UTC, so there was no point in calling .tz(...) to begin with

  • .toDate() puts everything back into a Date object, which represents UTC internally, but always shows its output using the local time zone.

  • So in the end, you just got the current date and time as a Date object, so the whole thing would reduce to new Date().

• With regards to MongoDB, its ISODate type is just storing UTC values anyway. So for the part of your code that needs to convert to UTC, consider something like:

  moment.tz([2014,0,1,10,0],'Asia/Seoul').toISOString()
  

  Or perhaps you can just pass the equivalent Date object directly - depending on how you're using the Mongo client:

  moment.tz([2014,0,1,10,0],'Asia/Seoul').toDate()
  

• Do consider though what I said in my initial response. The UTC value will help you know the instant in time that the even should run - but you should not lose track of the original input values! If eventAttributes.startDate is already a Date object, then it's probably already been converted to UTC and you've thus lost the original input value. You should make sure to pass the value from client to server using an ISO string that represents what the user provided. For instance, pass 2014-12-25T12:34:00. Don't try to pass an offset here, or convert to UTC, or pass an integer number. Unless you pass exactly what the user provided, you have no way to store that value.

  When you store the original input value in Mongo - store it as a string. If you store it as a Date, then it too will get adjusted to UTC by Mongo.