[SOLVED] Exasol SQL TIMESTAMP query

Exasol SQL TIMESTAMP query

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?

Solution

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.