[esnard]

In the "Avoid Transformations on Indexed Fields" part, I fail to understand how the example can work if you're applying the timezone computation on the right-hand side.

I'm not familiar with MS SQL (I've only worked with MySQL / PostgreSQL), can someone explain me how it works?

[moron4hire]

Your only failure is because it's just wrong. It's about the same as trying to change "if((a + b) > c)" to "if(a > (c + b))". If this weren't time zones, it'd obviously be "if(a > (c - b))", because you have to balance the equation by applying the same operation to boths sides. But because this is dealing with timezones, the offset of "b" is different depending on the value of "a", so we won't know what to subtract from "c" to get the right comparison. So the right transformation for this "gotcha" is not even possible.

[paulclinger]

I think the advice will still work, but you'd need to switch from "named" timezones to number-specific one, so for example replace `PST` with `-08:00` and then apply the opposite conversion on the right side (as you and I suggested).

[paulclinger]

I don't think it works the way author expects it to work, as the math is not correct. Think about `a+1 < 2` comparison. To remove +1, you need to change it to `a < 2-1`, not to `a < 2+1`; the operation needs to be transformed to the opposite one, which in this case would imply shifting the timezone in the opposite direction.

If you are asking about the timezone shift applied to a date, I think the engine converts the date to 00:00:00 timestamp and then does the timezone conversion.

[jlarocco]

I think the advice is correct, but the examples are not. When the transformation switches to the other side of the comparison it has to be inverted.