4

Consider an SQL query with the following WHERE clause:

WHERE expires >= DATE_FORMAT(NOW(), '%Y%m%d')

Will the result of DATE_FORMAT() be recalculated for each row, or will it be calculated once and used for each row? If the latter, I can calculate the value in Python and then inject it into the SQL sent to MySQL.

Due to application restraints, the value is actually a LONGTEXT column and this question is a simplification of the problem. The data must be stored in this column type due to the EAV nature of the table.

This question pertains to MySQL 5.7, but if other database management systems, particularly SQLite or Postgres have different behaviour then I would appreciate knowing that as well. Thank you.

Improve this question

3 Answers 3

Reset to default
3

As long as it is run as a single transaction, the value will be re-used on each line, not recalculated. I'm not 100% sure about Postgres or SQL Lite but I'm fairly confident (98%) they will work the same.

In the past when I was more confused about this question, I would pre-fetch the date value to a variable and then reuse the variable in the where clause. Now I realize that was pointless unless I have more than one place in a longer set of statements where I would like to use the value.

As a test, move the calculated date value to the select clause and execute against a large table that takes more than a few seconds to return. You will notice that the first line will have the same date/time value as the last record.

Improve this answer
2

DATE_FORMAT(NOW(), '%Y%m%d') is evaluated only once.
DATE_FORMAT(expires, '%Y%m%d') is re-evaluated, so it is quite inefficient.

Similarly even this seemingly simple expressions is very inefficient:

WHERE YEAR(my_date_column) = 2017

By turning it around, you may be able to use an index:

WHERE my_date_column >= '2017-01-01'
  AND my_date_column  < '2017-01-01' + INTERVAL 1 YEAR

(This answer applies to MySQL/MariaDB; I do not know if other vendors perform some optimization.)

Improve this answer
2

Will the result of DATE_FORMAT() be recalculated for each row, or will it be calculated once and used for each row?

This value is calculated once.

NOW() function returns datetime value of timepoint when query processing was started (see function NOW() description). It's a constant during of all query execution not dependent of row processed (unlike SYSDATE() calculated for each row and returned timepoint when current row is processed).

In most RDBMS there are no analogues of SYSDATE() function at all (because of its non-deterministic), so their behaviour when processing query with statement in question is absolutely the same - the value obtained is considered a constant and is calculated once.

Improve this answer
5
  • Postgres has clock_timestamp() and also statement_timestamp().
    – ypercubeᵀᴹ
    Commented May 21, 2018 at 9:00
  • @ypercubeᵀᴹ So MySQL's NOW() eq Postgre's statement_timestamp() and SYSDATE() eq clock_timestamp() respectively. Thanks!
    – Akina
    Commented May 21, 2018 at 9:05
  • No, MySQL's NOW() and CURRENT_TIMESTAMP are the same in Postgres: NOW() or CURRENT_TIMESTAMP and give the same for the whole transaction. The other 2 are more fine-grained. I suppose MySQL's SYSDATE() equivalent would be clock_timestamp()
    – ypercubeᵀᴹ
    Commented May 21, 2018 at 9:09
  • @ypercubeᵀᴹ NOW() or CURRENT_TIMESTAMP and is the same for the whole transaction. ?? Try in MySQL console select now() before_tran;set transaction isolation level [-= any type =-];start transaction;select now() start_tran;select sleep(2);select now() mid_tran;select sleep(2);select now() end_tran;commit; select now() after_tran; and look on timestamps.
    – Akina
    Commented May 21, 2018 at 9:19
  • I thought it was. I have been wrong before ;)
    – ypercubeᵀᴹ
    Commented May 21, 2018 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.