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 stackoverflow.com/a/48117316/1123335 — user1123335
– user1123335, Commented Jan 19, 2020 at 5: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
– gies0r, Commented Jan 19, 2020 at 13:07

Jim Jones · Accepted Answer · 2020-01-19 13:59:16Z

1

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';

edited Jan 19, 2020 at 13:59

answered Jan 19, 2020 at 13:17

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql query between time ranges on a timestamp column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related