How to get week start and end date string in PostgreSQL?

I am using PostgreSQL 8.3. I have a table like this:

id        regist_time        result
-----------------------------------
1     2012-07-09 15:00:08      3
2     2012-07-25 22:24:22      7
4     2012-07-07 22:24:22      8

regist_time's data type is timestamp.

I need to find a week time interval(start to end) and sum(result) as num.

I want to get the result as:

      week                    num    
---------------------------------
7/1/2012-7/7/2012              10
7/8/2012-7/14/2012              5
7/15/2012-7/21/2012             3
7/22/2012-7/28/2012            11

I can get the week number just in this year:

SELECT id,regis_time, EXTRACT(WEEK FROM regis_time) AS regweek
FROM tba

The key part is

EXTRACT(WEEK FROM regis_time)

extract function can only get the week number in this year, how can I get start time to end time in one week?

You can use date_trunc('week', ...).

For example:

SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp);
-> 2012-07-23 00:00:00

Then, you can convert this into a date, if you're not interested in a start time.

To get the end date too:

SELECT    date_trunc('week', '2012-07-25 22:24:22'::timestamp)::date
   || ' '
   || (date_trunc('week', '2012-07-25 22:24:22'::timestamp)+ '6 days'::interval)::date;

-> 2012-07-23 2012-07-29

(I've used the default formatting here, you can of course adapt this to use MM/DD/YYYY.)

Note that, if you want to make comparisons on timestamps, instead of using (date_trunc('week', ...) + '6 days'::interval, you might want to add an entire week and use a strict comparison for the end of the week.

This will exclude y timestamps on the last day of the week (since the cut-off time is midnight on the day).

    date_trunc('week', x)::date <= y::timestamp
AND y::timestamp <= (date_trunc('week', x) + '6 days'::interval)::date

This will include them:

    date_trunc('week', x)::date <= y::timestamp
AND y::timestamp < (date_trunc('week', x) + '1 week'::interval)

(That's in the rare cases when you can't use date_trunc on y directly.)

If your week starts on a Sunday, replacing date_trunc('week', x)::date with date_trunc('week', x + '1 day'::interval)::date - '1 day'::interval should work.

select date_trunc('week', regist_time)::date || ' - ' ||
       (date_trunc('week', regist_time) + '6 days') ::date as Week,
       sum(result) Total
from YourTable
group by date_trunc('week', regist_time)
order by date_trunc('week', regist_time)

See proof of concept at SQLFiddle: http://sqlfiddle.com/#!1/9e821/1

This can help, a query to get all days of current week.

select cast(date_trunc('week', current_date) as date) + i
from generate_series(0,6) i

2015-08-17
2015-08-18
2015-08-19
2015-08-20
2015-08-21

To get week start and end date (as 0 for Monday and 4 for Friday):

select cast(date_trunc('week', current_date) as date) + 0 || '-->' ||  cast(date_trunc('week', current_date) as date) + 4;

2015-08-17-->2015-08-21

How to get week start and end date string in PostgreSQL?

Related

Recent Posts