Syntax for extract in mysql-CodePudding

Is this the correct use of 'extract' in mysql?

select count(user_id) from users where registration_date >= extract(week from now());

this query is to get the count of registrations in the current week

Tried to replace postgreSQL's 'date_trunc' with 'extract'

CodePudding user response：

If you ask about synthactical correctness, then the answer is YES.

Function usage extract(week from now()) completely matches the pattern described in the documentation.

If you are interested in logic correctness, then the answer is NO.

You use where registration_date >= extract(week from now()). How does this condition is processed?

Firstly the function is evaluated. The function output is numeric WEEK value for specified DATETIME value, and its datatype is BIGINT.

Then the comparing is performed. One of the operands is some DATE datatype, another one is function output (not constant) of BIGINT. So the comparing context is set to numeric according to Type Conversion in Expression Evaluation, the arguments are compared as floating-point (double-precision) numbers. registration_date is converted to something similar to 20221230 which is obviously greater than the function output which is 52. So ALL rows which does not store NULL in the column matches the condition.

The most simple and at the same time logically correct condition is

WHERE DATE_FORMAT(registration_date, '%Y%u') >= DATE_FORMAT(CURRENT_DATE, '%Y%u')

The pattern may be, of course, adjusted if the "week" term does not match the description "Monday is the first day of the week (WEEK() mode 1)". But this condition is not SARGable. Apply it if the index by registration_date column is not present in the table.

So I'd recommend to use the next condition (for week mode 1):

WHERE registration_date >= CURRENT_DATE - INTERVAL (DAYOFWEEK(CURRENT_DATE)   5) MOD 7 DAY

Of course it is more complex - but it is SARGable.

For another week mode the expression must be adjusted.

CodePudding user response：

We can use the WEEK() function here:

SELECT COUNT(user_id)
FROM users
WHERE WEEK(registration_date) >= WEEK(NOW()) AND
      YEAR(registration_date) >= YEAR(NOW());