Converting varchar to datetime in logstash jdbc input?

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.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.