I have a Exasol database with Login values of datatype TIMESTAMP like:
2015-10-01 13:00:34.0
2015-11-02 13:10:10.0
2015-10-06 13:20:03.0
2016-02-01 14:15:34.0
2016-04-03 14:08:10.0
2016-07-01 11:05:07.0
2016-09-03 10:08:12.0
2016-11-15 09:03:30.0
and many many more. I want to do a SQL (SQLite) query where I get like Logins from 09:00:00 to 09:15:00 and logins from 09:15:00 to 09:30:00 and so on in separate tables (no matter what date it is). I already had success with selecting on 1 hour interval with:
...EXTRACT(HOUR FROM entryTime ) BETWEEN 8 and 8
that way i get entries of my database (no matter what date it is) within 1 hour, but i need smaller intervals, like every 09:00:00 - 09:15:00 minutes. Any ideas how to solve this in Exasol (SQLite)?
You can simply convert the time part of your timestamp to a string and do a between, something like:
WHERE to_char(entryTime, 'hhmi') BETWEEN '0900' AND '0915'
If you want to use extract and numeric values, I suggest this:
WHERE (EXTRACT(HOUR FROM entryTime) * 100) + EXTRACT(MINUTE FROM entryTime)
BETWEEN 900 and 915
I'm not in front of my computer now, but this (or something pretty similar) should work.
But I suspect that in both cases EXASOL will create an expression index for the first part of the WHERE clause. Because, I guess, you use EXASOL because you have a huge amount of data and you want fast performance, my suggestion is to have an additional column in your table where you store the time part of entryTime as a numeric value, that will create a proper index give you better performance.