CODEWITHSUNDEEP
sqlpostgresqldate
phillip chiarato
phillip chiarato

Reputation: 45

Postgresql query a weeks worth of data based on a calendar day regardless which one

In PostgreSQL, how could I get the dates of all days in a week, using the date of a single day as input? For example, imagine a user clicks on on a day in a calendar user interface widget. That highlights the entire week.

I want to write a query that pulls all rows from a table where a date column matches the date from any day in the highlighted week, regardless of whether the user selected Monday, Wednesday, etc.

Upvotes: 1

Views: 1121

Answers (2)

pozs
pozs

Reputation: 36214

Your best option would be date_trunc('week', ts), like in

WHERE date_trunc('week', ts_col) = date_trunc('week', ?)

However, this won't use indexes on ts_col. And date_trunc('week', ts_col) is only index-able if ts_col is timestamp [without time zone]. timestamptz (or timestamp with time zone) is not able to use date_trunc() based expression indexes. But you can make this sargable with:

WHERE ts_col BETWEEN date_trunc('week', ?) AND date_trunc('week', ?) + interval '1 week'

Note that date_trunc('week', ...) uses the ISO concept of a week, i.e. all of its weeks start on Monday. If that's not good enough for you, you can tweak the following expression: date_trunc('day', ts) - interval '1 day' * extract(dow from ts). This will calculate f.ex. the week start when weeks start on Sunday. If you're aiming other than Sunday, just use some math on the expression above, like:

select date_trunc('day', ts) - interval '1 day' * extract(dow from ts) "sunday",
       date_trunc('week', ts) "monday",
       date_trunc('day', ts) - interval '1 day' * ((extract(dow from ts) + 5)::int % 7) "tuesday",
       date_trunc('day', ts) - interval '1 day' * ((extract(dow from ts) + 4)::int % 7) "wednesday"

http://rextester.com/KZX2905

Upvotes: 1

user330315
user330315

Reputation:

You should combine the week and year to make the query stable even on the end/start of a year. E.g.: 

select *
from some_table
where to_char(some_date_column, 'iyyy-iw') = to_char(date '2017-06-06', 'iyyy-iw')

iyyy and iw is the ISO week numbering. The ISO week always starts on Monday.

or using the extract function:

select *
from some_table
where (extract(isoyear from some_date_column), extract(week from some_date_column)) 
      = (extract(isoyear from date '2017-06-06'), extract(week from date '2017-06-06'))

Note that extract(week ...) is always the ISO week. There is no corresponding "non-iso" keyword for the extract() function

Upvotes: 3

Related Questions