CODEWITHSUNDEEP
sqlpostgresql
IntoTheDeep
IntoTheDeep

Reputation: 4118

SQL select multiple records with limit on each iteration

I have array of timestamps [1544539216000, 1544604587000]; my target is to write a SQL query that for each timestamp return single record, which timestamp is less and closer to current timestamp. (Not sure how to cover cases like if there is no records between and second record return same rows as first..?) DB: Postgres. How is that possible.

EDIT

In other words - "Hey SQL take those timestamps and for each one of them return me a record which timestamp is less and closer". Output count must be equal to input count.

Code I have so far is:

SELECT * 
FROM public."myDate"
WHERE "createdAt" <= '1544539216000' AND "createdAt" <= '1544604587000'
ORDER BY id ASC

Upvotes: 0

Views: 639

Answers (1)

clamp
clamp

Reputation: 3357

Will a plpsql solution do?

CREATE FUNCTION select_by_dates(double precision[])
RETURNS SETOF public."myDate"
AS $$
DECLARE
cur_ts double precision;
out record;
BEGIN
FOREACH cur_ts IN ARRAY $1
LOOP
Select * FROM public."myDate" WHERE "createdAt" < cur_ts ORDER  BY "createdAt" DESC LIMIT 1 INTO out;
RETURN NEXT out;
END LOOP;
RETURN;
END;

$$Language plpgsql;



select * from select_by_dates(ARRAY[1544539216000, 1544604587000]);

Upvotes: 1

Related Questions