0

How do I select rows with a timestamp value at any time yesterday? The DBMS is DB2.

SELECT *
  FROM my_table
 WHERE CAST (my_timestamp_col AS DATE) = CURRENT DATE - 1 DAY

This is what I currently have but is casting timestamps as date inefficient? Can it make use of indexes?

haba713
  • 2,465
  • 1
  • 24
  • 45

1 Answers1

1

I would instead recommend:

where my_timestamp_col >= current date - 1 day and
      my_timestamp_col < current date
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    should point out that yes, casting from timestamp to date is inefficient. Generally speaking, using a function on a table column results in a full table scan. Unless you've built an index that includes that function. (wihich may not be available on all platforms & versions of Db2) – Charles Aug 09 '21 at 16:06