oops sorry, I meant contain, not display . I have a table with a whole bunch of columns, one of which contains the timestamp when a row is added to the table. I'm making a select query which displays the entries in my table by week of month, each week beginning on a monday and ending on a sunday, with the 1st monday of the month marking the start of week 1 for the month. I used this:
SELECT * FROM table1 t WHERE EXTRACT (WEEK FROM (t.datetimestamp, 'MM DD YYYY')) =
(SELECT distinct EXTRACT(WEEK FROM (t.datetimestamp, 'MM DD YYYY'))
FROM table1 t WHERE
(EXTRACT (MONTH FROM (t.datetimestamp, 'MM DD YYYY'))= [month])
AND (to_char ((t.datetimestamp, 'MM DD YYYY'), 'W')= [week of month])
AND (EXTRACT(DOW FROM (t.datetimestamp, 'MM DD YYYY')) = [day of week])
AND (EXTRACT (YEAR FROM (t.datetimestamp, 'MM DD YYYY'))= [year]));
although it doesn't work for weeks when there aren't any entries for mondays. I need something to make sure that there would always be an entry for all mondays. Or if you could suggest a new query or a way to change the week format in postgres, i'd really appreciate it.
If I understand you, this "should" be rather doable.
What you want is a list of entries that fall on a particular week of month (WOM) and your definition varies from Pg's.
Off the top of my head (and I may have some errors here), your WOM (1 thru 5) can be calculated by finding the WOM of the Monday prior to the date in question. This can be calculated by taking an offset of the current date.
The EXTRACT(DOW) function returns 0 thru 6, so the date of Monday prior to any date can be found by finding the difference between a date and the prior monday.
If my math is right, the offset is: - (EXTRACT(DOW, datetimestamp)+6)%7
So the previous mondat is: datetimestamp-(EXTRACT(DOW, datetimestamp)+6)%7