SQL

Querying by relative dates and times in SQL

Simon Reynolds

Simon Reynolds

— 1 min read
Querying by relative dates and times in SQL

Wanting to search for database records within a timeframe is relatively common, particularly if we want to examine error logs.

How we search can make a large difference. The two queries below search for the same set of records in the same table but the first query has the potential for far worse performance

-- Every event in the last day (DATEDIFF)
SELECT * FROM Event e
WHERE DATEDIFF(DAY, e.Timestamp, GETUTCDATE()) <= 1

-- Every event in the last day (DATEADD)
SELECT * FROM Event e
WHERE e.Timestamp BETWEEN DATEADD(DAY, -1, GETUTCDATE()) AND GETUTCDATE()

Because DATEDIFF uses a column of the table as a parameter the database cannot use any index that uses that column which has the potential to massively impact query performance.

The DATEADD doesn't use the column as a parameter of a function so the database can take advantage of any existing index.

This can result in a massive difference in the performance of a query.

A query that allows the database engine to use an index is Sargable (Search ARGument ABLE).

A query is most likely to be non-sargable if the WHERE clause contains a function operating on a column. Although a similar function in the ORDER BY, GROUP BY or HAVING clauses can also affect a query.

As a general rule, if you can at all, avoid using functions which operate in columns in clauses other than SELECT.

Read more