Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

closed as not constructive by Oded, Glenn Nelson, gnat, Dynamic, World Engineer Jan 3 '13 at 21:53

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
I don't know the common databases' datetime types well, but the behavior you describe should be trivial to support with those types (=> you should use them, because otherwise you'd be re-implementing their functionality for your custom timestamp): Just convert to a canonical timezone before storing in the DB. What's wrong with that? –  delnan Jan 3 '13 at 17:42
1  
this SO post addresses most of your concerns. –  assylias Jan 3 '13 at 18:47

4 Answers 4

up vote 9 down vote accepted
  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.

share|improve this answer
3  
+1 I cannot stress how important 2 is. Just as an example, there are multi-billion dollar hospital systems which have to incur downtime during DST change-overs as a result of using local times. One has to wonder how many millions of dollars (and potentially lives) would have been saved if they'd only used UTC. –  Dan Lyons Jan 3 '13 at 18:56

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.

share|improve this answer

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.

share|improve this answer
    
By the way SQL will allow you to convert a 'TIMESTAMP WITH TIMEZONE' into a 'TIMESTAMP (in UTC)' easily so you can write simpler queries when needed. –  SandRock Jan 8 at 23:27

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.