Postgresql query between time ranges on a timestamp column

Question

Assuming a table with a timestamp column such as

my_db=# \d l1
                                     Tabelle »public.l1«
    Spalte     |              Typ               | Sortierfolge | NULL erlaubt? | Vorgabewert 
---------------+--------------------------------+--------------+---------------+-------------
 timestamp     | timestamp(6) without time zone |              | not null      |

What is the most efficient way to query for those rows which match

<stub>
time(timestamp)>='10:00:00' and time(timestamp)<='10:30:00'
</stub>

I could not find a suitable BETWEEN statement.

Hi gies0r Does Postgres's tsrange help? Ref https://stackoverflow.com/a/48117316/1123335 — , Jan 19 '20 at 05:53
@pnorton It does not look like this is suitable.. From the docs: **tsrange — Range of timestamp without time zone**. This is not what I search for (because this filters between two datetime-fields). What I am looking for is a range filter, which ignores the date-part of the timestamp column and only filters for the time of day. — gies0r, Jan 19 '20 at 13:07

Jim Jones · Accepted Answer · 2020-01-19T13:59:16.293

The efficiency of your query will rather depend on your data volume and indexing technique. That being said, you can simply cast the column timestamp to time and use a between as you suggested:

SELECT * FROM l1
WHERE CAST(timestamp AS TIME) BETWEEN '10:00:00' AND '13:00:00';

or if you don't need to stick to SQL Ansi (imho a much cooler notation) ..

SELECT * FROM l1
WHERE timestamp::TIME BETWEEN '10:00:00' AND '13:00:00';

Postgresql query between time ranges on a timestamp column

1 Answers1