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

Question

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?

Paul Maxwell · Accepted Answer · 2016-01-14 12:39:32Z

1

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

edited Jan 14, 2016 at 12:39

answered Jan 14, 2016 at 12:26

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

7 Comments

Daniel Over a year ago

how can I make it look at userIn and userEnd for your given syntax?

Daniel Over a year ago

because it is saying I don't have enough parameters

Daniel Over a year ago

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%e delivered_time BETWEEN '2015-09-13 14:12:11' AND %e' at line 1

Paul Maxwell Over a year ago

if you supplied sample data (including parameter values) and the expected result your question would be easier to accurately answer, I have attempted to correct your SQL but offer no python code.

Paul Maxwell Over a year ago

Great! if you look at my text about not using between you will see that I use less than only, not <= and that is important. If you do need both points included in the result then stay with what you have (or use between) but in my example 2016-01-01 is NOT included, just everything less than that point.

|

Shadow · Accepted Answer · 2016-01-14 12:26:56Z

1

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.

answered Jan 14, 2016 at 12:26

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

1 Comment

Shadow Over a year ago

I'm sure you can also determine which of the 2 should be used.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related