0

I have a Exasol datatable with a column that looks like this:

2016-10-25 08:01:36.0    
2016-10-25 08:30:09.0    
2016-10-25 09:00:15.0    
2016-10-26 08:02:38.0    
2016-10-26 10:00:44.0    
2016-10-27 10:00:44.0

the datatype of that column is TIMESTAMP. Now i want to state a SQL query that shows me all the rows where the TIME is like before 12:00:00. No matter what date it is (only the time matters), and separate them from those after 12:00:00. (it would be nice if i could even say something like 'between 8 and 12' and '12 to 18').

Statements like this: 

SELECT * FROM MySCHEMA.MyTable WHERE entryTime < '%12:00:00.%';
SELECT * FROM MySCHEMA.MyTable WHERE entryTime BETWEEN '%08:00:00.%' AND '%12:00:00.%';

are not working. I'm getting the error message:

'data exception - invalid value for YYYY format token; Value: '%12:00:00.%' Format: 'YYYY-MM-DD HH24:MI:SS.FF6'

so is there a way, to solve this, so i can select a period of time no matter what date it is?

GriGrim
  • 2,891
  • 1
  • 19
  • 33
BlainTheMono
  • 49
  • 9
  • `%` is a `LIKE` wildcard, for character data types. – jarlh Feb 16 '17 at 11:29
  • Don't know about Exasol, but in Standard SQL this would be a `CAST(entryTime AS TIME) BETWEEN TIME '08:00:00' AND TIME '12:00:00'` – dnoeth Feb 16 '17 at 11:33
  • Thanks for the hint @dnoeth. Unfortunately Exasol does not support that. I'll find a work around. – BlainTheMono Feb 16 '17 at 12:06
  • I just checked that Exasol supports `EXTRACT`, so `EXTRACT(HOUR FROM entryTime) BETWEEN 8 and 11` returns everthing >=8:00 and <12:00 – dnoeth Feb 16 '17 at 12:47
  • INCREDIBLE! That's it! Thank you so much @dnoeth. and i was about to alter the table and stuff. spared me a lot of time! – BlainTheMono Feb 16 '17 at 13:32

2 Answers2

0

The SELECT statement for Exasol has to look like this:

SELECT * FROM MySCHEMA.MyTable WHERE EXTRACT(HOUR FROM entryTime ) BETWEEN 8 and 10;

this is the equivalent for the CAST(...) statement.

So another example where there is a second WHERE clause could look like this: 

SELECT * FROM MySCHEMA.MyTable WHERE myNames LIKE 'Sam%' AND EXTRACT(HOUR FROM entryTime ) BETWEEN 8 and 10;

the result would be a table of persons/things with myNames like Sam... (Samantha, Samuel, Samira....) and with an entryTime between 08:00:00.0000 and 10:59:59.9999 no matter what date it is.

BlainTheMono
  • 49
  • 9
0

Just the same solution like using EXTRACT() function with HOUR input parameter, you can use HOUR() function as follows

SELECT * FROM EXA_DBA_AUDIT_SQL WHERE HOUR(START_TIME) BETWEEN 8 and 9; -- up to 9:59:59
Eralper
  • 6,461
  • 2
  • 21
  • 27