Even fetching data using PostgreSQL

Question

I have a query that returns all points between two timestamps. If I make a particularly large time-lapse (say 1 year), I can get 10,000 lines. I want to be able to request permission (say, 1 day) and evenly distribute them for 1 day and get 365 lines back. Here is my request as it stands now:

SELECT *
      FROM checkins
      WHERE serial=${serial} AND created_at BETWEEN ${startTimestamp} AND ${endTimestamp}
      ORDER BY created_at DESC
      LIMIT ${limit}
      OFFSET ${offset}

Any ideas on a good strategy using Postgres?

Answer 1

Assuming you have PG 9.4 + , this should do the trick:

SELECT *
FROM checkins
JOIN (
  -- The below returns 366 created_at values within the two time points, inclusive
  SELECT precentile_disc(fraction/365.) WITHIN GROUP (ORDER BY created_at) 
  FROM checkins, generate_series(0, 365) f(fraction)
  WHERE serial = ${serial} AND created_at BETWEEN ${startTimestamp} AND ${endTimestamp}
) USING (created_at)
ORDER BY created_at DESC;

percentile_disc() , . generate_series() [0., 0.004, 0.008, ..., 1.]. ( created_at, ) checkins .

PG "" :

SELECT *
FROM (
  SELECT *, rank() OVER (ORDER BY created_at) AS rnk
  FROM checkins
  WHERE serial = ${serial} AND created_at BETWEEN ${startTimestamp} AND ${endTimestamp}
) sub
WHERE rnk % extract(day from ${endTimestamp} - ${startTimestamp}) = 1
ORDER BY created_at;

1 startTimestamp endTimestamp, , , 365 .