CODEWITHSUNDEEP
sqlpostgresqldateconditional-statements
Frederik Spang
Frederik Spang

Reputation: 3454

Getting records from the past x weeks

I've been fighting with an issue about querying the records where created_at is within the current, or past x weeks. Say, today is Wednesday, so that'd be from Monday at midnight, up to now, if x = 1. If x > 1, I'm looking for current week, up to today, or past week, but not using regular interval '1 week' as that'll get me Wednesday to Wednesday, and I'm only looking into "whole" weeks.

I've tried the interval-solution, and also things like WHERE created_at > (CURRENT_DATE - INTERVAL '5 week'). A solution that'll work for both day, month, year etc, would be preferred, as I'm actually building the query through some other backend logic.

I'm looking for a generic query for "Find everything that's been created 'x periods' back.

Edit:

Since last time, I've implemented this in my Ruby on Rails application. This has caused some problems when using HOUR. The built is working for everything but HOUR (MONTH, DAY, and YEAR)

SELECT "customer_uses".* 
FROM "customer_uses" 
WHERE (customer_uses.created_at > DATE_TRUNC('MONTH', TIMESTAMP '2017-09-17T16:45:01+02:00') - INTERVAL '1 MONTH')

Which works correctly on my test cases. (Checking count of this). The TIMESTAMP is generated by DateTime.now to ensure my test-cases working with a time-override for "time-travelling"-tests, therefore not using the built in function.

(I've stripped away some extra WHERE-calls which should be irrelevant).

Why the HOUR isn't working is a mystery for me, as I'm using it with a interpolated string for HOUR instead of MONTH as above like so:

SELECT "customer_uses".* 
FROM "customer_uses" 
WHERE (customer_uses.created_at > DATE_TRUNC('HOUR', TIMESTAMP '2017-09-17T16:45:21+02:00') - INTERVAL '1 HOUR')

Upvotes: 1

Views: 511

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Your current suggested query is almost right, except that it uses the current date instead of the start of the week:

SELECT * FROM your_table WHERE created_at > (CURRENT_DATE - INTERVAL '5 week')

Instead, we can check 5 week intervals backwards, but from the start of the current week:

SELECT *
FROM your_table
WHERE created_at > DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '5 week';

I believe you should be able to use the above query as a template for other time periods, e.g. a certain number of months. Just replace the unit in DATE_TRUNC and in the interval of the WHERE clause.

Upvotes: 1

Related Questions