0

Is there a way to change the showing date to the end of each week instead of the beginning of the week.

Here's my code:

SELECT date_trunc('week', day + '1 day'::interval)::date - '1 day'::interval  AS anchor, AVG(value) AS average
FROM daily_metrics
WHERE metric = 'daily-active-users'
GROUP BY anchor
ORDER BY anchor

And the result is as below: enter image description here

What I want to achieve is to make it 2018-03-03 (the end of the self defined week) instead of 2018-02-25 (the beginning of the self defined week), 2018-03-10 instead of 2018-03-04...

clemens
  • 16,716
  • 11
  • 50
  • 65
Chen Xu
  • 57
  • 6

2 Answers2

1

Your trick with shifting back and forth by one day works just fine to get the start of your custom week. You get the end or your custom week (Saturday) by adding 5 instead of subtracting 1:

SELECT date_trunc('week', day + interval '1 day')::date + 5 AS anchor ...

Adding an integer to the date, signifying days.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Simply try

SELECT date_trunc('week', day::DATE + 1)::date + 5 AS anchor, AVG(value) AS average
FROM daily_metrics
WHERE metric = 'daily-active-users'
GROUP BY anchor
ORDER BY anchor

When a date is the start date of a week adding 6 (1 + 5) days will move the date to the last date of the week. The the addition of one is to move sundays to the following week and the 5 to get the end of the week from the start date.

Note, PostgreSQL allows the addition of integers (= days) to dates.

clemens
  • 16,716
  • 11
  • 50
  • 65