AntonSack
AntonSack

Reputation: 1051

MySQL: Search by substrings

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

Answers (3)

Kickstart
Kickstart

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

Honglin Zhang
Honglin Zhang

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

1000111
1000111

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

Related Questions