How to compare time portion of dates for multiple dates

All we need is an easy explanation of the problem, so here it is.

I have a postgresql database with various records which have a date column which looks like 2020-10-12 12:45:55. I am wanting to construct a query that looks for activity with timestamps that are on any day, but between 22:00:00 of one day and 05:00:00 the next day in the actual time portion.

How do I ignore the date and just focus on the time portion of this column? I think I need to split up the date column, but still be able to use < and > operators on the time string which is left over.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

which have a date column which looks like 2020-10-12 12:45:55

That’s not a date column, but a timestamp (date and time). Assuming type timestamp, not timetamptz? (You should always disclose actual table definitions.)

SELECT *
FROM   tbl
WHERE  timestamp_in_disguise::time >= '22:00'
OR     timestamp_in_disguise::time <= '05:00';

See:

Or:

...
WHERE  timestamp_in_disguise::time NOT BETWEEN '05:00' AND '22:00';

The first includes bounds and you can adjust as needed.

The second uses BETWEEN, which always includes bounds – so excludes them in the negated expression.
You can still use a hack to include bounds building on the inside knowledge that Postgres stores times and timestamps with microsecond resolution (6 fractional decimal digits) in its current implementation.

...
WHERE  timestamp_in_disguise::time NOT BETWEEN '05:00:00.000001' AND '21:59:59.999999';

But I strongly advice against the latter. Building on implementation details is brittle and ugly.

Casting the string literals to the right is optional as their type is derived from the typed column.

If we are, in fact, dealing with timestamptz you need to define where in the world it’s supposed to be 22:00 etc. (You may need to think about that with timestamp, too.) See:

About BETWEEN and including lower and upper bound:

If you run this kind of queries a lot, consider an expression index:

CREATE INDEX ON tbl ((timestamp_in_disguise::time));  -- parentheses needed

Method 2

You can use a cast to time. Perhaps this will do:

WHERE end_timestamp - start_timestamp < INTERVAL '1 day'
  AND CAST(start_timestamp AS time) > TIME '22:00:00'
  AND CAST(end_timestamp AS time) < TIME '05:00:00'

That should match everything that begins after 10 p.m. and ends before 5 a.m. on the next day.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply