Counting Chains of Strings

Advertisements Given a string column col1 containing hierarchical relationships, such as: id1/id3/id4/id5 id2/id3/id6/id4/id10 id1/id7/id8 id10/id3/id14/id21/id34 how would you get the unique numbers of ids under id3? In this case it’s 7: [id4, id5, id6, id10, id14, id21, id34] I have a sketch of a pretty complex solution using arrays by splitting the col1, reversing the… Read More Counting Chains of Strings

Calculate several timedifference give different output

Advertisements I have the following table : entity_id time_c next_time_c 1 ‘2023-01-02 10:34:36’ ‘2023-01-10 15:12:24’ 2 ‘2023-03-01 16:10:12’ ‘2023-03-20 22:47:59’ My goal is to calculate the interval between time_c and next_time_c without Saturday and Sunday and the full interval. I come up with the following query : WITH parms (entity_id, start_date, end_date) AS ( SELECT… Read More Calculate several timedifference give different output

extract only date from current_date + interval data type in postgres

Advertisements I am trying to get only date without time in postgres from the following statement: select current_date – date_trunc(‘day’,interval ‘1 month’); But returns me that: 2023-02-07 00:00:00 What could be going wrong here. I see that date_trunc function returns timestamp and intervals cannot be cast to date type: select current_date – interval ‘1 month’::date;… Read More extract only date from current_date + interval data type in postgres

How to cast "truthy" and "falsey" strings to boolean in Postgres?

Advertisements Suppose the following: create table person( id serial primary key, person_name varchar(55) ); create table category( id serial primary key, category_name varchar(55) ); create table category_subscription( id serial primary key, category_id bigint references category(id), person_id bigint references person(id) ); insert into person values(1, ‘george’); insert into category values(10, ‘homework’), (20, ‘promotion’), (30, ‘delivery’); insert… Read More How to cast "truthy" and "falsey" strings to boolean in Postgres?

group by 15-minute interval in PostgreSQL

Advertisements here’s a sample from my table in Metabase PostgreSQL: timestamp 2023-01-25T05:00:01+03:00 2023-01-25T05:01:01+03:00 2023-01-25T05:05:01+03:00 2023-01-25T05:13:01+03:00 2023-01-25T05:14:01+03:00 2023-01-25T05:20:01+03:00 2023-01-25T05:24:01+03:00 2023-01-25T05:29:01+03:00 I want to group by 15-minute interval and count events that happen in the interval: timestamp count 2023-01-25T05:00 5 2023-01-25T05:15 3 this didn’t work SELECT date_trunc(‘hour’, timestamp_column) + (floor(date_part(‘minute’, timestamp_column) / 15) * interval ’15 minute’)… Read More group by 15-minute interval in PostgreSQL

How to optimize sql requests?

Advertisements How to optimize requests? Hello there is a chat which consists of the following tables Chat rooms id, type(USER_TO_USER, USER_TO_SHOP, USER_TO_PLATFORM), name Chat Room Members room_participants id, participantType (USER, SHOP, PLATFORM), participant_id list of messages room_messages – id, participant_id, text, is_read Additionally: users table id, first_name, last_name, image, image_src shops table id, name, logo,… Read More How to optimize sql requests?