The problem is finding differences (delta days, whatever) between two dates (or numbers) in the same column of a mysql database. I would have thought this was a common problem but couldn't find a good way to do it in SQL.
I couldn't find a suitable solution for so made one myself... (apologies for the rough non-optimum perl code)..
Has anyone got a better way, improvements, comments and optimizations appreciated!
I tried this SQL code from http://forums.databasejournal.com/showthread.php?t=40845 it was
helpful, but it was v-e-r-y s-l-o-w for my big table:
# sql to calculate date differences in same column
# SELECT B.*
# FROM
# (SELECT ordernum, agent, MIN([datetime]) AS mindatetime
# FROM YourTable
# GROUP BY ordernum, agent) AS A
# JOIN YourTable AS B
# ON A.ordernum = B.ordernum
# AND A.agent = B.agent
# AND (A.mindatetime = B.[datetime] OR DATEADD(minute, 120, A.mindatet
+ime) <= B.[datetime])
Actually using the hashes to keep track of things allows lots of cool things to be calculated in from the database (like delta of dates, time, weight etc, )
#!/usr/bin/perl
#query to get the difference between dates and things in the same colu
+mns...
use strict;
use DBI;
use Time::Local;
##################### mysql definitions
#password and access
my $db = "new" ;
my $host = "192.168.1.20" ;
my $user = "user" ;
my $pass = "pass" ;
my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host",$use
+r,$pass)or die "Can't connect to database: $DBI::errstr\n";
############################ mysql connection definitions
#the key thing is to do a select query that results in the items being
+ in order in the resultset...
my $sql_query ="SELECT brand, date, weight FROM test2 ORDER BY brand,
+date ASC";
my $sth_query = $dbh->prepare($sql_query);
$sth_query->execute();
#######################################################
my $brand="";
my $date_old=timelocal(0,0,0,10,10,1980); #just set it to something
my $date;
my $date_diff;
my $weight;
my $weight_old;
my %samples;
my %dates;
my @row;
while(@row = $sth_query->fetchrow_array()){
$brand = $row[0];
$date = $row[1];
$weight = $row[2];
$samples{$brand}++; #increment the number of times this brand
+has appeared
if($samples{$brand} ==1){ #if it is the first time then make old = new
+ so that no big step changes in weight etc recorded
$date_old = $date;
$weight_old = $weight;
}
#difference in two datestrings calculation - convert to seconds - I'm
+sure there are better ways to do this..
$date =~ m|(\d{4}).(\d{2}).(\d{2})|;
my $date_new1= timelocal(0,0,0,$3, $2-1, $1); #the '-1' cos locatime(0
+,0,0,dd,mm,yyyy) months count from zero
$date_old =~ m|(\d{4}).(\d{2}).(\d{2})|;
my $date_old1= timelocal(0,0,0,$3, $2-1, $1);
$date_diff = $date_new1- $date_old1; # and subtract to get difference
+in seconds
$date_diff = $date_diff / 60 / 60 / 24; # then divide by number of sec
+onds in a day to get days
# voila!
print "$brand,\t$samples{$brand}\t$date\t$date_old\t$date_diff,\t$weig
+ht,\t$weight_old,\t".($weight-$weight_old)."\n";
$date_old = $date;
$weight_old = $weight;
}
$sth_query->finish;
$dbh->disconnect;
#here's table crating code for your conveinience
CREATE TABLE `new`.`test2` (
`id` int(11) NOT NULL auto_increment,
`brand` varchar(32) NOT NULL,
`date` date NOT NULL,
`weight` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
INSERT INTO `new`.`test2` VALUES (1,'C','2008-02-06',15),
(2,'C','2008-02-05',20),
(3,'A','2008-02-04',140),
(4,'C','2008-02-03',10),
(5,'B','2008-02-01',190),
(6,'A','2008-02-01',150),
(7,'B','2008-01-03',200),
(8,'A','2008-01-01',110),
(9,'A','2007-01-01',100);
output:
A, 1 2007-01-01 2007-01-01 0, 100, 100,
+ 0
A, 2 2008-01-01 2007-01-01 365, 110, 100,
+ -10
A, 3 2008-02-01 2008-01-01 31, 150, 110,
+ -40
A, 4 2008-02-04 2008-02-01 3, 140, 150,
+ 10
B, 1 2008-01-03 2008-01-03 0, 200, 200,
+ 0
B, 2 2008-02-01 2008-01-03 29, 190, 200,
+ 10
C, 1 2008-02-03 2008-02-03 0, 10, 10,
+ 0
C, 2 2008-02-05 2008-02-03 2, 20, 10,
+ -10
C, 3 2008-02-06 2008-02-05 1, 15, 20,
+ 5
Ooops got the weight subtraction back to front... and there may need to be a -1900 in the date delta calc.