A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
What locale do folks recommend for storing dates in a database. I'm assuming it is best to store all dates as UTC and then convert back to Local Time for display. Is that right? Any traps to be wary of?
PS. I'm storing all dates in ISO8601.
Wednesday, March 29, 2006
The only real trap is that when people say "today" they mean today in their terms, not the databases, so you can't use any of the built-in shortcut operators some databases have for date checking; because some of "today's" real world operations will not have "today's" date in the database, because the date change doesn't happen at local midnight.
As long as you remember this and do checks as "timestamp > local2utc(midnight_this_morning)" format rather than "timestamp on DATE" format , it should all be fine.
There is another small gotcha about employee working hours. If you compute the hours by "utc2local(endtime) - utc2local(starttime)" you may accidentally pay someone an extra hour (or underpay them) if they were working while the local daylight savings kicked in, so you have to remember to always do those sums in UTC space. On days when the savings kick in, it's worth printing both times for the payslips, so there's less arguments.
The final yuck is that some conversion systems don't cope with savings properly, and what the answer to a utc2local() call results in depends on if you're IN the savings time or not even if the input to the call is outside the savings time. These things are right fricken pain; you have to set the program's locale to be GMT and then pass in the right context locales to the conversion systems. If your conversion system does this, bin it and buy a proper one...
The advantage of having a nice orderly monotonically incrementing time in the database is worth the extra bit of rendering work TBH. It's not all that much extra -- you'd probably be formatting the dates anyway, so adding a conversion wrapper is easy. Savings you'd have to handle anyway so the only real thing to be wary of is the difference between the dates in the database.
Wednesday, March 29, 2006
Katie, thanks for the detailed reply. I don't need to do much in the way of date calculations and such, so I'm not concerned here. Also the database I'm using doesn't know anything about dates. I already convert back from ISO8601 to a display format based on the users Control Panel settings (Windows), so doing a conversion to local time along the way isn't a problem.
For any date calculations I'll convert from UTC to local time first, which should take care of any savings issues.
You're final "yuck" may be a problem which I'll need to ponder on.
Wednesday, March 29, 2006
Absolutely store them as UTC for all the reasons that Katie says. As soon as you have a customer with locations in Indiana, you'll be grateful you did.
BTW, ISO8601 is a format, not a time or timezone. Format should be irrelevant if you're using a date/time column type in the database and a DateTime datatype in your language. Don't use a VARCHAR field to store date info if you can help it.
Your presentation layer should format the DateTime variable into what the OS says is their preferred format at the same time it converts it to local time.
We almost always store dates as local time. People using the system expect the time to be based on their own perspective of time and not some universal perspective. For example, if a user clocks out at 7:00 on Tuesday, then they clocked out at 7:00 on Tuesday. NOT at 1:00 GMT on Wednesday. The biggest problem comes when someone at another office checks their time card. If we stored the data as UTC then the person at the home office would see that they clocked out at 10:00 Tuesday due to the difference in time zones. This is undesirable because it is not the absolute istance in UTC that is important in this scenario.
Another example is when the home office sends down a date/time when a special sale is supposed to start. The intention is for the sale to start at say 9:00 AM on Tuesday. But each location may be in a different time zone. So sending it down in UTC would cause all of the sales to start at the same universal time but not the correct local time. Locations in California would end up starting the sale 3 hours too early.
When making the decision to use UTC vs. Local times you really need to consider the perspective of the user. Many times, local times will yield more consistent results by making the time zone portion of the date be implied.
Friday, March 31, 2006
If the local time perspective is important, store your dates in UTC along with the current local time zone offset in another field (minutes in a smallint column, for example). That way you'll always know the date in absolute terms while also knowing the local time with confidence, regardless of what changes to the time zone or daylight saving time may occur in the future.
Look at it this way: if you just store UTC or local time, you're effectively throwing away local time zone info that you may have to reconstruct later with some tricky function that tracks locations, laws, and conventions over the course of many years. ("The UTC date was 2005-05-20 03:25:46. So, let's see, that was in such-and-such county in Indiana, after the start of Daylight Saving Time in the US but before that county in Indiana started observing DST in 2006. Thus, the offset must be... [use table lookup and/or complicated algorithm that has to be updated regularly]")
I love UTC
Saturday, April 01, 2006
I beleive that the difference in the advice being given here is probably due to the difference in application architectures.
If your app is a web app running from a centralized server then it probably makes more sense to store dates as UTC. If you are writing a thick client application that is distributed all over the country then local times may be better. In the latter case, the user may not care about any sort of universal time. They have only a single perspective of time. Reporting is simpler using local times because you simply never have to deal with any sort of conversion.
But no matter how you do it there will always be "gotchas". And knowing how and when those gotchas are going to occur is the toughest part of designing a system that deals with dates successfully.
Sunday, April 02, 2006
Store time in UTC. The best way to store time (IMO) is always as seconds (or milliseconds or microseconds) since January 1, 1601.
WTF? you say? 1601 you say?
Well you could store time as _signed_ seconds since Jan 1, 2001 - that would also be good.
The math turn out to be extremely simple becuase leap years have a 400 year cycle (thus the 16 hundredness to them).
Calculate days since Jan 1, 1601:
unsigned long daysSinceYear1601(unsigned long year)
unsigned long days = 0;
unsigned long years = year - 1601;
days += daysPer400Years * (years / 400);
years -= (years / 400) * 400;
days += daysPer100Years * (years / 100);
years -= (years / 100) * 100;
days += daysPer4Years * (years / 4);
years -= (years / 4) * 4;
days += daysPerYear * years;
years -= years;
No 'if' statements required. I pity the fool who does math with calendar dates. Always convert to an epoch and then do simple subtraction on your base time increment (seconds, millseconds, etc.). Awlays store in UTC (machine time) and convert to local time when presenting to the user.
Don't create a mess you're just going to have to clean up later.
Huh? How has this helped? Databases and programming languages already understand the concept of timestamps. How has rolling your own made things any easier? You still have to deal with the decision of storing the number of seconds since January 1, 1601 in either UTC or local time so that hasn't changed. All you've done is required an extra conversion layer that needs to be called every time a date is displayed or entered. This sounds a heck of a lot more complicated than just storing a simple date. If nothing else you should be using a built in calendar class like the one's provided by Java, .NET, and other languages. Let these do the conversions for you.
You must not have to interface to anyone else's system because they would literally smack you for doing such a thing. It must be really nice to program in a bubble.
This sounds like another candidate for the daily WTF web site!
Wednesday, April 05, 2006
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz