Which the query datetime_add and datetime_sub more faster?

Question

I have an interview questions, Which of the two SQL statements below would be faster? Assume that the table is indexed using the registration_timestamp column. Explain why.

Query 1

SELECT 
  name
FROM 
  user_table
WHERE 
  DATE(DATETIME_ADD(registration_timestamp, INTERVAL 7 HOUR))
         >= DATE(‘2018-01-01’)
  AND 
  DATE(DATETIME_ADD(registration_timestamp, INTERVAL 7 HOUR)) 
         < DATE(‘2019-01-01’) 

Query 2

SELECT 
  name
FROM 
  user_table
WHERE 
  registration_timestamp
>= DATETIME_SUB(‘2018-01-01’, INTERVAL 7 HOUR)
  AND 
  registration_timestamp
< DATETIME_SUB(‘2019-01-01’, INTERVAL 7 HOUR) 

i'm using sql and bigquery but syntax did not correct. Any idea ?

Answer 1

The second one hands down.

The first one suffers from the "expression on the left hand operator" syndrome. An expression on the column defeats the use of the index.

Answer 2

Assuming the table has an index on registration_timestamp, then most likely that index could only be used in the second version, which is sargable. The first version, which has registration_timestamp appearing inside a function call, would generally not be able to use the index. So, you should generally stick with the second version.

Answer 3

Second Query would be faster than first one.

Reason: Indexing will not be used if you do casting or some manipulation on the indexed column causing your query to run as a simple query. But in second Query indexed are intact and manipulation are done on input only, which can directly use the range index in this case to perform search faster.

A good explanation you can found with example from here