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’
2
Answers
We can use the
WEEK()
function here: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 to20221230
which is obviously greater than the function output which is52
. 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
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):
Of course it is more complex – but it is SARGable.
For another week mode the expression must be adjusted.