SQL wrong result in calculating week

Question

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

Answer 1

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

Demo: https://dbfiddle.uk/5KZmU18G