A more elegant way to get current year-month to insert in a 'where' clause-CodePudding

The table I'm querying from has this DateTime column.

| created_time            | 
|:-----------------------:|
|2022-03-19T15:21:52 08:00|
|2022-03-19T13:10:22 08:00|
|2022-03-19T13:09:52 08:00|
|2022-03-19T13:02:47 08:00|
|2022-03-20T20:51:03 08:00|

select extract(year_month from curtime())

Using extract (as above) doesn't work as it will get me: 202203.

SELECT created_time
FROM `freemark-prod-zohocrm`.patients p
where select extract(year_month from curtime())

Therefore the query above will not give me any result as my 'where' clause needs to specifically ask for '2022-03%' and not 202203.

SELECT created_time
FROM `freemark-prod-zohocrm`.patients p
where date_format(p.created_time, '%Y')=(select extract(year from curtime()))
and date_format(p.created_time, '%m')=(select extract(month from curtime()))

Therefore I am currently using the query above to obtain Year='2022' AND Month='03' which I feel doesn't look that nice and might cause me future problems.

I am wondering if there is a more elegant way to get the current 'Year-Month' (eg.'2022-03%') to use in my 'where' clause.

Thank you for your time.

CodePudding user response：

querying based on function calls such as extract(), or others datepart(), etc. are not Sargeable

What you would be better doing is something like

where
       created_time >= '2022-03-01'
   AND created_time < '2022-04-01'

This way, it gets the entire month in question including time portion up to 2022-03-31 @ 11:59:59pm.

Now, to compare automatically against whatever the current date IS, you can do with MySQL Variables to compute the first of the month and beginning of next month for your from/to range.

select 
      ...
   from
      ( select @FirstOfMonth := CONCAT( year(curdate()), '-', month( curdate()), '-01' ),
               @FirstOfNextMonth := date_add( @FirstOfMonth, interval 1 month )) sqlvars,
      `freemark-prod-zohocrm`.patients p
   where
           p.created_time >= @FirstOfMonth
       AND p.created_time < @FirstOfNextMonth

CodePudding user response：

you can combine year and date and test it against the curent date

CREATE TABLE patients
    (`created_time` varchar(25))
;
    
INSERT INTO patients
    (`created_time`)
VALUES
('2022-04-19T15:21:52 08:00'),
    ('2022-03-19T15:21:52 08:00'),
    ('2022-03-19T13:10:22 08:00'),
    ('2022-03-19T13:09:52 08:00'),
    ('2022-03-19T13:02:47 08:00'),
    ('2022-03-20T20:51:03 08:00')
;

SELECT created_time
FROM patients p
where date_format(p.created_time, '%Y-%m')=date_format(current_date(), '%Y-%m')

| created_time              |
| :------------------------ |
| 2022-03-19T15:21:52 08:00 |
| 2022-03-19T13:10:22 08:00 |
| 2022-03-19T13:09:52 08:00 |
| 2022-03-19T13:02:47 08:00 |
| 2022-03-20T20:51:03 08:00 |

db<>fiddle here