PostgreSQL query on a timestamp array

Question

I have a table with a varchar array column that contains timestamp data. How can I query this table with a date range?

Something like this:

select * 
from events 
where any(occurrences::timestamp[]) between '2013-11-30' and '2013-12-01'

Please supply the table definition and some sample data including the expected output. Btw: You should never (really: never) store timestamps or dates as a string. And the problem you have now is a direct result of that wrong design. — user330315
– user330315, Commented Nov 30, 2013 at 8:05
sqlfiddle.com/#!15/51818/1 its a invalid result. The were condition need to run at same element. — danielgatis
– danielgatis, Commented Nov 30, 2013 at 8:44

Bradia · Accepted Answer · 2013-11-30 17:04:56Z

1

WITH tbl AS(
    SELECT *, unnest(occurrences::timestamp[]) itm
    FROM events 
)
SELECT DISTINCT * 
FROM tbl
WHERE 
  itm BETWEEN '2013-10-01 00:00:00 UTC'::timestamp AND '2013-11-01 00:00:00 UTC' ::timestamp

answered Nov 30, 2013 at 17:04

Bradia

9675 silver badges8 bronze badges

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

1 Comment

Pawel Zieminski Over a year ago

That's probably a separate question, but is there an index on timestamp[] that would support such a query?

Collectives™ on Stack Overflow

PostgreSQL query on a timestamp array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related