Convert timestamps stored in a Mongo collection

Question

I have various timestamps stored in Mongo collections, some as floats and some as ints.

They are all stored in BST and the server will be switched to UTC soon. How do I convert them within Mongo to be UTC timestamps?

In MySQL I can do this:

UPDATE `table` SET `field` = CONVERT_TZ(`field`, 'Europe/London', 'UTC');

Is there a Mongo equivalent?

Answer 1

You'll have to use your language of choice and update them one at a time. It should be as simple as a for loop that loads the data and rewrites it.

Just double-check how your language of choice handles timestamps across timezones. Making such a data change can have all kinds of unexpected effects on production code.

Answer 2

Timestamps are generally in UTC and not in a specific timezone. All date/time libraries I've worked with return timestamps that are the number of seconds (or milliseconds) since Jan 1st 1970 UTC. Check the documentation for the library you used to create the timestamp to be sure.

This means that you should be ok, unless you have used a date/time library that does not follow this convention, or somehow calculated the timestamps yourself and accounted for the timezone.

For example in JavaScript if you store the value returned from new Date().getTime() and later pass that value to new Date(...) on a different system you will end up with the same absolute date/time, regardless of the timezones of the two systems. The same goes for Ruby, do Time.new.to_i on one machine, and run Time.at(...) on another and you will get the same absolute date/time. When I say "absolute date/time" I mean that it's UTC time will be the same, most likely the system will display it in the local time zone, but that is what you want.

Answer 3

Some points to consider about date in Mongo:

• All dates are stored in UTC in MongoDB
• MongoDB stores dates internally as a 64 bit integer representing milliseconds since 1970-01-01T00:00:00Z
• If the date value you provide is not already in UTC it will be converted to UTC before being stored in MongoDB by the driver

The recommendation is not to use DateTime.Parse. You will get into all sorts timezone issues regarding how DateTimes are formatted.

Instead just use one of the DateTime constructors with UTC flavor.

Example:

var dateTime = new DateTime(2011, 5, 5, 0, 0, 0, DateTimeKind.Utc);

Hope you find it useful.