Calculating days in the last 3 months with MySQL

In order to try and calculate a 3 month average of data as a new data field, I had to two options:

  1. Divide the count of data by 28 (Assuming for 28 days in a month)
  2. Calculate the number of days in the last 3 months, and divide the count of data by that.

While option 1 would have been far simpler, It wouldn’t really work because it wouldn’t really be a 3 month average. Even if the number was 30, or 30.42 (365 / 12), it would be better, but it still wouldn’t be as accurate as it could be. So I went with option 2, which is fairly simple when you break it down, think laterally and using the MySQL last_day() function.

For this, we need to consider that you’re never going to be able to simply work out the last day of the last 3 months, and add that together. We need to get four figures;

  1. The number of days in the current month so far
  2. The number of days in the previous month
  3. The number of days in the month before that
  4. The difference between the number of days in the month before that, and the date exactly 3 months ago

Then we can simply add those four figures together, and we have the number of days in the past 3 months. Put that logic together with some SQL, and I came up with this:

NOW() AS 'now',
@dayCount4 := DATE_FORMAT(NOW(), '%d') AS 'Days this month so far',
@dayCount3 := DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '%d') AS 'Days last month',
@dayCount2 := DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), '%d') AS 'Days month before last',
@dayCount1 := DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 3 MONTH)), '%d') - DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%d') AS 'Days in month before that',
@dayCount1 + @dayCount2 + @dayCount3 + @dayCount4 AS totalDays

Running this SQL today gives us the following result set;Capture

Note: Running the SQL above won’t give you this exact result set – I’ve altered the query to run as multiple Unioned queries simply to produce an easier to read screenshot

And now, you know how to calculate the number of days in the past 3 months. This logic can obviously be extended to whatever time period you need to use it for.

Or, if you use the search term ‘get days between dates mysql‘ instead of ‘get days in last 3 months mysql‘ when googling how to do this, the first result would tell you there’s already a MySQL function called datediff() that already does all this for you. Or even dayofyear() for now and the date 3 months ago, and calculate the difference. Or even timestampdiff() and tell it you want the difference in months. But It’s always good to think laterally about problems to find your own solution every now and then.

Splitting a large file using PHP

A while ago, I was presented with a somewhat unique problem. I had an automatically generated log file, with a size of 1.2gb (Never let your log files get this massive, kids.) With a file that massive, it becomes very annoying to try and read – most programs will require to load the entire file into RAM in order to read it. Alternate software to read it was an exercise in futility too – nothing seemed to be able to read it fast enough (or at all, in many cases), thanks to the same RAM problem. So i struck upon the idea of splitting the file down into chunks that actually would be readable. And as with most of my bulk processing needs, i once again turned to PHP. Here’s the solution that I came up with:

 $file = new SplFileObject("test_dump.sql");
 $count = 0;
 $lines = "";
 while (!$file->eof()){
  $lines.= $file->fgets(). "\r\n";
  if($count % 100 == 0){
   // write to file
   $output = new SplFileObject('dump'.$count.'.txt', 'w');
   //reset lines to be blank
   $lines = "";

The script makes use of SplFileObject(), an Object Oriented class for handling files, which has a very useful advantage over the older fopen() or file_get_contents() functions – It can read files line by line. This allows the script to essentially load (in this example) 100 lines into a variable, and then dump it into an incrementally named file, which can be easily read. It’s not the most sophisticated solution, but it’s fairly fast, and gets around RAM limitations.

NXRK2013R: An elaborate fix to a nonexistent problem.

If you’re reading this, and you’ve seen this site before, you may notice the new design. I’m calling it ‘Redux’ because it’s rather fitting. But more importantly, because it’s the first thing that popped into my head, and i’m running with it.

The reasons for a new theme, in simple terms, are as follows:

  1. I felt like it.

The reasons for a new theme, in technical terms, are as follows: Continue reading

Why the Xbox One isn’t good value for money; or why PC is still the better choice.

Microsoft announced the pricing of the Xbone: $499 US. Or £429. Which is a pretty ridiculous price for us here in the UK. £429 isn’t a small amount of money at all. In fact, that much money is budget gaming PC territory.

A quick browse through eBuyer and within 5 minutes I’ve sorted this comparable build:


I’ve tried to make it match up with some of the Xbone specs. 8 core cpu? 6 is probably still four too many. 8GB Ram? Not shared between everything, but check. Powerful GPU? Check, and probably a bit more oomph than the Xbone’s APU. 7.1 channel sound? Check. 500gb HDD? Check. Black case? Check, and probably better quality too. Blu-ray? Check.  The only major feature it really seems to be missing is the new Kinect, which is probably what’s driving the price up so much.

I’m also 100% sure this isn’t the best value PC build you could make for the money either. I’m interested to see other people’s builds with an Xbone budget. Bonus points if you can can save enough money for some dog stickers. But if you’ve been waiting for an opportunity to start PC gaming? With the next gen of consoles, now’s probably the best time it’s ever been to make the switch.