DB2中如何查询时间戳为昨日任意时刻的数据？时间戳转日期操作的性能与索引利用疑问

Let's break down your two questions clearly—this is a super common scenario when working with timestamp columns in DB2, so great call asking about it.

1. Is converting timestamp to date inefficient? #

The CAST(my_timestamp_col AS DATE) operation itself isn't a massive performance hog for DB2; the database handles this conversion quickly for individual rows. The real issue isn't the conversion cost alone, though—it's how this operation sabotages index usage (more on that next). That said, if you're dealing with a huge table, any unnecessary per-row processing can add up over time, so avoiding it where possible is still smart.

2. Can this statement use an index? #

Short answer: No, not with a standard index on my_timestamp_col. When you apply a function like CAST() directly to the column in your WHERE clause, DB2 can't leverage the index on that column. Instead, it has to do a full table scan, convert every timestamp to a date, and then compare each value to your condition. This is a classic case of a non-sargable query—one that prevents index usage.

The Fix: Rewrite to make the query sargable #

Instead of converting the timestamp column, adjust your condition to target the full timestamp range that covers all of yesterday. This lets DB2 use any existing index on my_timestamp_col:

SELECT * 
FROM my_table 
WHERE my_timestamp_col >= CURRENT DATE - 1 DAY 
  AND my_timestamp_col < CURRENT DATE;

Here's why this works:

• CURRENT DATE - 1 DAY gives you the start of yesterday (e.g., if today is 2024-05-20, this becomes 2024-05-19 00:00:00.000000)
• CURRENT DATE gives you the start of today (2024-05-20 00:00:00.000000)
• The range condition captures every timestamp from the start of yesterday up to (but not including) the start of today—exactly all rows where the timestamp falls on yesterday.

This query is sargable, so DB2 can use the index to quickly locate matching rows instead of scanning the entire table.

内容的提问来源于stack exchange，提问作者haba713