CODEWITHSUNDEEP
sqloracle-databaseselecttimestamp
JessMcintosh
JessMcintosh

Reputation: 460

SELECT using timestamp in Oracle SQL

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?

Upvotes: 1

Views: 10419

Answers (2)

BrianM
BrianM

Reputation: 71

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().

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 4

Related Questions