SQL Timstamp Function

Question

Is there any difference between these two queries?

select * from tbl where ts < '9999-12-31-24.00.00.000000';

and

select * from tbl where ts < timestamp('9999-12-31-24.00.00.000000');

When is the timestamp function required? Is there a difference in performance?

Answer 1

At first glance, the first statement will make a string comparison while the second should make date related comparisons.

Answer 2

It depends on your SQL implementation, although assuming the column "ts" is some date/time type, there's practically no difference.

Most SQL implementations have a cast mapping that instructs the engine how to automatically convert data types that fit some pattern, especially if a literally comparison makes no sense. For example, literally comparing a timestamp to a string doesn't make much sense. However, most engines have a mapping that lets it know a string formatted like a date can be automatically converted to a timestamp, in order to be compared to another timestamps.

Answer 3

If ts is a string type:

• 1st one is comparing like for like as strings
• 2nd one will cause ts to be converted to date/time

If ts is a date/time type,

• 1st one will convert the constant to the same date time type as the ts column
• 2nd one is comparing like for like as date/time

If ts is string type, the 2nd one is worst to use because ts will be converted thus invalidating any indexes.

If ts is date/time, there is no difference

Data type precedence applies to most DB engines