SELECT using timestamp in Oracle SQL

Question

my problem is trying to use a SELECT statement and order the top 10 by a certain column. I managed to compile something after searching through lots of forums, however I need to confirm that the timestamp in one field is within the last week. I have gotten this to execute however i'm not sure whether this is correct as I can't print the value for the where clause:

SELECT itemid, count(itemid) 
FROM Rateddate 
WHERE TO_CHAR(CURRENT_TIMESTAMP - DATE_RATED) < TO_CHAR(7)  
GROUP BY itemid;

TLDR:

TO_CHAR(CURRENT_TIMESTAMP - DATE_RATED) < TO_CHAR(7)

does this make sure the date_rated timestamp is less than a week old?

Justin Cave · Accepted Answer · 2012-05-11 18:16:37Z

4

It would make more sense to say

WHERE date_rated > sysdate - interval '7' day

if you want data from the last 168 hours. You may want

WHERE date_rated > trunc(sysdate) - interval '7' day

if you want data from any point in the day 7 days ago rather than caring about what time of day it is currently.

answered May 11, 2012 at 18:16

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ollie · Accepted Answer · 2012-05-11 19:36:51Z

0

Wouldn't this work for you:

  trunc(CURRENT_TIMESTAMP - DATE_RATED) < 7

This is assuming that DATE_RATED is a date field. If not, you need to convert it first with TO_DATE().

edited May 11, 2012 at 19:36

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

answered May 11, 2012 at 18:17

BrianM

711 silver badge7 bronze badges

2 Comments

arturro Over a year ago

this throws ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

BrianM Over a year ago

If this is a timestamp filed then you need to cast to a date. What is your table definition?

Collectives™ on Stack Overflow

SELECT using timestamp in Oracle SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related