Using Dates in Aggregate Functions in SQL

Today I needed to find the average session length for our site visitors. We keep a stats log for our site visitors that login. So I know when they logged in, and I know when they clicked the logout button. Not all customers click the logout button (about 55% do), but for our purposes it was good enough to only count those customers who click logout. It is hard to know exactly when their session ended if they don't click logout.

The trouble I ran into was that I was dealing with data like this:

6EBFBED 2013-04-04 11:45:47
6EBFBED 2013-04-04 11:47:29
3779C2F 2013-03-27 14:45:04
3779C2F 2013-03-27 15:03:30
5D25F10 2012-10-01 14:30:24
5D25F10 2012-10-01 14:33:17

The first column is session IDs (I have truncated them from the real session IDs). The second column has 2 dates, representing the login time and the logout time for each session. So how can I take data like that and find the session length for each individual session? Here is one method (there may be better ways to do this):

select jsessionID, sessionLength=dateDiff(second,min(visitDate),max(visitDate))
from tempStats
group by jsessionID
order by sessionLength desc

The trick here is that you are using both the SQL Min and Max functions to aggregate the two dates into one row. Then you just have to use the dateDiff function to find the session length. In this case I am getting the session length in seconds. For the above sample records, this is the result:

6EBFBED 102
3779C2F 1106
5D25F10 173

This code was written for SQL Server 2005...I'm not sure if it will work in other databases, but if not I'm sure something similar will.

 

 

Categories: SQL 
No Comments »

CFFormProtect updated

I just released a new version of CFFormProtect. If you don't know, CFFormProtect is a utility that will help prevent spam on your website. Any form that you have on your site can be protected by CFFormProtect.

This release adds a new spam test using the LinkSleeve service. LinkSleeve is a free service that is similar to Akismet, except that it is free for all and does not require an API key. Both LinkSleeve and Akismet are web services that take a website's form contents as input and then they compare the form data to their database of form posts from all of their users. LinkSleeve looks for any URLs in the post and then checks the URL against a threshold. If too many forms have been posted to their service in a short time with the same URL, it is flagged as spam.

To test this service, I posted a URL to their service once, and it came back as not spam. So then I created a loop where I posted the same URL to them 100 times, and after that the URL is now considered spam. I don't know what their thresholds are, but I am pleased with the results.

There are also some minor bug fixes in this release. So if you want to get this latest version of CFFormProtect, head to the project site.

Categories: CFFormProtect ColdFusion 
No Comments »

ColdFusion 10 Web Service Compatibility

We are in the process of setting up new CF 10 servers here at work to replace our production CF 9 servers. We have some web services that are used internally to manage our production servers, and I had a devil of a time getting these web services to work. The internal server that needed to talk to the new CF 10 servers runs CF 9. So the troubles were with CF 9 talking to a CF 10 server over web services. I was able to get it mostly working on my own by struggling through things, but then I saw a post on the official ColdFusion Blog that explains how to get around these problems.

As you may remember, ColdFusion 10 features an upgraded web service engine (Axis). ColdFusion 9 had Apache Axis1 while in CF 10 the web services have been upgraded to Axis2. There are compatibility issues because of this upgrade, but ColdFusion has new features to help you handle this. I don't want to get into the details in this post, but just be aware that if you are having problems between CF 9 and CF 10 web services, check out the link above to solve it.

Categories: ColdFusion 
No Comments »

Scorpio M&M's

A few years ago I attended CFUnited before CF8 was released. Adobe was handing out these branded M&M's. Yesterday I found these on my desk. Do any of you remember getting these? I kept them thinking they were a cool keepsake. :)

Scorpio M&M's thumbnail
(click for larger image)

Categories: ColdFusion 
3 Comments »