Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TIMESTAMP and SYSDATE

Is it a simple way to compare is field of TIMESTAMP type get into last n hours? I can do the calculation based on the day and time, but is it possible something like

timestamp > SYSDATE - 1

(SYSDATE - 1) is yesterday, as far as understand, so that will check if timestamp is within the last 24 hours.

Thanks in advance.

like image 921
Alecs Avatar asked May 10 '12 16:05

Alecs


3 Answers

If you need to look for records within the last n hour, then subtract a time interval from the current time. For Oracle, the quantity of the interval must be in single quotes. There are many intervals you can use (hour, minute, second, day, year, month).

timestampfield > current_timestamp - interval '1' hour
like image 153
Kevin Rohrssen Avatar answered Oct 31 '22 04:10

Kevin Rohrssen


You can add/subtract non-integers too, e.g.:

SELECT SYSDATE - 0.5 FROM dual;

means 12 hours before now.

like image 30
Hali Avatar answered Oct 31 '22 06:10

Hali


You can use like

timestamp > to_timestamp(sysdate-1)
like image 38
hkutluay Avatar answered Oct 31 '22 06:10

hkutluay