39

I need to display some data if it's a - new data - updated data let's say, I will be basing these data from a publishdate column and updated column where publishdate and updateddate are both timestamps ? . so how to compute the date if it's a new one ?

sasori
  • 5,249
  • 16
  • 86
  • 138
  • i have a publishdate column, and I want to display the data that are published within this day, and not those were posted yesterday nor earlier – sasori Aug 25 '12 at 20:49

2 Answers2

76

For last 24 hours:

Where publish_date >= sysdate -1

or anytime today (midnight forward)

where publish_date >= trunc(sysdate)

If this is a big table, I assume you have an index on publish_date. If you use trunc(publish_date), it may not be able to use the index (untested, but run an explain plan to be sure).

tbone
  • 15,107
  • 3
  • 33
  • 40
15

Try this

Where TRUNC(sysdate) = TRUNC(publish_date)

sysdate returns today's date with the time. The TRUNC removes the time part

codingbiz
  • 26,179
  • 8
  • 59
  • 96
  • I don't want to spoil a -1, but as @tbone explained, using `TRUNC(publish_date)` is probably a bad idea and an performance killer in this situation – iDevlop Jun 10 '14 at 13:25