#!/usr/bin/perl

# configure
use constant STARTDATE => '2011-02-08 00:00:00';
use constant ENDDATE   => '2011-02-10 12:15:00';
use constant DSN       => 'dbi:mysql:c4l11:avery.infomotions.com';
use constant PASSWORD  => 'nobody';
use constant USERNAME  => 'nobody';
use constant INTERVAL  => 'minutes';
use constant LENGTH    => 5;

# require
use DBI;
use DateTime;
use DateTime::Format::MySQL;
use strict;

# initialize
# initalize
my $dbh       = DBI->connect( DSN, USERNAME, PASSWORD );
my $startdate = DateTime::Format::MySQL->parse_datetime( STARTDATE );
my $enddate   = DateTime::Format::MySQL->parse_datetime( ENDDATE );
my $interval  = INTERVAL;

while ( DateTime::Format::MySQL->format_datetime( $startdate ) le DateTime::Format::MySQL->format_datetime( $enddate ) ) {

	my $start  = DateTime::Format::MySQL->format_datetime( $startdate );
	my $end    = DateTime::Format::MySQL->format_datetime( $startdate->add( $interval => LENGTH ) );
	my $sql    = "SELECT COUNT( tweet_id ) FROM tweets WHERE datetime >= '$start' AND datetime <= '$end'";
	my $tweets = ( $dbh->selectrow_array( $sql ) )[ 0 ];
	my $sql    = "SELECT COUNT( irc_id ) FROM irc WHERE datetime >= '$start' AND datetime <= '$end'";
	my $irc    = ( $dbh->selectrow_array( $sql ) )[ 0 ];
	print "$start\t$irc\t$tweets\n";
	$startdate = DateTime::Format::MySQL->parse_datetime( $end );
	
}

# clean up
$dbh->disconnect;