Reputation: 1051
I have a database table that looks as follows:
Datum_Dag | kWh_Dag
-----------------------------
2016-10-03 08:35:00 | 0.005
2016-10-03 08:30:00 | 0.004
2016-10-02 19:15:00 | 19.019
2016-10-02 19:10:00 | 19.019
2016-10-02 19:05:00 | 19.015
2016-10-02 19:00:00 | 19.011
2016-10-02 18:55:00 | 19.004
As you can see, the last entry for each day contains the sum of energy collected that day. I would like to retrieve these daily values for a range of days (e.g. last week). As the date contains also the time, I am struggeling with the right query statement.
Row "Datum_Dag" is of type "datetime".
I tried:
SELECT
Datum_Dag,
SUBSTRING(
`Datum_Dag`,
1,
10
) AS this_will_be_grouped,
MAX(kWh_Dag)
FROM
`tgeg_dag`
WHERE
this_will_be_grouped > DATE_SUB(NOW(),INTERVAL 1 WEEK)
GROUP BY
this_will_be_grouped
but the date range does not work.
Any idea?
Thanks a ton!
Upvotes: 0
Views: 53
Reputation: 21523
If kWh_Dag can ever go down (ie, could there be a negative energy collection?) then you should get the last record of each day and then get the energy for that record.
Using a sub query to get the date / time of the latest record for each day, and then joining that back against the table to get the details for that record:-
SELECT b.Datum_Dag,
a.the_day,
b.kWh_Dag
FROM
(
SELECT DATE(Datum_Dag) AS the_day, MAX(Datum_Dag) AS max_date_time
FROM `tgeg_dag`
WHERE Datum_Dag > DATE_SUB(NOW(),INTERVAL 1 WEEK)
GROUP BY the_day
) sub0
INNER JOIN `tgeg_dag` b
ON sub0.max_date_time = b.Datum_Dag
Upvotes: 0
Reputation: 1598
it is due to Where
.
you should use
where SUBSTRING(`Datum_Dag`,1,10) > DATE_SUB(NOW(),INTERVAL 1 WEEK)
or
Where Datum_Dag > DATE_SUB(NOW(),INTERVAL 1 WEEK)
and instead of using SUBSTRING
, you better use cast(Datum_Dag as date)
to convert to date format.
Upvotes: 0
Reputation: 13519
Please give this a try:
SELECT
DATE(Datum_Dag) AS this_will_be_grouped,
MAX(kWh_Dag)
FROM
`tgeg_dag`
WHERE
Datum_Dag > DATE_SUB(CURDATE(),INTERVAL 1 WEEK)
GROUP BY
this_will_be_grouped
Note:
Alias
cannot be referred in where clause of the same select statement.
Date(timestamp)
returns the date part
DATE_SUB(CURDATE(),INTERVAL 1 WEEK)
returns the same date
throughout a day.
DATE_SUB(NOW(),INTERVAL 1 WEEK)
returns different timestamp
in every single second.
Upvotes: 1