Comment by leetrout

If / as we move to analytic workloads it would be awesome to see postgres pickup support for AsOf, time_bucket, etc that duckdb and timescale have.

I don't and never have enjoyed SQL and I much prefer the ergonomics of time_bucket to date_bin.

For example, I would do this in duckdb:

  SELECT
    count(*) as y
    , time_bucket(interval '2 weeks', at::timestamp) as x
  FROM analytics
  WHERE some_bool AND some_haystack = 'needle'
  GROUP BY x
  ORDER BY x

In postgres it looks more like:

  with counts as (
    SELECT 
        date_bin('1 hour'::interval, at, (now() - interval '2 weeks')::timestamp)
        , count(*) c
    FROM analytics a
    WHERE some_bool AND some_haystack = 'needle'
    GROUP BY date_bin
  )
  select series as x, coalesce(counts.c, 0) as y
  from generate_series(
    (now() - interval '2 weeks')::timestamp, 
    now()::timestamp,
    interval '1 hour'
  ) series
  LEFT JOIN counts
  ON counts.date_bin = series;