CODEWITHSUNDEEP
sqlpostgresqldatetimestampsubquery
Ty Kroll
Ty Kroll

Reputation: 1395

Perform query for every row in different table

I have a temp table of beginning and ending unix timestamps for a range of days:

create temporary table rr (t timestamp);

insert into rr (t)
    select generate_series(
        (select timestamp 'epoch' + create_dte_timestamp * INTERVAL '1 second'
        from data
        order by create_dte_timestamp asc limit 1),
    current_timestamp, interval '1 day');

create temporary table ts (s numeric(10), e numeric(10));

insert into ts (s, e)
    select date_part('epoch', (to_timestamp(to_char(t, 'MM/DD/YYYY 00:00:00'),   
    'MM/DD/YYYY HH24:MI:SS')) at time zone 'UTC')::int,
    date_part('epoch', (to_timestamp(to_char(t, 'MM/DD/YYYY 23:59:59'),
    'MM/DD/YYYY HH24:MI:SS')) at time zone 'UTC')::int from rr order by t asc;

I want to perform a query for each row of the ts table:

select count(id) from data
where create_dte_timestamp >= ts.s and create_dte_timestamp <= ts.e

How can I do this?

Upvotes: 0

Views: 74

Answers (1)

Thomas Mueller
Thomas Mueller

Reputation: 50127

I would try with a subquery:

select 
  ts.s, ts.e, 
  (select count(id) from data
  where create_dte_timestamp >= ts.s and create_dte_timestamp <= ts.e)
  as c
from ts;

Upvotes: 1

Related Questions