Translating date range query from MySQL to Postgres-CodePudding

There is a mysql query, and I need to implement it in a postgresql query.

create table objects(
   object_id int NOT NULL PRIMARY KEY ,
city_id int not null ,
price int ,
area_total int ,
status varchar(50) ,
class varchar(50) ,
action varchar(50) ,
date_create timestamp,
FOREIGN KEY(city_id) references avg_price_square_city(city_id)
);



SET @WeekDate = CAST('2020/11/16' as DATETIME);
SET @WeekRangeStart  = CAST('2020/11/16 00:00:00' as DATETIME);
SET @WeekRangeEnd  = CAST('2020/11/22 00:00:00' as DATETIME);
select
   object_id,ADDDATE(@WeekDate, -(DAYOFWEEK(@WeekDate)-1)  1) as Monday,
   DATE_ADD(ADDDATE(@WeekDate, -(DAYOFWEEK(@WeekDate)-1)  9), INTERVAL -1 SECOND) as Sunday,
   @range := CONCAT(@WeekRangeStart,' - ',@WeekRangeEnd) as 'Диапазон'
FROM objects;

This query outputs two fields and the range given in @WeekRangeStart and @WeekRangeEnd.@Weekdate finds out which day of the week i.e. Monday and Sunday

INSERT INTO newdb.objects (object_id, city_id, price, area_total, status, class, action, date_create) VALUES (1, 1, 4600000, 72, 'active', 'Вторичная', 'Продажа', '2020-11-18 12:23:00');
INSERT INTO newdb.objects (object_id, city_id, price, area_total, status, class, action, date_create) VALUES (2, 2, 5400000, 84, 'active', 'Secondary', 'Sale', '2020-11-19 21:49:35');

The result is:

How can this be organized?With help in range merge?

CodePudding user response：

You can use DATE_TRUNC to get the Mondays from the date in objects and then sunday is simple to calculate

SELECT
object_id,
date_trunc('week', date_create)::timestamp AS "Monday",
(date_trunc('week', date_create)  '6 days'::interval)::timestamp As "Sunday",
date_trunc('week', date_create)::timestamp 
   || ' - '
   || (date_trunc('week', date_create)  '6 days'::interval)::timestamp AS "range"

FROM objects

object_id | Monday              | Sunday              | range                                    
--------: | :------------------ | :------------------ | :----------------------------------------
        1 | 2020-11-16 00:00:00 | 2020-11-22 00:00:00 | 2020-11-16 00:00:00 - 2020-11-22 00:00:00
        2 | 2020-11-16 00:00:00 | 2020-11-22 00:00:00 | 2020-11-16 00:00:00 - 2020-11-22 00:00:00

db<>fiddle here