Monday, March 25

I don't like facebook

Yesterday I rejected a friend request:
Do I know you? I avoid adding strangers into my friend list. I don't think I know you in person, and I don't find anything interesting in your facebook page.
People always feel obliged when being requested to "be friend". That can be exploited by malicious. Actually there is one independent incident 12 hours after my rejection, and this incident proved my paranoid way is correct:

My facebook got a notification that a friend is tagged in one message, so of course I checked that message. As a friend, it's a courtesy to check on friends' activity, isn't it? Anyway, that message is a total non-sense commercial message without any value, but what makes it distinguished from other message of the same kind is: that author tagged all "friends" in that message, so all friends of those "friends" are getting notification, including me. I replied that message asking my friend: Do you endorse this commercial message? One person of the "friends" replied and say this author will be de-friended. If that person followed my way of adding friends, she wouldn't add that author as friend in the first place.

Compare to facebook, I like Google Plus better. I know that Google+ is described as "Ghost City" at this time. It is not that friendly, and there is not that many tricks to play, so it is not getting too much attention. But in each notification email of "xxx added you on Google+", you can see the reminder saying that if you don't know that person, you don't have to follow her back. That is a very heart-warming reminder because it takes the obligation off your shoulder.

Another thing that is different between Facebook and Google+ (and the other reason why I don't like facebook) is that Facebook put all friends together, while Google+ allows you to create different "circles" to separate your friends. To maintain the friendship in Google+ is not a trivial task, same as maintaining friendship in reality. Facebook simplified this structure, but I don't think that is a good thing.

Wednesday, March 20

DateDiff function in SQL Server

This DateDiff function in SQL Server might not be working as you expected.

For example, now we are looking for records that are older than 4 hours, you would think to do it like that:

select * from table where DateDiff(hour, CreateDate, getdate()) >=4
but that is not correct. Assume the CreateDate is 05:59 and the current time is
09:00 , you will find that the this record is selected, as if this 3 hours and 1 minute record is "older than 4 hours."

We can easily confirm that using these 2 queries:

 select datediff(hour, '2013-03-19 05:59', '2013-03-19 09:00')

The time difference is 3 hours and 1 minute, so I would expect this query to return “3”

select datediff(hour, '2013-03-19 05:00', '2013-03-19 09:59')

The time difference is 4 hours and 59 minute, so I would expect this query to return “4”

Actually, these two queries both return “4”.

So this DateDiff function is simply using the "hour" part of the 2 datetime to do the calculation. It use the "9" of the second datetime to substract the "5" of the first datetime to achieve "4".

Back to our initial question: How to look for records that are older than 4 hours? Use DateAdd:

Select * from table where DateAdd(hour, 4, CreateDate) >= getDate()

This will give a precise calculation of 4 hours.

Add on Sept 16, 2013:
The last query can do the work, but it wasted index, if any. The DB has to visit every item to calculate the DateAdd(hour, 4, CreateDate) in order to find out the candidates. 
It's important not to calculate each column, if you want to DB to use existing index of this column.
So the query can be changed to:

Select * from table where CreateDate >= DateAdd(hour, -4, getDate())

Labels: ,