Grouping MySQL entries in half hour increments

One of my hobbies at work is creating page-long MySQL queries to reduce the number of calls to the database from php. The following query is very mild in size but took a few weeks of brainstorming to develop. It is part of a reporting feature I built into an asterisk phone server. Calls are logged with an entry time, hold time, agent talk time, and whether the call was abandoned before an agent answered.

$query = "SELECT concat(if(hour(enter)>12,concat(hour(enter)-12),
concat(hour(enter))),':',
if(minute(ENTER) < 30,'00','30')) as `hour`,
concat(HOUR(ENTER),if(minute(ENTER) < 30,'00','30')) as `group`,
if(COMPLETE_CODE='ABANDON','ABANDON','SUCCESS') as `status`,
count(call_id) as `calls`,
SEC_TO_TIME(avg( time_to_sec(hold_time ))) as `average hold time`,
SEC_TO_TIME(avg( time_to_sec(agent_time ))) as `average agent time`
FROM `queue_data`
WHERE date(ENTER) = '".$date."'
GROUP BY HOUR(ENTER),
if(minute(ENTER) < 30,'ABANDON','SUCCESS'),
if(COMPLETE_CODE='ABANDON','ABANDON','SUCCESS')";

Fun queries like this can improve performance by reducing queries and using MySQL's processing engine instead of PHP's and make code cleaner. Happy MySQL'ing

Today's linux hint

I've had an annoying hiccup on a server at work. It's my first production linux server for an outside company for web serving. PHP, Apache, MySQL, and Sendmail are all working great by themselves, but php pages will not use the mail() command. The page goes through immediately, but the mail is never sent.

I tried adding apache as a trusted user in sendmail. I tried making sure that the path to sendmail was correct. Then I tried running a test by typing php mail.php as root from SSH... and it worked.

So I logged into SSH as another user, ran the command again and got

WARNING: RunAsUser for MSP ignored, check group ids (egid=506, want=51)
can not chdir(/var/spool/clientmqueue/): Permission denied
Program mode requires special privileges, e.g., root or TrustedUser.

A simple chmod -R 777 /var/spool/clientmqueue/ cleared up the problem.

So that's my linux hint for the day.

The MCAT experience

I just took the MCAT for the second time yesterday. My first attempt was in 2006, and I wasn't happy with the results. I was in the 70th percentile of testers but only in the 30th percentile of the accepted folks. With a mediocre-for-med school-GPA and no research/face time with faculty, I decided to put things on hold for a few years.

I must say that the new test was much more convenient. It's shorter, which is nice. The highlighting function on the screen is very nice, and the ability to take the test at noon instead of 8am makes a huge difference. Probably the greatest advance in the last 2 years is the online practice tests and analysis.

Two years ago, to practice, students would print out the test, fill in the bubbles, and figure out their grades. Now, everything is calculated automatically and you can find your weak areas immediately. This allows students to be more efficient.

I studied the first time with Examkrackers and tried to digest hundreds of pages of information. This time, I decided to take the practice tests and spend the most time on my weak areas.

After taking a few tests and a few copy/pastes into excel, I created some formulas and found some interesting facts. In all, I took 6 practice tests. In the physical sciences category, 28 out of the 312 questions were on 1 topic in chemistry and 28 were on one topic in physics. Not only were these the most common types of questions, they were also where I missed the highest number of points.

The results in Chemistry were also interesting. Out of the 312 questions in that area, 1/3 of the organic chemistry questions were based on one area and 26 were on a major area in biology. I quickly downloaded these two documents from the MCAT site and used mostly Wikipedia to create my own 20-page study guide for the test. My practice test scores improved significantly from the beginning to the end.

I think that actively discovering the information by research made it easier to retain. I guess we'll see in 29 days when the scores are released.

The first post in the world of Jonathan

Hi. I'm Jonathan and this is my blog. I've tried this before and usually quit after a week or two because nothing exciting was happening. My life is moderately more exciting now and I thought it might be nice to share random bits of information I come across during my life. I work as a sys admin during the day, so most of the time it will be crazy linux hints for people like me who have no formal education in the area.

When I'm not at work, I'm attempting to get into medical school. My prereqs are out of the way, I just took the MCAT, and this summer it's all about the AMCAS and some cool research about uranium-munching bacteria.

The other topics you should expect are probably my conservative libertarian ramblings. I do believe that our government is a little too involved in our lives and will share some thoughts on how things could be improved.

With no more of an introduction, I'll move on to my first post.