PostgreSQL select only rows whose dates match a specific number of the week in a table

Question

I have a table that looks like this for a span of many years:

      dump_time      | group_id | client_count 
---------------------+----------+--------------
 2014-10-21 19:45:00 |      145 |           74
 2014-10-21 19:45:00 |      131 |          279
 2014-10-21 19:45:00 |      139 |           49

where dump_time is of type 'timestamp without time zone'.

I want to select only rows that match a specific week of the year and a specific day of the week. For instance, I want all rows that are 3rd day of the 15th week of the year. Any idea on how I could do this? I've explored the EXTRACT command, but haven't quite figured it out.

Thanks!

What aspect(s) of the Extract command are confounding you at the moment? — DrHouseofSQL, Mar 30 '18 at 20:13
For instance, in the official doc I see the usage `SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');` but I do not know how I can apply this to the entire table — Vince, Mar 30 '18 at 20:18
I wonder if something like what is contained in the link would work. Some type of cursor. https://stackoverflow.com/questions/22339628/cursor-based-records-in-postgresql — DrHouseofSQL, Mar 30 '18 at 20:23

score 2 · Accepted Answer · answered Mar 30 '18 at 21:00

2

select *
from testme
where extract(week from dump_time) = 15
and extract(dow from dump_time) = 3

answered Mar 30 '18 at 21:00

Chris Curvey

9,738
10
48
70

PostgreSQL select only rows whose dates match a specific number of the week in a table

1 Answers1