PostgreSQL date range query

Question

I have a PSQL database that has fields for "start_time" and "end_time" in a table.

These get imported from a json file as string to a text field in the database. The string has the following format: yyyy-mm-dd hh:mm:ss I want to do a query to give all id's of a table in the database that are within a given date range.

To do this I can do one of two things. Either I can query for a list of start and end times given a date range, and with the return from that query do a separate query for the actual data I am looking to get with WHERE start_time = ANY ('{list of dates go here}')

The other option is to figure out how to change the format of the row to something that is comparable. Something like WHERE start_time > beginning of date range AND end_time > end of date range if the fields are not text fields.

The date range is going to come from a date time picker from a website.

This might be a duplicate post but after hours of searching for a solution to either of those nothing has come up. So anything in the right direction is helpful.

Thanks

I guess I'm not sure what your question is, here. Are you asking whether it's better to store timestamps as timestamps, or as text? Or are you asking how to convert your text-timestamps to timestamp-timestamps? — jmelesky
– jmelesky, Commented Aug 18, 2016 at 18:07
Don't add tags for things that aren't relevant. This is Postgres, not MySQL. — tadman
– tadman, Commented Aug 18, 2016 at 19:24
Added tags and background information in case there was a better solution to process the information on either the server side in python or on the client side in the javascript before or after the dates were put into a query. However postgres is magic and Boris's answer is exactly what I am looking for. — Justin Olson
– Justin Olson, Commented Aug 18, 2016 at 19:52

Boris Schegolev · Accepted Answer · 2016-08-18 18:44:10Z

2

PostgreSQL should go easy on you in this case. Simply cast the string to timestamp and to the comparison.

This should work out of the box:

WHERE start_time::timestamp > '2016-01-01' AND end_time::timestamp < '2016-12-31'

edited Aug 18, 2016 at 18:44

answered Aug 18, 2016 at 18:19

Boris Schegolev

3,7115 gold badges23 silver badges35 bronze badges

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

4 Comments

filiprem Over a year ago

Fair answer. Just two corrections. 1. second comparison should be < not >. 2. For performance on big dataset, you might need expression indexes on these text fields. CREATE INDEX mytable_start_time_idx ON mytable (start_time::timestamp); CREATE INDEX mytable_end_time_idx ON mytable (end_time::timestamp);

Boris Schegolev Over a year ago

All you say is true. Index is the right way to go, and yes, I copied the query from the question itself keeping the mistake in it :)

filiprem Over a year ago

One more bug in your answer. Timestamp < Date comparisons. One must be aware that '2016-12-31 23:55:00' is NOT lesser than '2016-12-31'. So your query will NOT include anything later than '2016-12-31 00:00:00'. Right side should be end_time::timestamp <= '2016-12-31 23:59:59.999' Or even better - end_time::date <= '2016-12-31' (which is simpler to type).

Boris Schegolev Over a year ago

You are right again. It was just an example, though.

Collectives™ on Stack Overflow

PostgreSQL date range query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related