I faced this issue while working with Redshift. But it can be found in any SQL database engine.
Redshift is known as special column database, that allows run query in parallel way that to distribution and sort key. Of course, the most widely used sort key is date and should really careful with it in queries. Sort key is associated only with specific date/time format, so converting this field to any format will cause huge drop of performance. Redshift have to convert all stored values to new format during query execution and new converted values have no sort index, so no advantages of sort key.
As an example, let's imagine that there is a column with timestamp type without time zone. In SQL query we need to convert string to timestampt, so let's use function
select * from some_table where timestamp_field > TO_TIMESTAMP('2018-03-24', 'yyyy-MM-dd')
Actually, in this case Redshift should compare field
timestamp_field with type
TIMESTAMP against value
2018-03-24 in format of
TO_TIMESTAMP function returns
timestampTZ value. It will require implicit casting of
timestamp_field to type
timestampTZ and it will be absolutely new field without any sort key.
To avoid performance drop can be used
::TIMESTAMP or directly value
2018-03-24 with no casting, it will be casted implicitly to
I have faced such issue on table with ~60M of rows and
to_timestamp function cased drop of performance to 90 seconds per query against 6 seconds in case of using