CODEWITHSUNDEEP
pythonmysqlpython-3.x
Yasii
Yasii

Reputation: 1842

Check whether the timestamps is between are between two columns

I need to check whether a timestamp range is within the timestamp range from two field in the database.

I have 2019-09-02 09:20:00 and 2019-09-02 09:28:00 in the database.

I need to check whether 2019-09-02 09:22:00 and 2019-09-02 10:40:00 is between the above time range.

---update---

following is the query I used.

self.cursor.execute(
        "SELECT * FROM time_tracker.time_tracker_details WHERE((created_time  <= %s AND stopped_time >= %s) OR (created_time  >= %s AND stopped_time <= %s))"
        "ORDER BY created_time ASC",
        [str(created_time), str(stopped_time), str(created_time), str(stopped_time)])

Thanks in Advance

Upvotes: 0

Views: 62

Answers (3)

Yasii
Yasii

Reputation: 1842

finally found what I was hoping for

SELECT * FROM time_tracker.time_tracker_details WHERE parent_tt_id = '' AND user_id = %s AND 
(( %s >= created_time  AND stopped_time >= %s)) OR (created_time  >= %s AND stopped_time <= %s)
OR  ( %s >= created_time AND ( %s <= stopped_time AND stopped_time <= %s)) 
OR (( %s <= created_time  AND created_time <= %s) AND stopped_time >= %s)) 
ORDER BY tt_id ASC

Upvotes: 0

jcHernande2
jcHernande2

Reputation: 291

you can try changing OR for AND, where dates are greater than created_time and less than stop_time

example:

AND ((created_time  <= %s AND created_time <= %s) AND (stopped_time >= %s AND stopped_time >= %s))

Upvotes: 1

Nick
Nick

Reputation: 697

It is not entirely clear to me what you want to test whether both dates are between created_time and stopped_time or whether one of them is.

The answer below asumes you want to test whether BOTH dates are within the range (started_time-stopped_time)

Actually there is a keyword BETWEEEN in SQL which I find really describtive. You can use it like that 

..WHERE (%s BETWEEN created_time AND stopped_time) AND (%s BETWEEN created_time AND stopped_time)

To improve readability; Keep in mind BETWEEN is inclusive, what you require here.

BETWEEN DOCS

Upvotes: 1

Related Questions