· 9 years ago · Dec 20, 2016, 08:40 AM
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
14SELECT DATE(r.date),count(d.date)
15FROM dates AS r
16LEFT JOIN table AS d ON d.date = r.date
17GROUP BY DATE(r.date)
18ORDER BY r.date ASC;
19
20my $current_date = Date::EzDate->new();
21$current_date->{'default'} = '{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}';
22while ($current_date <= $final_date)
23{
24 print "$current_datet|t%hash_o_data{$current_date}"; # EzDate provides for automatic stringification in the format specfied in 'default'
25 $current_date++;
26}
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`
93
94select * from (
95select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
96(select 0 as num
97 union all select 1
98 union all select 2
99 union all select 3
100 union all select 4
101 union all select 5
102 union all select 6
103 union all select 7
104 union all select 8
105 union all select 9) n1,
106(select 0 as num
107 union all select 1
108 union all select 2
109 union all select 3
110 union all select 4
111 union all select 5
112 union all select 6
113 union all select 7
114 union all select 8
115 union all select 9) n2,
116(select 0 as num
117 union all select 1
118 union all select 2
119 union all select 3
120 union all select 4
121 union all select 5
122 union all select 6
123 union all select 7
124 union all select 8
125 union all select 9) n3,
126(select 0 as num
127 union all select 1
128 union all select 2
129 union all select 3
130 union all select 4
131 union all select 5
132 union all select 6
133 union all select 7
134 union all select 8
135 union all select 9) n4,
136(select 0 as num
137 union all select 1
138 union all select 2
139 union all select 3
140 union all select 4
141 union all select 5
142 union all select 6
143 union all select 7
144 union all select 8
145 union all select 9) n5
146) a
147where date >'2011-01-02 00:00:00.000' and date < NOW()
148order by date
149
150select n3.num*100+n2.num*10+n1.num as date