PostgreSQL: How to truncate and group timestamps?

Question

I am working with a database and am using the following query:

SELECT
  evt_block_time,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      evt_block_time
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      evt_block_time
    FROM
      uniswap."Factory_evt_NewExchange"
    ORDER BY
      evt_block_time
  ) as creations

Here's a glimpse at what it returns:

I would like to do a few things. First of all, truncate the timestamps, evt_block_time, by week and then group by week.

NOTE: I tried using date_trunc('week', evt_block_time) under each of my select statements, but it throws an error. See below:

SELECT
  date_trunc('week', evt_block_time),
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time)
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time)
    FROM
      uniswap."Factory_evt_NewExchange"
    ORDER BY
      evt_block_time
  ) as creations

which returns:

Column "evt_block_time" does not exist at line 31, position 26.

Additionally, though I guess it's not required, I would like to only query data from the last 52 weeks (1 year).

Obviously, I'm kinda new to this SQL thing but I'm trying my best. Any help whatsoever would be appreciated!

We would need to know the DBMS you are using: _"Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used."_ — Stefan Wuebbe, Jul 13 '22 at 18:54
@StefanWuebbe Apologies. I will edit the question right now. I am using PostgreSQL. — Chris Miller, Jul 13 '22 at 18:57
`date_trunc('week', evt_block_time)` should have worked, can we see the query which produced the error? — Schwern, Jul 13 '22 at 19:12
@Schwern, I have updated the question to include both that query and the returned error. — Chris Miller, Jul 13 '22 at 19:17

score 0 · Answer 1 · answered Jul 13 '22 at 20:08

0

Your subquery creations does not have a column with alias evt_block_time, consequently you cannot use that column name in the main query.

answered Jul 13 '22 at 20:08

Laurenz Albe

209,280
17
206
263

score 0 · Accepted Answer · answered Jul 13 '22 at 20:10

The problem is you're selecting evt_block_time from the subquery, but the subquery no longer contains evt_block_time, it contains date_trunc('week', evt_block_time).

To fix this, give it a name like evt_block_week and select that.

Since it's a calculated column you can't order by it, but the order by in the subquery does nothing. Remove it. If you want to apply an order, do it in surrounding query.

The orders in the count filters also do nothing, order doesn't matter for a count. Remove them.

Finally, to get the number of each version of timestamp per week, group by evt_block_week. And also order by evt_block_week.

SELECT
  evt_block_week,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap."Factory_evt_NewExchange"
  ) as creations
group by evt_block_week
order by evt_block_week

If you want to only do a range of weeks, use generate_series to generate a list of weeks. If you want to see all weeks, use that as the from sub-query and left join with creations. Order and group by the generated week.

SELECT
  weeks.week,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) as v2_pairs
from (
  select
    generate_series(
      date_trunc('week', '2020-01-01'::date), date_trunc('week', '2020-12-31'::date), '1 week'
    ) as week
) as weeks 
left join
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap."Factory_evt_NewExchange"
  ) as creations on weeks.week = evt_block_week
group by week
order by week

Demonstration.

Thank you so, so much. Makes perfect sense. – Chris Miller Jul 13 '22 at 20:20 — Chris Miller, Jul 13 '22 at 20:20

PostgreSQL: How to truncate and group timestamps?

2 Answers2