Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding or Subtracting Time From Postgres Timestamps

I am using the following ruby code, with the PG gem, to return a timestamp that is in Postgres "timestamp without timezone" format. Instead of returning the current value, I would like it to return the value (-1 hour).

def get_latest_timestamp(id)
  conn1 = PGconn.open(:dbname => 'testdb')
  res = conn1.exec("SELECT MAX(time_of_search) FROM listings WHERE id=#{id}")
  res.values[0][0]
end
like image 486
HMLDude Avatar asked Nov 13 '13 02:11

HMLDude


1 Answers

In Postgres, you can add or subtract an interval to or from a timestamp to produce another timestamp, as described here.

So in this case, assuming time_of_search is a timestamp, you could do

"SELECT MAX(time_of_search) - INTERVAL '1 hour' FROM listings WHERE id=#{id}"
like image 93
Sarah Avatar answered Sep 24 '22 01:09

Sarah