My query result is getting the week wrong. I have this simple table:
CREATE TABLE `test` (
`id` int(10) NOT NULL,
`p_date` datetime DEFAULT NULL,
`amount` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test` (`id`, `p_date`, `amount`) VALUES
(4, '2024-01-02 13:15:38', 150);
I try to catch the post in the table with this query:
SELECT
amount,
date_format(p_date, '%u') as week,
date_format(p_date, '%Y') as year
FROM test
WHERE week(p_date) = 1
AND year(p_date) = 2024
this returns empty. However, if I change the week to 0 it fetches the post. But the date 2024-01-02 is in week 1. So why isn't the post fetched when I run the query with week 1?
Demo: https://dbfiddle.uk/tIzywRY4
I MariaDB 10.4.22
As per the documentation, by default the week
function starts week numbers at 0.
The date 2024-01-02
is in the first week of the year, so the function will return 0
as the week number for that date.
You can vary that by setting the mode
argument of the function. I will reproduce from the documentation the table of values you can use for this option and what they represent:
Mode | 1st day of week | Range | Week 1 is the 1st week with |
---|---|---|---|
0 | Sunday | 0-53 | a Sunday in this year |
1 | Monday | 0-53 | more than 3 days this year |
2 | Sunday | 1-53 | a Sunday in this year |
3 | Monday | 1-53 | more than 3 days this year |
4 | Sunday | 0-53 | more than 3 days this year |
5 | Monday | 0-53 | a Monday in this year |
6 | Sunday | 1-53 | more than 3 days this year |
7 | Monday | 1-53 | a Monday in this year |
Therefore, to get the result you're expecting you could use mode 3, for example
SELECT
amount,
date_format(p_date, '%u') as week,
date_format(p_date, '%Y') as year
FROM
test
WHERE
week(p_date, 3) = 1
AND year(p_date) = 2024