tag:blogger.com,1999:blog-9144505959002328789.post5136910235150290143..comments2024-03-28T11:39:50.622+01:00Comments on Karlsson on databases and stuff: It's about timeKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-9144505959002328789.post-5927273190568026902010-10-26T21:23:15.503+02:002010-10-26T21:23:15.503+02:00Recently I've been dealing with data hidden a ...Recently I've been dealing with data hidden a layer deeper: the business is only closed during a subset of US holidays. Even if I could get information like "Is $day a holiday?" it's not enough to answer whether we will be closed that day.<br /><br />Another interesting one is that MySQL doesn't store timezone. I store everything as UTC in my own projects, but most of my employers have had systems storing local time into DATETIME columns, and then I can't tell when '2010-11-07 01:45:00' is EST or EDT.<br /><br />(DATETIME can get even more exciting when you're mixing use of NOW() in mysql and date() in php, and they're working off of different tzdata or system timezone. But I don't expect anyone can solve that one in the DB alone.)sapphirepawhttps://www.blogger.com/profile/08959423651720108923noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-60687621907772505882010-10-26T09:51:04.880+02:002010-10-26T09:51:04.880+02:00Jeremy: Yes, absolutely, but that query would not ...Jeremy: Yes, absolutely, but that query would not be easy to read, nor easy or even possible to index using a b-tree at least. Like searching for overlaping intervals and stuff like that. There is a PostgreSQL project to adress thi, have a look and you see what I mean.<br /><br />Roland: Regarding atomicity, I agree. Regarding the solution to the problem you outline, well, true, that would solve the problem, sort of. But would not be terribly relational (data duplication). And what about different holidays in different cultures or regions?Karlssonhttps://www.blogger.com/profile/04874338187076980133noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-35408369014287751022010-10-26T03:43:34.299+02:002010-10-26T03:43:34.299+02:00The problem is basically that we pretend that date...The problem is basically that we pretend that date and datetimes are atomic, but they really aren't :) <br /><br />(Perhaps atomic is not exactly the right word, complex is perhaps better)<br /><br />But regarding the "soft" metadata, I think that problem is adequately solved by keeping a date table like you do in data warehousing (a date dimension): simple have a table with one row for each day, and use columns to store metadata like name of holdiday, whether or not it is a weekend, whether this year is a leap year, the quarter according to the calendar, the fiscal quarter etc. <br /><br />Depending on your requirements you can store calendar data in that table too, or create separate date tables for each distinct calendar (taking care that the key of the date tables is the same everywhere). Same goes for localization and internationalization.<br /><br />Of course, you still need to fill this table periodically, but after you generated and stored that data, it is amazing how much easier queries become once you have all metadata as simple fields, ready to be used (as opposed to crafting complex date formatting and extraction functions)<br /><br />There are a few maintenance problems to take care of, such as how often do I add more data for future dates, but in reality you'll find that you only need to have a supply of dates to add temporal context to events in the past or events in the relatively near future.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-46495186935466967992010-10-26T00:41:50.795+02:002010-10-26T00:41:50.795+02:00Anders,
I see this more as a relatively easy to s...Anders,<br /><br />I see this more as a relatively easy to solve secondary problem. For instance, learning and being able to query on the "hidden" metadata could be exposed with a two-prong approach:<br /><br />1. Some function to take an arbitrary date/time and return "interesting" metadata about it, perhaps as K/V pairs. E.g., holiday=Christmas-Eve, quarter=4, days-to-end-of-quarter=7, days-from-start-of-quarter=84, etc.<br /><br />2. The ability to index on functions in some useful way.<br /><br />Combining those above things you could fairly easily construct queries about any "interesting" metadata your function happens to support.<br /><br />Regards,<br /><br />JeremyJeremy Colehttps://www.blogger.com/profile/16788004780218819380noreply@blogger.com