I have a sessions table with session_start and session_end, both datetime fields.
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| session_start | datetime | YES | | NULL | |
| session_end | datetime | YES | | NULL | |
I have incoming hits, that fit into session if they happened no earlier than "session_timeout" seconds before session_start and no later than "session_timout" after session_end.
I am trying to write sql query that will return matching session or nothing based on hit.remote_stamp (python datetime object), but so far it always returns empty result.
matchingSession = models.SessionList.query.filter(and_(
models.SessionList.session_start - timedelta(0,session_timeout) <= hit.remote_stamp,
models.SessionList.session_end + timedelta(0,session_timeout) >= hit.remote_stamp
)).first()
if matchingSession:
print("Updating existing session.")
else:
print("Session not found. Creating new session.")
it always going to "else" statement and create new session even if it should match existing session +/- session_timeout value (it's set to 3600 btw)
EDIT (as requested in comments): Here's a sample of hits:
MariaDB [cbts]> select * from hits limit 10;
+-----+---------------------+---------------------+------+---------+------+----------+
| id | stamp | remote_stamp | hits | quality | accu | is_valid |
+-----+---------------------+---------------------+------+---------+------+----------+
| 126 | 2021-12-27 13:41:11 | 2021-12-27 13:41:10 | 16 | 37 | 2 | 0 |
| 127 | 2021-12-27 13:41:17 | 2021-12-27 13:41:17 | 16 | 41 | 2 | 0 |
| 128 | 2021-12-27 13:42:02 | 2021-12-27 13:42:01 | 16 | 41 | 2 | 1 |
| 129 | 2021-12-27 13:43:03 | 2021-12-27 13:43:03 | 17 | 39 | 2 | 1 |
| 130 | 2021-12-27 13:43:15 | 2021-12-27 13:43:15 | 18 | 43 | 2 | 0 |
| 131 | 2021-12-27 13:47:31 | 2021-12-27 13:47:31 | 18 | 50 | 2 | 0 |
| 132 | 2021-12-27 13:49:19 | 2021-12-27 13:49:19 | 23 | 51 | 2 | 0 |
| 133 | 2021-12-27 13:49:44 | 2021-12-27 13:49:44 | 13 | 32 | 2 | 1 |
| 134 | 2021-12-28 15:25:37 | 2021-12-28 15:25:36 | 4 | 40 | 2 | 0 |
| 135 | 2021-12-28 21:58:44 | 2021-12-28 21:58:43 | 34 | 30 | 2 | 0 |
+-----+---------------------+---------------------+------+---------+------+----------+
if remote_stamp fit into existing session, it should do some additional stuff (like increment session hits), otherwise - it should create new session.
