Justin Olson Justin Olson - 1 year ago 145
SQL Question

PostgreSQL date range query

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.


Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download