· 7 years ago · Sep 07, 2018, 08:52 PM
1PHP Mysql group by date from accumulative values
2$sql = "select date(tstamp), sum(".$column.") from mash group by date(tstamp) order by tstamp asc limit 10";
3$result = mysql_query($sql);
4$previous = 0;
5$firstRun = true;
6while($row = mysql_fetch_array($result))
7{
8 $difference = $row[1] - $previous;
9 if (!$firstRun)
10 {
11 $strXML .= "<set name='".$row[0]."' value='".$difference."' color='AFD8F8' />";
12 }
13 $previous = $row[1];
14 $firstRun = false;
15}
16
17--------------------------------------------------------
18
19--
20-- Table structure for table `mash`
21--
22
23CREATE TABLE IF NOT EXISTS `mash` (
24 `id` int(25) NOT NULL AUTO_INCREMENT,
25 `steam` int(25) NOT NULL,
26 `bore_water` int(25) NOT NULL,
27 `boiler1oil` int(25) NOT NULL,
28 `boiler2oil` int(25) NOT NULL,
29 `tstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
30 PRIMARY KEY (`id`)
31) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5362 ;
32
33--
34-- Dumping data for table `mash`
35--
36
37INSERT INTO `mash` (`id`, `steam`, `bore_water`, `boiler1oil`, `boiler2oil`, `tstamp`) VALUES
38(2, 436, 73, 15, 1, '2010-11-25 12:28:03'),
39(3, 495, 74, 36, 1, '2010-11-25 12:38:04'),
40(4, 553, 76, 58, 1, '2010-11-25 12:48:09'),
41(5, 565, 77, 74, 1, '2010-11-25 12:58:05'),
42(6, 584, 79, 78, 1, '2010-11-25 13:08:05'),
43(7, 630, 82, 100, 1, '2010-11-25 13:18:11'),
44(8, 686, 86, 130, 1, '2010-11-25 13:28:07'),
45(9, 740, 89, 151, 1, '2010-11-25 13:38:07'),
46(10, 780, 93, 173, 1, '2010-11-25 13:48:13'),
47(11, 883, 100, 218, 1, '2010-11-25 14:08:10');
48
49+----+------+---------------------+
50| id | val | tstamp |
51+----+------+---------------------+
52| 6 | 1 | 2010-01-02 01:00:00 |
53| 7 | 4 | 2010-01-02 02:00:00 |
54| 8 | 6 | 2010-01-02 03:00:00 |
55| 9 | 15 | 2010-01-02 04:00:00 |
56| 10 | 20 | 2010-01-02 05:00:00 | <-- this value
57| 11 | 1 | 2010-01-03 01:00:00 |
58| 12 | 4 | 2010-01-03 02:00:00 |
59| 13 | 6 | 2010-01-03 03:00:00 |
60| 14 | 15 | 2010-01-03 04:00:00 |
61| 15 | 20 | 2010-01-03 05:00:00 | <- this value
62| 1 | 2 | 2010-02-01 01:00:00 |
63| 2 | 8 | 2010-02-01 02:00:00 |
64| 3 | 16 | 2010-02-01 03:00:00 |
65| 4 | 32 | 2010-02-01 04:00:00 |
66| 5 | 64 | 2010-02-01 05:00:00 | <- this value
67+----+------+---------------------+
68
69SELECT DATE(tstamp), MAX(val)
70FROM mash
71GROUP DATE(tstamp)
72ORDER BY tstamp ASC;
73
74+--------------+----------+
75| DATE(tstamp) | MAX(val) |
76+--------------+----------+
77| 2010-01-02 | 20 |
78| 2010-01-03 | 20 |
79| 2010-02-01 | 64 |
80+--------------+----------+
81
82select DATE_FORMAT(tstamp, '%Y-%m-%c') as ymd, sum(".$column.") from mash group by ymd order by tstamp asc limit 10
83
84select YEAR(tstamp) as year, MONTH(tstamp) as month, DAY(tstamp) as day, sum(".$column.") from mash group by year, month, day order by tstamp asc limit 10
85
86create temporary table foo select * from bar;
87
88create temporary table non_accumulative
89select b.id, b.rain - f.rain as "rain", rain_timestamp from bar b join foo f on (f.id + 1) = b.id;
90
91select sum(rain), date(rain_timestamp) from non_accumulative group by date(rain_timestamp) order by rain_timestamp;