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

user_716448 0
Viewed 78625

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?

timestamp postgresql sql
5 Answers

If you want to get week start and end date with week start day is any day in the week (Monday, Tuesday, ...). You can use this way:

day_of_week_index mapping:

{
    'monday': 1,
    'tuesday': 2,
    'wednesday': 3,
    'thursday': 4,
    'friday': 5,
    'saturday': 6,
    'sunday': 7
}

Query template:

SELECT 
concat(
    CASE
        WHEN extract(ISODOW FROM datetime_column) < day_of_week_index THEN cast(date_trunc('week', datetime_column) AS date) - 8 + day_of_week_index
        ELSE cast(date_trunc('week', datetime_column) AS date) - 1 + day_of_week_index
    END, ' - ', 
    CASE
        WHEN extract(ISODOW FROM datetime_column) < day_of_week_index THEN cast(date_trunc('week', datetime_column) AS date) - 8 + day_of_week_index + 6
        ELSE cast(date_trunc('week', datetime_column) AS date) - 1 + day_of_week_index + 6
    END) 
FROM table_name;

Example:

Get week start and end date with week start day is Tuesday:

SELECT 
concat(
    CASE
        WHEN extract(ISODOW FROM TIMESTAMP '2021-12-01 03:00:00') < 2 THEN cast(date_trunc('week', TIMESTAMP '2021-12-01 03:00:00') AS date) - 8 + 2
        ELSE cast(date_trunc('week', TIMESTAMP '2021-12-01 03:00:00') AS date) - 1 + 2
    END, ' - ', 
    CASE
        WHEN extract(ISODOW FROM TIMESTAMP '2021-12-01 03:00:00') < 2 THEN cast(date_trunc('week', TIMESTAMP '2021-12-01 03:00:00') AS date) - 8 + 2 + 6
        ELSE cast(date_trunc('week', TIMESTAMP '2021-12-01 03:00:00') AS date) - 1 + 2 + 6
    END);

=> Result:

2021-11-30 - 2021-12-06

Note: You can change the day_of_week_index following the above mapping to determine the week start day (Monday, Tuesday, ..., Sunday)

user_97489120
Related