SQL Timstamp Function
Is there any difference between these two queries?
select * from tbl where ts < '9999-12-31-24.00.00.000000';
开发者_C百科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?
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
At first glance, the first statement will make a string comparison while the second should make date related comparisons.
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.
精彩评论