1

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)?

BlainTheMono
  • 49
  • 9

2 Answers2

1

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.

mucio
  • 7,014
  • 1
  • 21
  • 33
0

I found a workaround. The solution is to interleave the SQL states. So in first step, you select the hours, and around that SELECT state, you wrap another, where you specify the minutes. 

SELECT * FROM 
       (SELECT * FROM MY_SCHEMA.EXA_LOCAL WHERE EXTRACT(HOUR FROM TIMESTMP) BETWEEN 9 and 9) 
       where EXTRACT(MINUTE FROM TIMESTMP) BETWEEN 0 and 15;
BlainTheMono
  • 49
  • 9