Recently I’ve been working a lot with dates and tonight I had a hard time finding information about how to group a model by date when the model only have a timestamp column.

Lets pretend we have a user model with a created_at attribute which is stored as a timestamp.

If we want to plot a simple graph showing signups per date this is the way to extract them:

1
User.count(:group => "DATE(created_at)")

Thats all there is to it. From this you will get an OrderedHash containing the date and count of users for each date.

One small thing to remember if you are using PostgreSQL. You will need the DATE() function in any ORDER/SELECT statements because PostgreSQL will only select/order by fields thats in the GROUP BY statement and this is a pretty healthy behavior that many databases (no names here) don’t care about. Think about it. If you are grouping all users by date and use * as selector how would the database know which username or email address to display?

This is what happens if you try to group by created_at without DATE() ->

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
>> User.count(:order => 'created_at DESC', :group => ["DATE(created_at)"])                                                
ActiveRecord::StatementInvalid: PGError: ERROR:  column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT count(*) AS count_all, DATE(created_at) AS date_created_at FROM "users"  GROUP BY DATE(created_at)  ORDER BY created_at DESC
        from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `log'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:507:in `
execute'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:985:in `select_raw'

        from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:972:in `select'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `
select_all_without_query_cache'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'

        from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:255:in `execute_grouped_calculation'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:132:in `
calculate'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:130:in `catch'

        from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:130:in `calculate'
        from /tmp/app/vendor/rails/activerecord/lib/active_record/calculations.rb:48:in `
count'
        from (irb):18

And this is the right way to do it:

1
2
>> User.count(:order => 'DATE(created_at) DESC', :group => ["DATE(created_at)"])
=> #<OrderedHash {"2009-10-14"=>22, "2009-10-25"=>4, "2009-11-04"=>8, "2009-10-15"=>9, "2009-10-26"=>16, "2009-11-05"=>9, "2009-10-16"=>193, "2009-10-27"=>14, "2009-11-06"=>9, "2009-10-17"=>49, "2009-10-28"=>15, "2009-11-07"=>6, "2009-10-18"=>36, "2009-10-29"=>8, "2009-10-19"=>116, "2009-10-30"=>15>

As you may notice the Hash does not look ordered. But if you loop over it you will get them in order:

1
2
3
4
5
6
7
8
9
10
11
12
>> User.count(:order => 'DATE(created_at) DESC', :group => ["DATE(created_at)"]).each {|u| puts "#{u[0]} -> #{u[1]}" }
2009-11-07 -> 6
2009-11-06 -> 9
2009-11-05 -> 9
2009-11-04 -> 8
2009-11-03 -> 14
2009-11-02 -> 20
2009-11-01 -> 10
2009-10-31 -> 6
2009-10-30 -> 15
2009-10-29 -> 8
..

And thats how you group a timestamp field with just its date part.

Cheers!

Posted in Databases, PostgreSQL, Rails, Ruby at November 8th, 2009. 3 Comments
Tagged with , , , , . Written by:

When helping a friend last night with couchdb a got stuck when trying to start it as an unprivileged user. The error messages you get from couchdb is far from easy to understand.

This is my way of solving it.
Read More…

Posted in Databases, Hosting at April 10th, 2009. 5 Comments
Tagged with , , . Written by:

Today I where about to analyze a PostgreSQL database with the wonderful pgFouine. On the machine syslog-ng where installed as logging daemon and I have never worked with PostgreSQL logging and syslog-ng. This post is about how to get them to work together.

Read More…

Posted in Databases, Hosting, PostgreSQL at December 15th, 2008. 2 Comments
Tagged with , , , . Written by:

This is a little memory note for my self and hopefully it can help someone else how to reset MySQL root password.

It happens that we are asked to do some work on our customers dedicated servers and when that happens it’s not that uncommon that there is not a single person alive that has the root password for the MySQL server. When that happen it’s good to know how to reset it 😉

This post is about how simple it’s to reset MySQL’s root password under FreeBSD.
Read More…

Posted in Databases, Hosting, MySQL at November 24th, 2008. 6 Comments
Tagged with , , . Written by:

These are two interesting articles/benchmarks about how you can use SSD drives for storing postgres indexes on them.

The low low seek times on a SSD drives make it a perfect candidate for the indexes.
 
Posted in Databases, PostgreSQL at August 6th, 2008. No Comments
Tagged with , . Written by:

PostgreSQL has lots and lots of sweet internals but its not always easy to know how to find them. Here is an simple way of getting the disk size of a PostgreSQL table.

Read More…

Posted in PostgreSQL at March 16th, 2008. 3 Comments
Tagged with . Written by:

It's past my bedtime is using WP-Gravatar