Month: February 2014

DateTime Ticks With Objective-C

If you need to generate unique strings for every case, the concept of DateTime.Ticks is probably the best solution for you. It represents the count of seconds since the oldest DateTime object available.

However, DateTime.Ticks usage comes from .NET platform and it is that easy in .NET, not in Objective-C.

To generate a ticks number in Objective-C, you need to do the following:

  1. Create an NSDate (01.01.0001), I know, rocking.
  2. Calculate the ticks between this oldest date and [NSDate date] (which means now).
  3. Turn that double variable to an integer. Well, long integer. Or… long long integer. (True story).

Here’s how I did it:

// Create the date formatter
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"yyyyMMdd"];
 
// Create the oldest day possible
NSDate *ticksDate = [dateFormat dateFromString:@"00010101"];
 
// Get ticks difference between now and 01.01.0001 in double   
double ticksDouble = [[NSDate date] timeIntervalSinceDate:ticksDate];
 
// Turn it to a long long int
long long int ticks = llround(ticksDouble);
 
// Test it
NSLog(@"%lli",ticks);

Selecting Grouped Row Count When Using MySQL GROUP BY Column

You may have been using subqueries to get how many rows were grouped when using MySQL’s GROUP BY function. There’s a little trick that helps to get this number without using subqueries, more clean and faster (when the amount of rows get higher).

Let’s assume we have a table contain folks from different cities. Like this:

folk_name   |   city_name
---------------------------
evren       |   ohio
steve       |   ohio
cem         |   weston
don         |   sunderland
ritchie     |   weston
ian         |   hounslow
alican      |   ohio

When we want to get only city names (distinct) from this table, we may group the data by city_name column like this:

SELECT f.* FROM `folks` f GROUP BY f.`city_name`

And the result would be:

folk_name   |   city_name
---------------------------
ian         |   hounslow
evren       |   ohio
don         |   sunderland
cem         |   weston

The expected behaviour of GROUP BY is to select first rows so other folks from same cities do not appear. It also orders by the GROUP BY column (ascending).

Let’s say we want to get how many folks exist in each city by grouping by. First thing comes to mind could be using a subquery like this:

SELECT f1.*, (SELECT COUNT(f2.`folk_name`) FROM `folks` f2 WHERE f2.`city_name` = f1.`city_name`) AS `folk_count` FROM `folks` f1 GROUP BY `city_name`

And the result would be:

folk_name   |   city_name   |   folk_count
-------------------------------------------
ian         |   hounslow    |   1
evren       |   ohio        |   3
don         |   sunderland  |   1
cem         |   weston      |   2

Let’s admit, subqueries are messy. They make the query more complicated to read and also are question marks on performance.

How about selecting number 1 (one) for each row and sum it up on grouping by?

Doing something like this:

SELECT f.*,SUM(1) AS `folk_count` FROM `folks` f GROUP BY f.`city_name`

Would return:

folk_name   |   city_name   |   folk_count
-------------------------------------------
ian         |   hounslow    |   1
evren       |   ohio        |   3
don         |   sunderland  |   1
cem         |   weston      |   2

Nicely done.