(Dis)advantages of datetime vs. long in globally used applications [closed]

Question

I am currently designing a web/mobile application the bulk of whose users will be distributed across the different U.S. time zones with a potential of scaling up to other countries and time zones as well

I have worked with several other globally used apps and noticed they all used datetime to record timestamps with the default time zone being that in which the application is typically hosted. In displaying the times, they would typically do not care about the user preference (locale) and simply display the timestamp with the TZ and leave it up to the user to convert if (s)he wishes.

I am entertaining the idea of shifting away from that paradigm and recording timestamps as one global time (GMT) in long milis regardless of the user location and collecting the user locale by their IP or letting them change their TZ as a preference in their profile, which would later be used for display formatting of all their GMT long milis. This seems like a simpler solution than confusing the app design with TZs.

I am curious to hear feedback of pros and cons to this approach. I am also curious to hear some explanations why the, IMO overcomplicated time zoned datetime data type has ever been used across databases and not a uniform single time zone with conversions just for display.

Answer 1

1. I don't think you're going to gain much by storing all of your datetimes as longs. Every language I can think of has ways to convert dates to other formats (string, long) use the preferred format for your database.

2. Store the dates in UTC. I think this makes sense for nearly every application, since many languages and APIs have a way to get Now() in UTC. Also, for most devices and browsers it's trivial to figure out the timezone offset. Then it's simple addition for displaying a user's local time. You will save yourself much headache down the road.

Answer 2

This depends greatly on the purpose of your datetime information.

For a calendaring app, the ability to track time zones is essential. For a logging daemon, having all records in the same TZ is quite convenient. What kind is your app?

OTOH I don't see what will be your gain from using a long int to store the timestamp. This looks like a premature optimization to me; you can store datetimes as GMT as well, and have your servers' clocks always set to GMT (as many of us do). If this is the point of your question, then yes, having everything internally stored in the same TZ may be a good idea.

Answer 3

Is that GMT going to always be the same without any adjustments for Daylight Savings Time scenarios? I could imagine if suddenly a bunch of date data has been shifted because of DST that could be an issue and so you may have to contemplate what business rules would you have for this.

Jon Skeet's "Epic Fail" has a section about Time Zones that I find interesting and quite funny at the same time.

Answer 4

There are a lot of intricacies in converting between time zones, especially when you consider daylight saving time and the fistful that aren't even increments of one hour. If you're using a database that has robust TIMESTAMP support, switching to integer storage would lose you access to the wealth of available operations. That may seem like the expedient thing to do, but having to re-implement all of the conversions in your own code is redundant and error-prone. Why reinvent an already well-proven wheel?

SQL92 defines an AT TIME ZONE clause that lets you recast a time in one time zone to another (PostgreSQL example):

-- Grab a value in one time zone and convert it to another
SELECT TIMESTAMP WITH TIME ZONE '2013-01-03 12:34:56 EST' AT TIME ZONE 'MST';

-- Store it as the user provided it, in his preferred time zone
INSERT into bar (foo) VALUES ('2013-01-03 12:34:56 EST');

-- Retrieve it in its original form
SELECT foo FROM bar WHERE ... ;

-- Retrieve it in the time zone some other user prefers
SELECT foo AT TIMEZONE 'PST' FROM bar WHERE ... ;

Several databases, Oracle and PostgreSQL among them, implement this.

I am also curious to hear some explanations why the, IMO overcomplicated time zoned datetime data type has ever been used across databases and not a uniform single time zone with conversions just for display.

There's nothing complicated about it; the zoned types just store more information. Sometimes it's important to know the time zone in which an event occurred, and converting to a single time zone irrevocably obliterates that information. If you're doing a marketing promotion and want to know which customers placed orders around lunchtime, an order that appears to have been placed at noon GMT isn't very helpful when it was actually placed over breakfast in New York. To get all of that sorted out, you're going to have to reinvent the aforementioned wheels, which your database may have already done for you.