Search code examples
mysqlconvert-tz

Would you ever use CONVERT_TZ() on a timestamp in a ORDER BY clause?

Can someone give me a reason why you would ever use CONVERT_TZ() on a timestamp in an ORDER BY clause. The timestamp, in this case, will always be in UTC.

I found it in a piece of code I am using, which is buggy right now because of mysql_tzinfo_to_sql, so I want to just remove it.

I can see a reason for using it in other places such as SELECT and WHEN. But is there a valid reason to use it in a ORDER BY?

Solution

  • If they all convert to the same time zone, the conversion should go away for two reason;

    • It breaks indexing
    • It will potentially sort things wrong around DST shifts (when the time is set back, a later UTC time stamp may be sorted before an earlier one when converted to local time)

    Of course if your logic depends on the latter, you may want to keep it since it's a functionality change.