CODEWITHSUNDEEP
mysqldatetimepython-3.x
Daniel
Daniel

Reputation: 101

How to query a date range from two columns in MySQL using Python

I'm using Python34 working with a MySQL database that has a table with a sent_time and delivered_time column. I am trying to get all the orders that fall between two datetime inputs but I'm only getting the script to look at sent_time and not both sent_time and delivered_time.

So in other words, I'm looking to find all the objects that were sent, in process of being delivered or delivered within a given timeframe.

query = (
    "SELECT sent_time, delivered_time, OBJ, id1, id2 FROM table1 "
    "WHERE sent_time BETWEEN %s AND %s"
)
userIn = dateutil.parser.parse(input('start date:'))
userEnd = dateutil.parser.parse(input('end date:'))

I tried changing the query to be WHERE sent_time or delivered_time BETWEEN %S AND %s but that just returns the entire table.

Any suggestions?

Upvotes: 0

Views: 1355

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35553

the SQL syntax is:

WHERE sent_time  BETWEEN %s AND %e 
OR delivered_time BETWEEN %s AND %e

s for start, e for end, both those points are included in the results.

or, perhaps for your needs:

WHERE %s between sent_time and delivered_time

While not apparent to all, using BETWEEN is problematical for date ranges, and the best practice is to use >= with < instead.

e.g. to get absolutely everything for the year 2015, but absolutely nothing after (or before) that year, then:

select * from sometable
where datecolumn >= '2015-01-01' and datecolumn < '2016-01-01'

Regardless of data precision (day? second? millisecond?) that construct will be accurate

Upvotes: 1

Shadow
Shadow

Reputation: 34231

You have to add an extra between condition for the delivered_time field as well:

query = (
    "SELECT sent_time, delivered_time, OBJ, id1, id2 FROM table1 "
    "WHERE (sent_time BETWEEN %s AND %s) OR (delivered_time BETWEEN %s AND %s)"
)

You need to determine, if you need or or and to combine the 2 criteria. It's not clear to me from the question.

Upvotes: 1

Related Questions