Goodies… but not the kind you can eat

You’re probably wondering, “What does this dandelion picture have to do with dates?” I wondered the same thing when I typed “calendar” into the image search box. Forget the dandelion. Let’s talk date table fun. On to the “goodies” I promised.In case you missed it, I posted earlier about the wonder of a “dates” table (Dates Tables (More Numbers Table Sugar)) I said I would probably make posts about why it is so wonderful. I like to deliver on what I promise.

Since the previous posts use MySQL, this will also use MySQL syntax in places. Considering how prevalent it is I’m sure not many people will complain.

For those who want to complain, you may insert into this table I created:

CREATE TABLE complaints ( complaint_id int unsigned auto_increment, …

Why A Table of Dates?

I thought it would be nice to build on the numbers table with another very useful tool.

Dates are pretty important–especially if you are playing with data warehousing. Not only dates, but all the different properties and derived goodies contained within them. You really don’t need a dates table, unless you care about efficiency and getting to all the good stuff quickly. Perhaps I’ll showcase some of the “goodies” in later posts.

Sure, most databases have built-in functions to get everything you might want. But do you really want all the inefficiency that comes with calling functions and doing calculations over and over again? I hope not. I used the method in this article when I developed a SQL Server data warehouse for a company I worked for. It was adopted by our other SQL Server guru for all the date-sensitive stuff we did (which was a lot). It increased efficiency by leaps and …

Both data types: timespamp and datetime can be used to store a date and a time in a MySQL table. So what are the difference between these two and which one should I use in which cases? The usual use of timestamps in MySQL is in generall to track changes of record (fields like created, [...]

Today, we run into an issue when a client of ours. They wanted to see some of the data on our website and when doing a search, they didnt see all the days they asked for in the search

Why didn't they see all the data? Because they did a search by date and some of the dates were stored via the website in the form of '2008-01-01 00:00:00' and some were stored via the database in the form of '2008-01-01 12:35:49'.
Now, for some magical reason, if you hide the time in the date in your searches, like so:
where signupdate between '2008-01-01' and '2008-01-02'
or
where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')
then you might not see all the data between the days 01 to 02.
Depending on which way your date was stored, you might only see the dates between those 2 dates and not equal to those dates as well.

So for example, if you had:

…