• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3194
  • Last Modified:

Oracle 5 mins or ten minutes ago

What is the best way of finding whether my time stamp or date field in a table was 5 minutes ago?

Some people say timestamp, some say old fashioned date.

Thanks.

I needs to find if the date in the table was greater than five minutes ago. Maybe changed to ten minutes.
0
wilflife
Asked:
wilflife
4 Solutions
 
Gerwin Jansen, EE MVETopic Advisor Commented:
select fields from table where some_date_field > sysdate- 5/1440;

select fields from table where some_date_field > sysdate- 10/1440;
0
 
johnsoneSenior Oracle DBACommented:
What is the data type of the column?  Both DATE and TIMESTAMP are valid data types and they are different.

What is posted by  gerwinjansen will work with DATE data types.  If the column is a TIMESTAMP, it will work, however you will lose the precision of the TIMESTAMP data type.

If you change to the interval syntax, it should work with both data types and you would not lose the TIMESTAMP precision.

select fields from table where some_date_field > sysdate- interval '5' minute;

select fields from table where some_date_field > sysdate- interval '10' minute;
0
 
DavidSenior Oracle Database AdministratorCommented:
johnsone has a good solution; my short add is to address the two methods.  THE TIMESTAMP function will mask down into the sub-seconds, whereas DATE takes it down to the second.

I chuckle at the "old-fashioned" adjective.  Why in my day, we only had hours........
0
 
awking00Commented:
Are you looking for if the field value represents a time within the last 5 (or 10) minutes or if it was earlier than that?
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now