The other side of the moon

Several discussions I've had with friends and colleagues recently reminded me of an incident we faced several years ago at Yahoo!

Now Yahoo! as a company was made up of many different local offices around the world, each responsible for content in their locale. Since there was a lot of user generated content, this meant users in a particular locale could easily enter content (blog posts, restaurant reviews, etc.) in their local language script.

Everyone was happy!

From about 2005 onwards, the company was looking to unify some of the platforms used around the world. For example, we had something like 4 or 5 different platforms to do ratings and reviews and it didn't make sense to have different architectures, database layouts, BCP setups, and a separate team managing each of these, so we started unifying. Building a common architecture was the easy part. I worked on several of these projects. Getting front end teams to migrate was also not terribly hard. Migrating content though, was tough because each region had content in their own locale and MySQL didn't let you set multiple character encodings on text columns.

So the i18n team started working with teams across Y! to move everything to utf8. The easy part was changing HTTP headers and <meta> tags. Content was a little harder, but doable with iconv(1) since in most cases we knew the source character encoding and the destination was always utf-8. In some cases we had to guess, but it generally worked...

...until at one point we also decided to do it for authentication.

One of the things that was localized was authentication, because it allowed users in, for example, South Korea, to use Hangul characters in their passwords. Usernames were always restricted to just alphanumeric characters and underscores (If I remember correctly).

Passwords are stored, as they should be, salted and hashed, so the character encoding of the database column was always us-ascii, which is compatible with utf-8, so no biggie..., except the input character encoding used by the browser was based on the HTTP headers or META tags of the page, and the transfer encoding was based on the enctype of the login FORM.

Prior to this move, these were all set to a character encoding that made sense locally, so Korea used EUC-KR and China used Big5, and so the hashed passwords used the byte sequences that resulted from treating the input as one of these encodings.

After the move, the user would still type in the same password, but when we converted them to bytes, we used utf-8, which resulted in a different byte sequence than the original encoding, so hashing this new sequence of bytes resulted in a different hash, and users could no longer log in. Well, only users that used non-ASCII characters in their passwords.

I forget what the actual fix was, but there were several options on the table. One was to revert the character encoding changes on the login page and to re-encode all passwords after a successful login. Another was to generate two hashes, one using utf-8 and another using the pre-migration character encoding for the region and to allow a success on either to go through.

Wrote this as an answer to a question on StackOverflow, but I think it was one of my better ones, so am reproducing it here.

There are four different times you should consider:

1. Event time: eg, the time when an international sporting event happens, or a coronation/death/etc. This is dependent on the timezone of the event and not of the viewer.
2. Television time: eg, a particular TV show is broadcast at 9pm local time all around the world. Important when thinking about publishing the results (of say American Idol) on your website
3. Relative time: eg: This question has an open bounty closing in 21 hours. This is easy to display
4. Recurring time: eg: A TV show is on every Monday at 9pm, even when DST changes.

There is also Historic/alternate time. These are annoying because they may not map back to standard time. Eg: Julian dates, dates according to a Lunar calendar on Saturn, The Klingon calendar.

Storing start/end timestamps in UTC works well. For 1, you need an event timezone name + offset stored along with the event. For 2, you need a local time identifier stored with each region and a local timezone name + offset stored for every viewer (it's possible to derive this from the IP if you're in a crunch). For 3, store in UTC seconds and no need for timezones. 4 is a special case of 1 or 2 depending on whether it's a global or a local event, but you also need to store a created at timestamp so you can tell if a timezone definition changed before or after this event was created. This is necessary if you need to show historic data.

Storing times

• Always store time in UTC
• Convert to local time on display (local being defined by the user looking at the data)
• When storing a timezone, you need the name, timestamp and the offset. This is required because governments sometimes change the meanings of their timezones (eg: the US govt changed DST dates), and your application needs to handle things gracefully... eg: The exact timestamp when episodes of LOST showed both before and after DST rules changed.

Offsets and names

An example of the above would be:

The soccer world cup finals game happened in South Africa (UTC+2--SAST) on July 11, 2010 at 19:00 UTC.

With this information, we can historically determine the exact time when the 2010 WCS finals took place even if the South African timezone definition changes, and be able to display that to viewers in their local timezone at the time when they query the database.
System Time

You also need to keep your OS, database and application tzdata files in sync, both with each other, and with the rest of the world, and test extensively when you upgrade. It's not unheard of that a third party app that you depend on did not handle a TZ change correctly.

Make sure hardware clocks are set to UTC, and if you're running servers around the world, make sure they're OSes are configured to use UTC as well. This becomes apparent when you need to copy hourly rotated apache log files from servers in multiple timezones. Sorting them by filename only works if all files are named with the same timezone. It also means that you don't have to do date math in your head when you ssh from one box to another and need to compare timestamps.

Also, run ntpd on all boxes.

Clients

Never trust the timestamp you get from a client machine as valid. For example, the Date: HTTP headers, or a javascript Date.getTime() call. These are fine when used as opaque identifiers, or when doing date math during a single session on the same client, but don't try to cross-reference these values with something you have on the server. Your clients don't run NTP, and may not necessarily have a working battery for their BIOS clock.

Trivia

Finally, governments will sometimes do very weird things:

Standard time in the Netherlands was exactly 19 minutes and 32.13 seconds ahead of UTC by law from 1909-05-01 through 1937-06-30. This time zone cannot be represented exactly using the HH:MM format.