UNION ALL date on the same row

Question

I am unioning multiple tables together in Postgres, where the timestamp is slightly different, but the same if I round to the hour. Is it possible to make each union show on the same row, if the date is the same? Currently I'm getting duplicate rows of each date.

select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp), 
COALESCE(count(*), 0) as "1489",
null as "1616"
from actions
where group_id = 1489
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
union all
select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp), 
null as "1489",
COALESCE(count(*), 0) as "1616"
from actions
where group_id = 1616
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
order by 1

It returns:

Answer 1

You can use FULL JOIN like this:

SELECT
    COALESCE(a1.t, a2.t) as t,
    a1."1489",
    a2."1616"
FROM
(
    select 
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t, 
        COALESCE(count(*), 0) as "1489"
    from actions
    where group_id = 1489
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a1
FULL JOIN
(
    select 
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t, 
        COALESCE(count(*), 0) as "1616"
    from actions
    where group_id = 1616
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a2 ON (a1.t=a2.t)
ORDER BY 1;

Or just use CASE:

SELECT
    date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
    SUM(CASE WHEN group_id=1489 THEN 1 ELSE 0 END) as "1489",
    SUM(CASE WHEN group_id=1616 THEN 1 ELSE 0 END) as "1616"
FROM actions
WHERE 
    group_id in (1489, 1616)
    AND TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
GROUP BY 1
ORDER BY 1;

Answer 2

Assumptions

• Current Postgres 9.6.
• actions.created is a bigint column holding a count of ms since 1970-01-01 UTC (epoch in microseconds).
• actions.groupid is integer.

The basic query:

SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC') AS hour  -- !
     , group_id
     , count(*)::int AS ct       -- count never returns NULL
FROM   actions
WHERE  group_id IN (1489, 1616)  -- more?
AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000  -- !
GROUP  BY 1, 2
ORDER  BY 1, 2;

Major points

Don't make your query depend on the time zone setting of the session or you may be in for surprises: The result changes with the setting. Remember that "today" is a function of the time zone you are in. Detailed explanation:

• Ignoring timezones altogether in Rails and PostgreSQL

Replace 'UTC' in above query with the time zone you want your times and dates to reflect. Use a time zone name (not an abbreviation) to be perfectly sure.

• Time zone offset with surprising result

Transform the WHERE condition:

TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7

to the equivalent:

created >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000`

That's generally faster and can use an index on created. (Makes it "sargable".)

Depending on data distribution, size of the table and typical you may want a multicolumn index on (group_id, created DESC) to optimize read performance. Performance optimization depends on the complete picture, though ...

The aggregate function count() never returns NULL. COALESCE(count(*), 0) is useless noise and can safely be replaced with just count(*).

• Query optimization or missing indexes?

Pivot

You want the result pivoted with one column per selected group_id. Use crosstab() from the additional tablefunc module for fastest results. If you are not familiar with it, read basic instructions here first:

• PostgreSQL Crosstab Query

SELECT * FROM crosstab(
    $$SELECT date_trunc('hour', to_timestamp(created / 1000) AT TIME ZONE 'UTC')
           , group_id
           , count(*)::int
      FROM   actions
      WHERE  group_id IN (1489, 1616)  -- more?
      AND    created  >= EXTRACT(EPOCH FROM (now() AT TIME ZONE 'UTC')::date - 7) * 1000
      GROUP  BY 1, 2
      ORDER  BY 1, 2$$

   ,$$SELECT unnest('{1489, 1616}'::int[])$$
   ) AS ct (hour timestamp, "1489" int, "1616" int);  -- more?

Missing elements are NULL. You cannot catch this with COALESCE around count() in the inner query. Use COALESCE in the outer query instead. Like:

SELECT hour, COALESCE("1489", 0) AS "1489", COALESCE("1616", 0) AS "1616" FROM crosstab( ...

Note that hours without any rows are still missing in the result. Use an OUTER JOIN to a complete time grid if you want empty rows in the result. Like:

• Filling in missing dates in record set from generate_series()