Ruby Time Tracker

Hi guys,

If you are one of those people that works as freelancer or want to keep track of your time for some motive, then this post may interest you.

There was a point in time that I used web sites (free or paid) to track my working time, but I got annoyed because of the time loose reaching those pages or the network issues you might have while using them... and as you may agree with me, nothing is much efficient that using command line tools. So, I've just write a new gem called RTT to track time from the command line. Here is how I use it all the time:

$ rtt 'new task description' (when I started the timer)

To stop (or pause) it, just type:

$ rtt stop (pause)

Then if you've a task already paused, then you can:

$ rtt resume

Or, to restart an existing task already finished, you can type:

$ rtt 'task description'

Then the new time, will be sum to the existing accumulated time for that task.

Also, I always want to check how I'm doing today, so I've type this:

$ rtt list

And I get and output like this:

Task List
=========
Task: || Client: || Project: || User: || Elapsed time: 0h58m
Task: || Client: || Project: || User: || Elapsed time: 2h20mm
Task: || Client: || Project: || User: || Elapsed time: 0h25m
...

Having a time tracking tool for the command line was the main propose to gain time, and avoid all network issues for tracking time using web sites. But also, I've included in this gem a way to generate PDF's documents (or invoices if you will), so that it is easier for you to send your worked hours, using this command:

$ rtt report

So, If it sounds like it feet for you, you can install it as usual, with:

$ [sudo] gem install rtt

There are several more options for filtering tasks for the list, delete or report command, you can check for those here: http://github.com/marklazz/rtt

Enjoy!

Substracting sets with SQL

Hey guys,

Long time no see!

I've wanted to mentioned about an sql's trick that I used on my current project and was pretty handy!

The Problem

Let's say you have a table of users and wanted to write a query to retrieve the rows that meet certain criteria and doesn't meet other... Also, we don't want to use NOT EXISTS statement (as it is too expensive in terms of performance). In short, you want something like this:

You_want = Some_Set_Users (I) - Another_Users_Set (II)

This can be accomplished by simple execute a couple of queries, one for (I) and another for (II) and just subtract them using the programming language. For instance, in Rails you just can do the following:

query_1 = User.find(..conditions for set (I)..)
query_2 = User.find(..conditions for set (II)..)
..and then just using ruby..
result = query_1 - query_2

But, this has a couple of drawbacks:

1) This would load all records into memory which it not good, as it takes time and occupies valuable space
2) And two more importantly in most cases, it's throughput is worse than doing it directly on the database.

So, which is the alternative ?

The Solution

Although, joining tables is not the cheapest operation available for a database, still it does the job pretty efficiently and it can be tunned to be pretty fast depending on your needs.

So, in this case, I propose to use the following strategy:

select some_users_set.* from users as some_users_set LEFT JOIN
(select inner_another_users_set from inner_users as another_users_set where ..conditions for another_users_set...) as another_users_set on some_users_set.id = another_users_set.id
where ..conditions for some_users_set.... AND another_users_set.id IS NULL


The Explanation

The idea behind this sql is to do a LEFT JOIN of the two sets (I and II) that we want to make the subtraction.

This way, as a LEFT JOIN's operation will always match for every row on both sets, when there is no row on set II for a given row on I, we will get all fields for set II filled with NULL (as there is no correspondent on the join). And those cases, are exactly the ones we are looking for, the ones that exists on set I but there is no correspondent on set II.

I find this trick very useful, I hope you could benefit from it too!

Let me know your thoughts, and obviously any questions if there is something unclear!

See ya!