I've got my db table which has a column named time, type of (varchar). Value of the column looks something like 2016-09-02 00:00:01.
I tried casting it from varchar to datetime along with the select statement, assuming that the time field would be converted into a datetime field so that I can use it to store within sql_last_value.
So these are the statements which I've tried within my jdbc input plugin in logstash:
Using CAST:
statement => "SELECT * from testnew where CAST(time AS DATETIME) between '2016-09-01 00:00:00' and '2016-09-03 00:00:04' and time > :sql_last_value"
Using STR_TO_DATE function:
statement => "SELECT * FROM testnew where STR_TO_DATE(time, '%Y-%m-%d %H:%m:%s') > :sql_last_value and STR_TO_DATE(time, '%Y-%m-%d %H:%m:%s') between STR_TO_DATE('2016-09-01 00:00:00', '%Y-%m-%d %H:%m:%s') and STR_TO_DATE('2016-09-03 00:00:04', '%Y-%m-%d %H:%m:%s')"
The date range is working fine. But then what I expected was that, sql_last_value would store the timestamp value from the column time. Where as, it's storing the machine timestamp which makes me feel the above statements aren't working in logstash.
What I want is to store the converted value (which I assume the varchar is being converted to datetime) which I'm trying to do through the statements above and store it into sql_last_value. Is this possible?
Where am I going wrong? Any help could be appreciated.