Don't Ever Use NOW() - `datetime field`

***All point news bulletin***

Do not ever use NOW() - `datetime field`! Broadcast this from the mountaintops of open source to the valleys of companies who dabble in it.

Instead, use the following:

TIME_TO_SEC(TIMEDIFF(Now(),`datetime field`))

There are other ways to do the above, just as long as you do not use NOW() - `datetime field`.

Wanna see proof?

mysql> SELECT RIGHT(NOW(),8) `Now`, RIGHT(datetime_inserted,8) `Inserted`, NOW()-datetime_inserted Incorrect, TIME_TO_SEC(TIMEDIFF(NOW(),datetime_inserted)) Correct from my_table;
+----------+----------+-----------+-------+
| -- Now - | Inserted | Incorrect | Correct
+----------+----------+-----------+-------+
| 11:16:59 | 10:59:45 | 5714.0000 | 1034
| 11:16:59 | 10:59:51 | 5708.0000 | 1028
| 11:16:59 | 10:59:55 | 5704.0000 | 1024
| 11:16:59 | 11:03:17 | 1342.0000 | 822
| 11:16:59 | 11:03:19 | 1340.0000 | 820
| 11:16:59 | 11:03:20 | 1339.0000 | 819
+----------+----------+-----------+-------+

Fun, eh?

Til next time,
David

Data Warehousing 101: The purpose of a data warehouse

When your company decides that "it is time to build a data warehouse", what thoughts come to mind?

1) A magical fairy ice cream land where data is presented in chocolate shells for everyone to digest perfectly;
2) A big literal warehouse in the industrial section of town with rusty old containers;
3) Another place to put data, which means another place for you to track and monitor additional activity;
4) A place to put a pared-down representation of your OLTP database and call it OLAP.

Sorry to dash anyone's hopes, but it is none of the above.

Before anyone starts shooting from the hip about what a data warehouse is, I recommend picking up the book "The Data Warehouse Toolkit" by Ralph Kimball, a very deserved pioneer in the specialty of data warehousing.

Let's start with the goals of a data warehouse. What do you hear through the walls of business when they are talking about data?

"We have oceans of data in this company, but we can't access it."
"We need to slice, dice, and thrice the data in every conceivable manner possible. "
"You've got to make it easy for business people to get at the data directly."
"Why do we have people present the same business metrics at a meeting, but with different numbers?"
"We want people to use infromation to support more fact-based decision making."

Because these concerns are widespread, they have driven the requirements for the data warehouse:

The data warehouse must make an organization's information easily accessible.
The data warehouse must present the organization's information consistently.
The data warehouse must be adaptive and resilient to change.
The data warehouse must be a secure bastion that protexts our information assets.
The data warehouse must serve as the foundation for improved decision making.
The business community must accept the data warehouse if it is to be deemed successful.

So what does a data warehouse look like? I am glad you asked. That will be the next thing I blog about. :)

Until next time,
David