CODEWITHSUNDEEP
mysql
seaders
seaders

Reputation: 4096

With MySQL, why is this date query showing incorrect results?

Sample table:

id | foreign_key_id | timestamp           | amt |
-------------------------------------------------
1  | 223344         | 2018-06-01 09:22:31 | 3
2  | 233445         | 2018-06-15 23:22:31 | 2
2  | 233445         | 2018-06-30 23:22:31 | 5
3  | 334455         | 2018-07-01 12:22:31 | 1
3  | 334455         | 2018-07-15 12:22:31 | 1
4  | 344556         | 2018-07-31 20:22:31 | 2

And what I want is a grouped result of the total of amt per month, something like,

year | month | total_amt
------------------------
2018 |     6 |       10
2018 |     7 |        4

Which I thought would be easily enough achieved with a query like,

SELECT YEAR(timestamp) year, MONTH(timestamp) month, SUM(amt) total_amt
FROM sample_table
WHERE timestamp >= '2018-06-01'
AND timestamp <= '2018-07-31'
GROUP BY YEAR(timestamp), MONTH(timestamp)

Unfortunately, the result of this query is incorrect,

year | month | total_amt
------------------------
2018 |     6 |       10
2018 |     7 |        2

The amount for June is correct, but July is wrong.

Upvotes: 0

Views: 67

Answers (3)

Santosh Jadi
Santosh Jadi

Reputation: 1527

This will work:

SELECT YEAR(timestamp) year, MONTH(timestamp) month, SUM(amt) total_amt
FROM sample_table
WHERE DATE(timestamp) >= '2018-06-01'
AND DATE(timestamp) <= '2018-08-01'
GROUP BY YEAR(timestamp), MONTH(timestamp);

OR

SELECT YEAR(timestamp) year, MONTH(timestamp) month, SUM(amt) total_amt
FROM sample_table
WHERE DATE(timestamp) BETWEEN '2018-06-01' AND '2018-08-01'
GROUP BY YEAR(timestamp), MONTH(timestamp);

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try this:

SELECT YEAR(timestamp) year, MONTH(timestamp) month, SUM(amt) total_amt
FROM sample_table
WHERE timestamp >= '2018-06-01'
AND timestamp < '2018-08-01'
GROUP BY YEAR(timestamp), MONTH(timestamp)

Upvotes: 1

seaders
seaders

Reputation: 4096

This is a misunderstanding of what is timestamp here, and then what is the compared string.

In the timestamp is a date-time object, which has both a date part, and a time part. The string used in the query is just the date part, without the time part, so when MySQL is doing its thing, it basically "zeroes out" the rest of the date.

So when the query is

...
AND timestamp <= '2018-07-31'

It basically turns into,

...
AND timestamp <= '2018-07-31 00:00:00'

And when you dump the row that isn't matched with the query,

...
AND '2018-07-31 20:22:31' <= '2018-07-31 00:00:00'

Whether it's date comparison, or even simple string comparison, the missing row's timestamp is not less or equal to the date passed in, it is definitely more.

You've a few options to fix this, create a full date-time object in the comparison, with the "fullest" of times,

...
AND timestamp <= '2018-07-31 23:59:59'

Change the operator to less-than the next date,

...
AND timestamp < '2018-08-01'

Or convert the timestamp from a date-time object to a date one,

...
AND DATE(timestamp) <= '2018-07-31'

All work, though I'm not sure about which is the best one performance / speed wise.

Upvotes: 2

Related Questions