- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Persisting date/time in any other timezone than UTC is an anti-pattern. Time zones are something that should be handled on the presentation layer of software (aka user facing) and has no place anywhere else.
Admin
Just this morning I saw a title card on NASA TV which listed upcoming times in a mission in EDT. The time change was yesterday. If there is any place you would expect people to understand time zones, it would be NASA.
I guess dates really are hard.
Admin
We had a persistent and difficult-to-locate timing bug once.
Turned out that we could not guarantee that all the servers were synchronised to precisely the same time, and so getting the time from the server was not reliable. What did we do? Made sure we always got the time from the database using a SQL call. Nailed it.
Admin
That is the major suck with dst though - it creates situations where you can’t just adjust for the local time zone at display time, you almost have to do some kinda of dst awareness on the backside once you have a recurring task that should happen at “the same hour” of a given day or any sub hourly sampled events you need to roll up. I’ll agree store time in utc wherever possible and do as much of your date math and accounting there as possible but unfortunately crossing dst events changes the math often enough you can’t ignore what time zone the data will be presented on the server side all the time
Admin
Yep, been there (10, 15 years ago) - clock drift, also inconsistent timezones on the web servers, using the DB as the single source of time solved a lot of issues.
Admin
Many moons ago, we had this problem where I worked. We installed NTP clients everywhere, and the problem went away.
Admin
And the last one is a bit weird, since it ends up saying that the difference between today and tomorrow is two days. (SQL's "between" operator is inclusive, so the SELECT grabs today and tomorrow and counts both of them.)
It might be right for their application, but it is still weird.
Admin
That's not the only thing that's weird about it. It filters out all days whose day_of_week is not BETWEEN 1 AND 7... which should be none, unless they expect to be dealing with Octeday.
Admin
A long time ago I had to maintain an application that saved its configuration using gmtime calls for the times and loaded it using mktime calls.
Hint - this works fine if you are in the UK in winter ...
Admin
There do exist applications where it is necessary to use local time,
An immediate one that comes to mind is predicting power demand. If everybody turns the TV on at 6 pm to watch the news, you need to know when 6 pm actually is in the local milieu so you know when to enable the transmission of that much extra power.
Admin
If it's the time my morning alarm call goes off on my phone, it had better be in local time where I happen to be sleeping that night.
Admin
I can't be the only one who thought that the developer was trying to figure out how much time was left in/until Eid al Fitr, right?
Admin
Changing clocks twice a year sucks, but you know what sucks even more? Changing how the clocks change by modifying or cancelling DST.
Especially in the more north/south countries where you actually seriously do need daylight to shift around the hours over the year.
Admin
Maybe it was EDT when they "staged" it?
Admin
How do you express "4 PM local time in location X daily" using UTC, when you're in location Y which has different rules for daily light saving times than location X has?
In a previous job we had software interfacing with trading floors in Chicago, New York City, London, Frankfurt, Tokyo and Melbourne, and what it did (or did not) depended on which trading floors were open. Those times are fixed relative to their local times, but fluctuate in respect to UTC over the year. In my current job, we deal with hotels and what we present to the customer very much depends on the date (and sometimes time) of where to hotel is located (and not where the customer or our offices are located). With millions of hotels in almost every timezone, tracking those date/times in UTC doesn't make any sense.
There are a lot of times where storing date/times in UTC is the better thing to do. But it's a grave mistake to assume this can always be done, or it even to always be the best solution.
Admin
I had a weird time bug once. It seemed like the application would get times in the future for "Now". It ended up being that the server was virtual, and the hypervisor had a clock that was about 5 minutes fast. The server would sync it's time to the hypervisor via the guest OS utilities, then Windows would sync it back to normal via Active Directory. But for a little bit, it would be in the future. Just one example of how hard it can be to get people to setup NTP properly.
Admin
The big date dimension table with every variation is actually not just really common in data warehousing, but considered a best practice. It's way less expensive to filter a (relatively) small table on a constant value than parse every timestamp and compute a value for every row in a giant fact table. When reporting, it's fast and easier to join in the, say, "day of week" column from the date table and then group and aggregate by day of week.
As far as how they used it and any bugs there, that's a WTF, but the date table itself isn't.
Admin
I believe the canonical approach is to store the DT as UTC, and convert UTC to local time as and when required.
But then again, I might be missing something.
Admin
You can't approach it that way because you are storing "a specific time in local time zone X regardless of the day" (as for an alarm clock, say), so you can't reliably store the required time as UTC because you can't correctly return it to "local time in time zone X"
Admin
The bit which cheeses me to no end is the column named [char_full_date]. Is that "YYYYMMDD" or some other format? You'll have to guess . . .
Admin
I'm not sure about all the frameworks out there, but .NET DateTime handling code handles this seamlessly: you can take any UTC value and convert it to any known (or custom) timezone and it handles regular offset and DST offset which are in effect at the time of the UTC value.
Admin
I think MaxiTB meant it's better to store date/time values - usually, specific points in time when things occurred - which is valid. You are describing a different situation - some kind of scheduling system - in which case, it's not a date/time value, but rather a schedule with a time of day, and those sometimes do require the local timezone stored. I guess, both of you are right, in describing different requirements.
Admin
Someone should probably eidt that article.
Admin
Another reason for getting the time from the database: What if local time is untrustworthy? (A time clock run as a desktop app.) I hadn't thought of the database, it's probably the answer to our hassles with NTP to the server not always working.
Admin
Oh man, the stories I could tell about timezones. I became a unintentional expert on that stuff while working on a global reservation system for the 'hospitality industry' that started out using times all relative to US/Central. FML. Later on I ended up in transportation industry. On a trip to a customer site in Egypt the times changed 3 times in the span of a week as an edict came down to "we're going to do DST", then a few days later "except for Ramadan", then a few days later "wait changed my mind again". The airline had to reissue the itinerary each time. I'm thinking to myself, "I feel sorry for that poor guy who maintains the tzdata project". https://xkcd.com/2347/
Admin
If you can't trust your server's time, then how can you trust the time from the database running on that server ?
Admin
Who said anything about the (app) server being on the same machine as the database? I suppose we can't always rule out that being a possibility. However, in this case if the app and db are running on the same server then we must question the existence of this code; since why bother with asking the db for the time that's going to be the same as the local time. While it is possible that they are preparing for a future where this may be needed (this site full of stories involving this level of absurdity), our dear friend Occam recommends that we assume the simpler explanation, which is that they are just trying to use the database to synchronize between multiple servers.
Admin
Or simply a TV set with a recording function. Which reminds me ... my cheap receiver doesn't handle DST changes and I have a weekly recording programmed. So this article at this time was actually useful to me, as a user. :)
Admin
And this is why any rule can be prefixed with "Usually, ...". Requirements will make fools out of us all.
Admin
Two points to consider here:
First: on a certain level, it's not necessarily about whether the time is correct so much as the fact that it has to be consistent.
If it is important that all your events are sequenced by the time they happened, all you're really interested in is that event A happened before event B
Second: if it is absolutely necesssary that the time be accurate, you have the same problem whether you're scoring off the server or the database, Wherever you need that accuracy, that's where you maintain that accuracy.
If you only have one machine whose time has to be consistent, then whatever you have to do to ensure that time is accurate, you only have to do it on one machine.
Confucius says: "Man with watch knows what the time is. Man with two watches is never sure."
Admin
Once upon a time, on the other side of the millennium in a *nix environment designed before BSD on the network management computer and there was no such things as NTP, i fixed a reported problem in the code that consolidated network event logs into one file for the day. The code called time (now) every time it wanted to "look" at the clock. If the cronjob kicked off near midnight and the process took long enough to run, "now" indicated it was tomorrow in the middle of the run. As this was a consolidation task and it was looking at the past day's logs written to disk- it didn't need to follow the clock -- looking once and remembering that in a static variable was all that was needed.
Admin
Good to see that last one ensures that the day of week is between 1 and 7. Don't want to have reports that include numbers from Smatterday or Blunday.
Admin
Time of events which are in the past can be stored in UTC. Times for future events need to be stored as a local time with timezone. That allows for the 3pm meeting to stay at 3pm when DST starts or stops or when a government changes the rules. It may be useful to record local time for past events as well, depending on the nature of the event - if it was something like a meeting it may be useful to know what its local time was, but if it's something like an astronomical observation, there is no need.