cbgrn_analyze.pl

#/usr/env perl

# サイボウズガルーンのDBに潜って書き込みの時間帯の分布をみるよ。
# メンテナンス時間決めるときに書き込み少なさそうな時間を選んだりするのに使おう。

use strict;
use warnings;
use DBI;
use DateTime;
my $password = 'xxxxxxxx';

# ここも設置状況に合わせて直そう
my $dbh = DBI->connect('DBI:mysql:cb_cbgrn:127.0.0.1:3770', 'cbroot', $password);

#my $sth = $dbh->prepare('SELECT col_ctime FROM tab_grn_bulletin_articleentity');
#my $sth = $dbh->prepare('SELECT col_ctime FROM tab_grn_bulletin_followentity');
my $sth = $dbh->prepare('SELECT col_ctime FROM tab_grn_message_messages');
#my $sth = $dbh->prepare('SELECT col_ctime FROM tab_grn_message_follows');

$sth->execute();

my $hash = {};
while ( my $res = $sth->fetchrow_hashref() ) {
  my $epoch = $res->{col_ctime};
  next if $epoch eq '';
  my $dt   = DateTime->from_epoch( epoch=> $epoch, time_zone =>'GMT')
                     ->set_time_zone('Asia/Tokyo');
  my $hour = $dt->hour;
  $hash->{$hour} += 1;
  print "$epoch $dt $hour\n";
}

$sth->finish;
undef $sth;
$dbh->disconnect;

print "---\n";
for ( my $i = 0; $i <24; $i++ ) {
  print "$i $hash->{$i}\n";
}
exit;

__END__