Tag Archives: MySql

Surprise! Not all Amazon EC2 compute units are created equal

Posted on by
8

A very interesting discovery made by our sys admin not so long ago: While Amazon EC2 sells its hosting services on the notion of leasing virtualized servers with a guaranteed amount of standard compute units, memory and disk space, it turns out that in fact, not all EC2 compute units are created equal. In other words, imagine you boot up 2 separate virtual servers (or instances as they are known in EC2 speak) and these are both EC2 Extra Large instances. Each instance comes with 8 EC2 compute units – which is essentially supposed to be the amount of raw CPU processing power available to you where the larger the number of compute units, the more processing power your (server) instance should be giving you.

Now one would expect that since you are paying the same amount of money to Amazon for each server instance created of this same type and size, that you should be getting the same performance out of each one. Sadly, that is a very wrong assumption, as our sys admin found out.

Continue reading →

Handy SQL snippet: Easily calculate average age of all members from DOB

Posted on by
Reply

The problem: Let’s say you have a database with a members or users list and you have the birthdate of these members stored in a date or datetime format (i.e. 1985-07-31 or similar). Now let’s say you would like to know the average age of the members in your list. This incidentally was exactly the problem I faced this afternoon. So what’s an easy way to do it in a matter of seconds? Simple once you have it figured it out.

If you have the list stored in a MySQL database, this little snippet will get the job done easily:

SELECT avg( (year( now() ) – year( [birthdate] ) ) + (DAYOFYEAR( now() ) > DAYOFYEAR( [birthdate] ) ) ) FROM [tablename]

Note: you need to replace [tablename] with the actual tablename (without the square brackets of course) and [birthdate] with the actual name of the column in your [tablename] in which you store the birthdate.

So what’s going on here?

Here’s the breakdown of what it’s doing:

      Step 1 – We calculate each member’s age by subtracting the year we are now in from the year of their birth: year( now() ) – year( [birthdate] )
      Step 2 – We check if as of right now, that person’s birthday has already passed, and if so, we will be adding 1 year to their age, if not, we don’t add a year: + ( DAYOFYEAR( now() ) > DAYOFYEAR([birthdate]) )
      Step 3 – We are selecting the birthdays from the relevant table in the db, and with each record, doing the calculation and taking an average, i.e. the avg () portion

And that’s it!

I don’t remember offhand the syntax if you were doing this in another db, such as MS SQLServer or Postgres, but the syntax should be very similar (or exactly the same, just depending on whether those functions operate exactly the same in those dbs).