Basically I have a PostGIS layer with a date column.

I only need to show features that have the last week as a value in the date column. How to achieve this?

Unfortunately now() - 1 week doesn't seem to work

  • 2
    Do you mean last week (somewhen between mon and sunday last week) or exactly 7 days past? – markgraeflerland Nov 9 at 12:23
  • @markgraeflerland exactly 7 days, thought that was the definition of a week – Luffydude Nov 9 at 13:50
  • @Luffydude not necessarily. There are several definitions (any 7 days, Monday-Sunday, or Sunday-Saturday (and other start-end days), plus partial weeks for the 1st or last week of the year). The intended usage in terms of weeks or days is relevant ex: Monday of week 1 is "last week" from Sunday of week 2, but they are more than 7 days apart. – JGH Nov 9 at 14:23
  • @Luffydude: If I have a feature with monday in the date column than a feature created last friday would also be a feature created last week, but not neccessarily 7 days ago. That's the reason why I try to find out what your talking about – markgraeflerland Nov 9 at 14:26
  • Sure guys, make sense. For the purpose of this exercise I would need something like 7 days then – Luffydude yesterday
up vote 5 down vote accepted

If you want to completly filter out the records, i.e. set a filter at the layer level, you can use the Postgres function to create an interval:

"update_date" >= (CURRENT_DATE - interval '1 week')

If you want to style/label the records differently, you can use the age function to return a timespan between two dates, and the week function to extract the number of weeks.

To get result from last week, the rule filter would be:

week(age(now(),"update_date"))<=1
  • Worked like a charm. Thanks dude – Luffydude yesterday

You should use

NOW() - INTERVAL '1 week'

instead of

now() - 1 week

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.