dates in postgres

Question

I want to see how long the client spend time connecting to our website daily. My table source in created as below and contains the data as shown below.

CREATE TABLE source_ (
"nbr" numeric (10),
"begdate" timestamp,
"enddate" timestamp,
"str" varchar(35))
;
INSERT INTO source_
("nbr", "begdate", "enddate", "str")
VALUES
(111, '2019-11-25 07:00:00', '2019-11-25 08:00:00', 'TMP123'),
  (222, '2019-03-01 12:04:02', '2019-03-01 12:05:02', 'SOC'),
  (111, '2019-11-25 19:00:00', '2019-11-25 19:30:00', 'TMP12'),
  (444, '2020-02-11 22:00:00', '2020-02-12 02:00:00', 'MARATEN'),
  (444, '2020-02-11 23:00:00', '2020-02-12 01:00:00', 'MARA12'),
  (444, '2020-02-12 13:00:00', '2020-02-12 14:00:00', 'MARA12'),
  (444, '2020-02-12 07:00:00', '2020-02-12 08:00:00', 'MARA1222')
 
;


create table target_ (nbr numeric (10), date_ int(10), state varchar(30), terms interval);

I did an attempt below, but as you can see i associated the date_ (day of the event) to the beddate which is not always true see (4th row) when the event is between two days.

INSERT INTO target_
(nbr, date_, state,  terms)
 select
   nbr,
   DATE_TRUNC('day', begdate) as date_,
   state,
   sum(term) as terms
   from (
  select
      nbr, begdate,
      (case
            when trim(str) ~ '^TMP' then 'TMP'
            when trim(str) ~ '^MARA' then 'MARATEN'
            else 'SOC'
       end) as state,
 (enddate - begdate)as term from source_ ) X

   group by nbr, date_, state;

expected output

111 2019-11-25 00:00:00+00 TMP 90

222 2019-03-01 00:00:00+00 SOC 60

444 2020-02-11 00:00:00+00 MARATEN 180

444 2020-02-12 00:00:00+00 MARATEN 300

Welcome to SO. Can you also add the expected results?

Jim Jones
– Jim Jones

2020-09-17 10:33:38 +00:00
Commented Sep 17, 2020 at 10:33 — Jim Jones
– Jim Jones, Commented Sep 17, 2020 at 10:33
thank you @JimJones i updated my original post to add it.

direction
– direction

2020-09-18 12:14:57 +00:00
Commented Sep 18, 2020 at 12:14 — direction
– direction, Commented Sep 18, 2020 at 12:14

Gordon Linoff · Accepted Answer · 2020-09-17 10:54:13Z

1

If I understand correctly, you can use generate_series() to expand the periods and then aggregate:

select gs.dte, 
       (case when trim(str) ~ '^TMP' then 'TMP'
             when trim(str) ~ '^MARA' then 'MARATEN'
             else 'SOC'
        end) as state,
       sum( least(s.enddate, gs.dte + interval '1 day') - greatest(s.begdate, gs.dte))
from source s cross join lateral
     generate_series(begdate::date, enddate::date, interval '1 day') gs(dte)
group by state, gs.dte
order by gs.dte, state;

Here is a db<>fiddle.

answered Sep 17, 2020 at 10:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

direction Over a year ago

Thank you, what is lateral please? @gordon Linoff

Gordon Linoff Over a year ago

@direction . . . A good place to start is the documentation (postgresql.org/docs/current/queries-table-expressions.html). However, they are basically like correlated subqueries that can return multiple rows and multiple columns.

direction Over a year ago

tahnk you, please is there a way to convert the dte to number of seconds (int). I saw this function extract(epoch from (dte))

Gordon Linoff Over a year ago

@direction . . . That function will convert a date to the number of seconds since 1970-01-01.

direction Over a year ago

i didn't succeded to implement it in the same code that you propoed. can you help please?

|

Collectives™ on Stack Overflow

dates in postgres

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related