Question

When you have timestamp times stored in PostgreSQL and you do a SELECT BETWEEN with just dates, where does the edge of that range lie to the nearest second?

Working

First we create a table to work with.

create table tmp (
  t timestamp
)

Then, inserted a heap of values around the edge of Sep 30th 2021. Here's the timestamps I've picked:

> select * from tmp;
          t          
---------------------
 2021-09-29 11:00:00
 2021-09-30 11:00:00
 2021-09-30 23:55:00
 2021-09-30 23:56:00
 2021-09-30 23:57:00
 2021-09-30 23:58:00
 2021-09-30 23:59:00
 2021-09-30 23:59:59
 2021-10-01 00:00:00
 2021-10-01 00:00:01
(10 rows)

Now, let's try to select our some records between two dates.

> select * from tmp where t between '2021-07-01' and '2021-09-30';
          t          
---------------------
 2021-09-29 11:00:00
(1 row)

Looks like the end range of this is he midnight at the start of Sep 30th, so should really try to specify to the nearest second?

    > select * from tmp where t between '2021-07-01 00:00:00' and '2021-09-30 23:59:59';
          t          
---------------------
 2021-09-29 11:00:00
 2021-09-30 11:00:00
 2021-09-30 23:55:00
 2021-09-30 23:56:00
 2021-09-30 23:57:00
 2021-09-30 23:58:00
 2021-09-30 23:59:00
 2021-09-30 23:59:59
(8 rows)

Ah, ok, so if you want to select timestamps including a particular date you need to specify a timestamp component down to the second (or further) at the end of that day.