I want to correct this common misconception that UTC is enough. Calendar time with all human traditions involved is more complex than a simple timestamp.
The advice above is incorrect for calendar and scheduling apps, or anything that has a concept of a repeating event.
An example: we have a weekly meeting occurring 9AM Monday in San Francisco. You are in London and want to attent the meeting over Skype. When is it in London time?
On 7 Mar 2011 it's at 5pm
On 14 Mar 2011 it's at 6pm
On 29 Mar 2011 it's at 5pm
To make these calculations, you need to know timezone & daylight saving time (DST) rules of both your current location and the home location of the event.
A "DST zone" of a home location of a repeating event has to be saved together with a time and thus it's not just presentation-layer issue.
When you're inventing a rule system based on local times, as you are above, of course you need to track the rules in the local time zone. That's because the rule is: "do this thing at 9am in my particular local time zone", not "do this thing every 86400 seconds". Keep in mind that this is hard, though, due to the fact that in many local time zones, one hour is missing on one day ("spring forward"), and one hour occurs twice on one day ("fall back"). If you have some event that should be triggered at 1:30am, which 1:30am do you mean? The first 1:30am or the second 1:30am? What about on the "spring forward" day, when 1:30am doesn't occur at all?
Storing a recurring class of dates is - of course - more complicated; but then has anyone ever suggested otherwise?
Note also that not all governments are in the habit of giving sufficiently advance notice of daylight saving changes.
You also had to take into account what the DST would be for both locales at the scheduled time. Hint: Use Oracle, SQLServer doesn't have a way to stay updated with the constantly changing DST offsets worldwide..
If you save the UTC date of the event, the localized date for some timezone can be extrapolated from it.
I just wanted to highlight that "time" is a human concept that in informal setting means a lot of things, but when you start to model it formally, it can be more complex than a single timestamp.
vCal has spec for DST rules, but you don't want to store them for every event. You store a location or "DST zone" of your event and have DST rules in separate database (they need to be updated as DST rules can change)
When I worked on calendar applications, there was not commonly agreed way to transfer DST zones between systems, but single DST rules could be transferred as part of vCal entry.
Microsoft apparently implemented their own integer code for every "DST zone" and used it to transfer events correctly between Microsoft systems (e.g. sending meeting invitations by email from Outlook to Outlook). Things might have changed since I worked on this area, I haven't checked the current status.
A couple months before I left, one of the engineers proposed switching everything over to a textual representation according to ISO_8601. I forget the nature of the argument, but it was inane(to me). This actually led an extensive back/forth email exchange between various members of engineering, me as one of the frontend engineers, and even the engineering manager who seemed to favor the idea.
I argued, "why change the entire stack which works just fine, etc etc". Fortunately, in this instance a heavy workload and group apathy about taking on unnecessary additional work allowed this entire concept to wither and disappear after a couple days.
Any calendar app should allow the entry of appointments with time zone.
It seems logical to me that whatever time I write in my calendar app is the time that I expect something to happen. I.e. it is the local time at wherever I happen to be. If I put in a meeting at 4pm on 8/7/2011, then I expect an alarm to sound whenever the local time is 4pm on 8/7/2011.
That's how my paper diary works (or used to work when I had one) - if I am planning for a future event where I will be in a different time zone, I simply write down the local time of the event.
Your other way also allows for the same event to happen twice, at different times, which is entirely unexpected.
But I'll use the paper diary example again - if I was in Mombassa and was due to have a phone conference at 10am Montreal time, I would write "6pm - Phone conference" in my diary (assuming that is indeed the correct local time).
And in your example, yes, it would be easier for me if I could tell my calendar that it is "10am, montreal time, please adjust that to mombassa time". So I guess my ideal solution would be a calendar app that works the way I described unless I specifically override it.
Anyway, I think this proves the point that time is hard.
Though if you lived close to a time zone border it could be a problem. There are towns straddling the Queensland-New South Wales border in Australia. There is a one hour time difference between the states for half of the year (NSW does daylight saving, QLD does not). I've always wondered how local businesses deal with that.
And I've even lost a birthday flying back in the other direction. Great scheduling, that.
Or use a fast plane going west (Concorde used to take ~3h for London to NYC, and NYC is on UTC-5, so passengers on Concorde would arrive "2h before they left").
What if you actually wanted to call someone before their dinner and that's why you put the event in? If that person was in France in this scenario, you'd want to call them before 7pm UK time instead. Calendars can't guess the context - location of the event depends completely on what you put in.
(which has problems with uniqueness, but does seem like an intuitive "dwim" high level interface).
For my most recent app, timestamps are UTC and everything else is stored local time.
Storing it this way is preferable because it makes working with times across DST boundaries a non-existent problem. DST is a pain in the ass and you want to use whatever language / database facilities to exist to make this as smooth as possible -- storing local time (and setting the timezone appropriately) makes it not your problem. Also users frequently set their own timezone incorrectly, and when they fix their timezone, they don't want all their appointments to be at the wrong time.
I've done previous projects where I've stored UTC dates or unix timestamps for this situation and it was a hassle to deal with. You really need to consider the nature of the data -- for straight forward timestamps (like the date of a post) UTC makes the most sense. For user-entered dates, I think local time is much more appropriate and a lot simpler.
Then again, you'd have to somehow verify what's the time in that location at that point.
As for granularity, you can derive day/minute/hour etc based on the timestamp. For us, we were able to do those calculations in the application layer. For other types of projects, you can store that data in the db if you need to do more efficient queries for example.
As an example: We have some certifications our product must pass and the certification body plays a 4 minute looping broadcast stream with the test condition in it. It turns out I handled the time jump hat occurred when the stream would loop around poorly and this caused about 1 week worth of headaches and delays in getting our certification. None of my code expects time to be ever increasing now.
...or ntpd on the test computer had failed and the machine was a minute fast :o) so the code to search for data now looks backwards in time as well as forwards.
incidentally, does anyone know of a really good API for time (including calendars etc)? python's (which is largely a thin layer over C) is a horrible mess, for example.
The only date & time API I've ever seen praised is Joda (JDK/Java library). Joda's author went on to redesign it from scratch (though with inspiration from his work on Joda) for JSR-310, Java's new Date and Time API.
Right now I'm working with C, pthreads and struct timespec's and it makes me wish for a good time handling library.
DATETIME: Eight bytes:
A four-byte integer packed as YYYY×10000 + MM×100 + DD
A four-byte integer packed as HH×10000 + MM×100 + SS
Storing UNIX time as an integer would be silly, considering:
TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)
How about actually doing the right thing and using time_t?
Hmm, i wouldn't call it totally arbitrary. UTC = TAI + LS, such that |UTC - UT1| < 1 second, where:
* LS are leap seconds,
* TAI is "physicist" time, based on the ticking of atomic clocks at mean sea level. The length of a second is constant.
* UT1 is "astronomer" time, the rotation angle of the Earth with respect to the quasar reference frame. The length of a second is not constant.
The Earth's rotation is slowing down, so UT1 is gradually drifting away from TAI. UTC is a pretty natural scheme to reconcile these two systems.
Sort of. This could be misleading because GMT and UTC are still two different things with different definitions. Wikipedia is a good source of info on this, but for starters:
UTC is closely related to Universal Time and Greenwich Mean Time (GMT) and within informal or casual contexts where sub-second precision is not required, it can be used interchangeably.
So not strictly, but practically.. ;-)
Also it doesn't take different calendars into account, still doesn't work with leap seconds, doesn't deal well with time spans (t1 - t2 specified in seconds can be a lot things in reality), ...
Use a proper date time library to deal with dates and store them in your database in a string format, including the time zone. It depends on your application which time zone (UTC or local), but in general UTC is best, and the local time zone could be a second column if you need the info (or it could be a property of the user, but e.g. many calendaring application then screw it up in the UI layer...)
I'd like to read a book on the UI issues associated with dates and times, anyone know of something like that?
I don't think this is strictly correct. This implies that someone could start an atomic stopwatch at midnight on Jan 1, 1970, and it would match Unix time. It won't.
Because Unix time is non-linear and will either slew or repeat seconds when UTC has leap seconds, the hypothetical stopwatch would be ahead of Unix time by 34 seconds.
... at least this is how I understand it. Every time I try and wrap by head around the differences between UTC/TAI/UT1, my head really starts to hurt.
Not to mention that you can't know the UTC-TAI offset more than a few months into the future. We can not predict which years will have leap seconds inserted.
Unix timestamps do not handle leap seconds well at all. Obvious things like t₂-t₁ fail to provide the number of seconds between t₂ and t₁.
// schedule another run for tomorrow
schedule_event(now() + 86400)
This is one of my favorite go-to test cases. I've found some really fantastically interesting, catastrophic network halting badness with this really simple test.
My first thought was that this was some kind of prank :-) but seems it was a hardware issue on the parent machine combined with ntpd trying to compensate.
There are a lot of issues that can happen because of this...
No need to run the test case, you'd run into it soon enough on a 2.6 kernel on VMWare. ;)
Also, it is very common for business applications to deal with 'dates in the calendar', for example:
a) John's birthday is 26 August 1966
b) The loan was borrowed on 16 January 2006 and repaid on 9 September 2009.
I suspect most programmers will disagree with me, but in my experience it is NOT good practice to use a timestamp class to represent such things. It's better to use a class specifically designed to represent a date in the (Gregorian) calendar. In fact, I created an open-source Java class for this purpose:
GetTickCount is the poster child for this class of bugs: http://en.wikipedia.org/wiki/GetTickCount
In fact some versions of Windows CE intentionally set this value to (0xffffffff - 10 minutes) before bootup so that bugs were more likely to come out in testing, rather than showing up 42 days after bootup.
Also, don't store time intervals as floating point, especially if you're working on a missile system: http://apps.ycombinator.com/item?id=1667060
What I mean by this is that instead of messing with timezones (by trying to guess the user's timezone or even worse, asking for it) in most cases it is sufficient to tell the user something has happened x hours ago, or y days ago.
If you're programming in PHP, I can recommend this book: http://www.amazon.com/architects-Guide-Date-Time-Programming...
The first day of the week can be sunday or monday depending on where you are.
The way to count the weeks isn't the same everywhere.
Default to using ISO 8601 whenever possible http://en.wikipedia.org/wiki/ISO_8601
Don't ask for more precision then you actually need (Don't ask date & time when you only need year & week)
Don't store dates and times with more precision then was actually entered (e.g. Don't ask for year & week, and then store the (calculated) first day of the week.
On my first large project I made the mistake of asking for the year & week while storing the calculated first day of the week, however using the wrong first day of the week and the wrong numbering of weeks…
Edit: We basically just need a logical clock though we may face problems reconciling this logical clock in a distributed space/time system.
"It's 10:00 here in London. I need to call someone in New York, what time is it there?" "10:00, same as it is everywhere in the world." "So... is now a good time to call them?" "I have no idea."
Every programmer should know about DST. Offsets are not always enough.
When it's winter north of the equator, some countries are on summer time (DST) south of the equator.
Wow that's terrible!
"YYYY-MM-DD HH:MM:SS" is the format of the DATETIME datatype. That's all.
Today, UT1 - UTC = 82ms.
This is BS. ISO-8601 (MySQL time) is way better and is not prone to the 2038 bug. Unix time has 'scalability' issues.
(It assumes symmetry because there isn't any way to measure the asymmetry without first having time sync, and obvious catch-22. If you need better than 10ms accuracy, you'll need a GPS, etc.)