How can I paginate when ordering by `date_bin`?

Question

I have the following query

SELECT u.update_time, about_me
FROM users u
ORDER BY date_bin('14 days', u.update_time, '2023-04-07 23:11:56.471560Z') DESC, LENGTH(u.about_me) DESC, u.user_id;

I get the following:

update_time	about_me
2023-04-06 19:59:56.771388 +00:00	Hello! How are you?
2023-04-02 03:31:09.833925 +00:00	Hello!!!
2023-04-06 00:36:26.822102 +00:00	Hello!
2023-04-05 19:16:20.968274 +00:00	Hey!

I now want to only get everything after the 3rd row. So I would do the following:

SELECT u.update_time, about_me
FROM users u
WHERE (date_bin('14 days', u.update_time, '2023-04-07 23:11:56.471560Z'), LENGTH(u.about_me)) <
      ('2023-04-07 03:05:24.990233 +00:00', 6)
ORDER BY date_bin('14 days', u.update_time, '2023-04-07 23:11:56.471560Z') DESC, LENGTH(u.about_me) DESC, u.user_id;

But the issue is that I'm still getting the exact same results, it's as if the WHERE isn't working. How can I paginate the query?

Answer 1

Get your case straight. First, a proper table definition. We need a PK or similar to break ties unambiguously:

CREATE TABLE users (
  user_id     int PRIMARY KEY
, update_time timestamptz NOT NULL
, about_me    text NOT NULL
);

The column update_time is type timestamptz. Keep working with timestamptz, and don't mix with timestamp.

Next, sample rows to exhibit possible problems:

INSERT INTO users VALUES
  (1, '2023-04-06 19:59:56.771388 +00:00', 'Hello! How are you?')
, (3, '2023-04-02 03:31:09.833925 +00:00', 'Hello!!!')
, (2, '2023-04-06 00:36:26.822102 +00:00', 'Hello!')
, (4, '2023-04-05 19:16:20.968274 +00:00', 'Hey!')
, (5, '2023-04-02 03:31:09.833925 +00:00', 'Same len')
, (7, '2023-05-02 03:31:09.833925 +00:00', 'Later!!!')
, (6, '2023-03-02 03:31:09.833925 +00:00', 'Earlier!')
;

Proper base query:

SELECT user_id, update_time, about_me
FROM   users u
ORDER  BY date_bin('14 days', update_time, '2023-04-07 23:11:56+0') DESC
        , length(about_me) DESC
        , user_id DESC;  -- !

'2023-04-07 23:11:56+0' being your arbitrary (but constant!) time anchor.

Notably, you added user_id as 3rd expression to ORDER BY to break ties. That's good. But if you want to keep using Row Constructor Comparison, all 3 expressions must share the same sort direction. All ASCENDING or all DESCENDING. (Else it gets more complex and expensive.) See:

• SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'
• How to form a WHERE condition matching multiple ORDER BY items?

Also, add user_id to the SELECT list. It's essential for your pagination.

Then, to get the 1st page with an example page size of 3:

SELECT user_id, update_time, about_me
FROM   users u
ORDER  BY date_bin('14 days', update_time, '2023-04-07 23:11:56+0') DESC
     , length(about_me) DESC
     , user_id DESC
LIMIT  3;

2nd page:

SELECT user_id, update_time, about_me
FROM   users u
WHERE (date_bin('14 days', update_time, '2023-04-07 23:11:56+0'), length(about_me), user_id)
    < ('2023-03-25 00:11:56+01', 8, 5)
ORDER  BY date_bin('14 days', update_time, '2023-04-07 23:11:56+0') DESC
     , length(about_me) DESC
     , user_id DESC
LIMIT  3;

fiddle

('2023-03-25 00:11:56+01', 8, 5) being derived from the last row of the first page.

'2023-03-25 00:11:56+01' is the result of the date_bin() expression. You might add it to the SELECT list for simplicity. (I added it in the fiddle.)

Since your date_bin() expression uses an arbitrary time anchor (and interval?), using an index is sophisticated, but still possible. Work with the lower / upper bounds of possible date_bin() values ...

These two timestamptz values are exactly the same:

'2023-03-24 23:11:56+00'
'2023-03-25 00:11:56+01'

The display depends on the timezone setting of your session, the value is the same. Basics:

• Ignoring time zones altogether in Rails and PostgreSQL