How do you find results that occurred in the past week?

sql postgresql date-arithmetic
Huuuze picture Huuuze · Jan 4, 2012 · Viewed 41.4k times · Source

I have a books table with a returned_date column. I'd like to see the results for all of the books with a returned date that occurred in the past week.

Any thoughts? I tried doing some date math, but Postgres wasn't happy with my attempt.

Answer

Eric picture Eric · Jan 4, 2012

You want to use interval and current_date:

select * from books where returned_date > current_date - interval '7 days'

This would return data from the past week including today.

Here's more on working with dates in Postgres.

Related questions

Subtract hours from the now() function

We have a machine running 24x7. Every day I report the number of pieces it produced per hour. In our case one working day means '2015-06-16 06:00:00' to '2015-06-17 06:00:00' for example. Here is my code: …

Read More
Calculate working hours between 2 dates in PostgreSQL

I am developing an algorithm with Postgres (PL/pgSQL) and I need to calculate the number of working hours between 2 timestamps, taking into account that weekends are not working and the rest of the days are counted only from 8am …

Read More
How to get the end of a day?

I'm using PostgreSQL 8.4. I have a column of the table my_tbl which contains dates (timestamp without timezone). For instance: date ------------------- 2014-05-27 12:03:20 2014-10-30 01:20:03 2013-10-19 16:34:34 2013-07-10 15:24:26 2013-06-24 18:15:06 2012-07-14 07:09:14 2012-05-13 04:46:18 2013-01-04 21:31:10 2013-03-26 10:17:02 …

Read More