What does GROUP BY -ing on a date with +1 do?

Database
Enthusiast

What does GROUP BY -ing on a date with +1 do?

I'm analyzing the code of someone else and stumbled on a query like this:

SELECT ...
FROM ...
GROUP BY date_field + 1

What's the difference between this query and one such as the following?

SELECT ...
FROM ...
GROUP BY date_field

If the result to both queries is the same, are they different in terms of performance? Thanks in advance.

  • SQL
  • Teradata
1 REPLY
Teradata Employee

Re: What does GROUP BY -ing on a date with +1 do?

Any expression specified in the GROUP BY clause will be evaluated prior to the grouping operation. In this case, one day waill be added to the contents of the date field for the row and the resulting date will be used to group the rows. 

 

Simply adding 1 to the date will not affect performance but an expression can affect performance if it requires a change to the query plan. For instance if the group by fields are the PI, then the expression would require that the rows be redistributed during grouping rather than grouping locally. In this case, since it is a date field, it is unlikely that it would be part of the PI so the probability is less that it would have a signifcant effect on performance. As always, the best way to determine the effect is to review the explains for variations on the query.