Possible to order column in range of another column?

Question

I have a table with the following two columns: about and create_time.

I want to receive a list of rows that are ordered length(about) DESC but only per chunk of time. For example:

If I have the following rows:

about	create_time
Hello	2023-01-01 00:00:00
Hey	2023-01-05 00:00:00
My name is Daniel	2023-02-01 00:00:00
Hi	2023-02-08 00:00:00
What's up	2023-03-02 00:00:00
Hello	2023-03-04 00:00:00
I like to eat	2023-11-05 00:00:00

And I want to order them per length of about but only in chunks of last 30 days, I'd expect to get the following:

about	create_time
Hello	2023-03-04 00:00:00
What's up	2023-03-02 00:00:00
My name is Daniel	2023-02-01 00:00:00
Hi	2023-02-08 00:00:00
Hey	2023-01-05 00:00:00
Hello	2023-01-01 00:00:00
I like to eat	2023-11-05 00:00:00

So they're ordered by length of about me but per chunk of 30 days, starting from now().

Answer 1

Sounds like a job for date_bin() in Postgres 14 or later:

SELECT *
FROM   tbl
ORDER  BY date_bin('30 days', create_time, now()) DESC, length(about) DESC;

See:

• What is the fastest way to truncate timestamps to 5 minutes in Postgres?