I've got my db table which has a column named
time, type of (
varchar). Value of the column looks something like
I tried casting it from
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
So these are the statements which I've tried within my
jdbc input plugin in
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
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.