0

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.

4
  • It's impossible to answer without data. For all we know, you get no results because there is no valid record Commented Jun 3, 2023 at 6:19
  • I am adding a sample of hits and additional explanation. Commented Jun 3, 2023 at 6:21
  • I am sure they are Commented Jun 3, 2023 at 20:26
  • Perhaps you wanted something like this answer, so sa.func.date_sub(SessionList.session_start, sa.text(f'interval {session_timeout} second')) <= hit.remote_stamp, (date_add(...) for the other condition). Commented Aug 11, 2023 at 11:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.