EP6zydUa

1select DATE(date),count(date) from table group by DATE(date) order by date asc;
2	
3while(my($date,$sum) = $sth->fetchrow) {
4    print CSV "$date,$sumn"
5}
6	
7| 2008-08-05 |           4 | 
8| 2008-08-07 |          23 |
9	
10| 2008-08-05 |           4 | 
11| 2008-08-06 |           0 | 
12| 2008-08-07 |          23 |
13	
14my $current_date = Date::EzDate->new();
15$current_date->{'default'} = '{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}';
16while ($current_date <= $final_date)
17{
18    print "$current_datet|t%hash_o_data{$current_date}";  # EzDate provides for     automatic stringification in the format specfied in 'default'
19    $current_date++;
20}
21	
22SELECT DATE(r.date),count(d.date) 
23FROM dates AS r 
24LEFT JOIN table AS d ON d.date = r.date 
25GROUP BY DATE(r.date) 
26ORDER BY r.date ASC;
27	
28use DateTime;
29my $dt;
30
31while ( my ($date, $sum) = $sth->fetchrow )  {
32    if (defined $dt) {
33        print CSV $dt->ymd . ",0n" while $dt->add(days => 1)->ymd lt $date;
34    }
35    else {
36        my ($y, $m, $d) = split /-/, $date;
37        $dt = DateTime->new(year => $y, month => $m, day => $d);
38    }
39    print CSV, "$date,$sumn";
40}
41	
42use DateTime;
43use DateTime::Format::Strptime;
44my @row = $sth->fetchrow;
45my $countdate = strptime("%Y-%m-%d", $firstrow[0]);
46my $thisdate = strptime("%Y-%m-%d", $firstrow[0]);
47
48while ($countdate) {
49  # keep looping countdate until it hits the next db row date
50  if(DateTime->compare($countdate, $thisdate) == -1) {
51    # counter not reached next date yet
52    print CSV $countdate->ymd . ",0n";
53    $countdate = $countdate->add( days => 1 );
54    $next;
55  }
56
57  # countdate is equal to next row's date, so print that instead
58  print CSV $thisdate->ymd . ",$row[1]n";
59
60  # increase both
61  @row = $sth->fetchrow;
62  $thisdate = strptime("%Y-%m-%d", $firstrow[0]);
63  $countdate = $countdate->add( days => 1 );
64}
65	
66CREATE TABLE IF NOT EXISTS `Ordinal` (
67  `n` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`n`)
68);
69INSERT INTO `Ordinal` (`n`)
70VALUES (NULL), (NULL), (NULL); #etc
71	
72SELECT CURDATE() - INTERVAL `n` DAY AS `day`
73FROM `Ordinal` WHERE `n` <= 7
74ORDER BY `n` ASC
75	
76SET @end = CURDATE() - INTERVAL DAY(CURDATE()) DAY;
77SET @begin = @end - INTERVAL 3 MONTH;
78SET @period = DATEDIFF(@end, @begin);
79
80SELECT @begin + INTERVAL (`n` + 1) DAY AS `date`
81FROM `Ordinal` WHERE `n` < @period
82ORDER BY `n` ASC;
83	
84SELECT COUNT(`msg`.`id`) AS `message_count`, `ord`.`date` FROM (
85    SELECT ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH) + INTERVAL (`n` + 1) DAY AS `date`
86    FROM `Ordinal`
87    WHERE `n` < (DATEDIFF((CURDATE() - INTERVAL DAY(CURDATE()) DAY), ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH)))
88    ORDER BY `n` ASC
89) AS `ord`
90LEFT JOIN `Message` AS `msg`
91  ON `ord`.`date` = `msg`.`date`
92GROUP BY `ord`.`date`