CODEWITHSUNDEEP
postgresqldatedatetime
Asle
Asle

Reputation: 827

postgresql find total records for nth weeks backwards from now

I am trying to find a total of records pr. week from current week and backwards 8 weeks. I am running postgresql 9.4. I looked at this post but could not find out how to get the weeks.

My database is darwincore2 and I have a field "registerdate" that looks like this "2011-02-01". I have read the docs on extracting dates. I want to count only backwards from this year and the current week. Anyone have a suggestion? How do I use this code on a SQL statement on my database?

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');

I guess I must filter on the current year also?

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');

Please forgive my little postgresql knowledge!

I tried this:

select date_trunc(registerdate, 'week') as wk, count(*)
from darwincore2
where registerdate >= date_trunc('week', current_date) - '8 week'::interval
group by wk
order by wk;

But I get this error:

ERROR:  function date_trunc(character varying, unknown) is not unique
LINE 1: select date_trunc(registerdate, 'week') as wk, count(*)
           ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Upvotes: 0

Views: 468

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you want to use Postgres's definition of week:

where timestamp >= date_trunc('week', current_date) - '8 week'::interval

Postgres weeks start on Monday, which is consistent with the ISO 8601 standard.

EDIT:

If you want the total number of records per week, then something like this:

select date_trunc(timestamp, 'week') as wk, count(*)
from t
where timestamp >= date_trunc('week', current_date) - '8 week'::interval
group by wk
order by wk;

Upvotes: 1

Related Questions