Subject Re: Subtracting minutes from a timestamp
Author Adam
Just remember there are 1440 minutes per day and it becomes quite
easy. The reason you are getting a discrepancy is not a rounding
error, but due to the fact current_timestamp will have seconds.

select * from foo
where some_date - (11/1440) > current_timestamp

should give you the past 10 minutes including the minute that started
just over 10 minutes ago. Remember the last 10 minutes probably
started between 10 and 11 minutes ago, so -10/1440 will return the
last 9 minutes.

It is really quite logical, each full integer represents 1 day. It
allows for very efficient extraction of a date from a timestamp, and
you can use the standard decimal operation subtraction to work out a
difference in time.

If it is a common query, you can create a stored procedure

sp_foo(nummins) that is a wrapper around that query, and protect
yourself from future mistakes.

--- In, "Garrett Smith"
<garrett@m...> wrote:
> I'm wanting to select items that have a timestamp value that's a
> number of minutes from current_timestamp. E.g., to select items that
> have some_date within the last 10 minutes, I'm trying something
> select * from foo where some_date - (10.0/(24.0*60.0)) >=
> current_timestamp
> This is based on the documentation that says the decimal portion of
> number is added as minutes to the timestamp value.
> Apart from this being a very awkward way to express an interval,
> are rounding errors -- e.g. the expression of 10 minutes above
> to behave more like 9 minutes.
> Is there a better way to handle this?
> -- Garrett