String to Datetime cast in Where clause Mysql and Impala

Question

I have a mysql table which have datetime stored as string. I want to get the rows from the table with the time range provided.

My table looks like:

datetime                   plugin_name
2017-11-12T00:44:48.530000  testng
2017-11-12T00:44:48.529000  testng
2017-11-12T00:46:22.668000  testng
2017-11-12T00:46:22.666000  testng
2017-11-12T01:00:16.338000  ntf-test

Here column datetime has type as String.I have tried with this query

select `datetime`, plugin_name  
from plugin_names 
where datetime(`datetime`) > '2017-06-29T06:16:49.426000' 
and DATETIME(`datetime`) < '2017-06-29T06:16:49.314000'

But I am getting error as:

Encountered: DATETIME
Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER

I tried following this: MySQL compare DATE string with string from DATETIME field

I am using Impala for distributed Mysql database Anyone has idea how to fix this?

Is this query to be executed in MySQL? or Impala? please add that information as tags. For Impala use this ref: [**TIMESTAMP Data Type**](http://www.cloudera.com/documentation/cdh/5-1-x/Impala/Installing-and-Using-Impala/ciiu_timestamp.html#timestamp) — Paul Maxwell, Nov 12 '17 at 04:51
By the way `datetime` as a column name is going to be very frustrating, can't you call it by the function it fulfills? (e.g. created_time) instead of the data type it holds? — Paul Maxwell, Nov 12 '17 at 04:54

Paul Maxwell · Answer 1 · 2017-11-12T04:56:44.087

For MySQL

The example you are following is for a date, not a complete timestamp. For those strings you will need str_to_date and you will need to memorize '%Y-%m-%dT%H:%i:%s.%f' because every time you run a query against that table you are going to need it. A far better solution would be to use a REAL datetime column. sqlfiddle demo

select
      `datetime`
    , str_to_date(`datetime`,'%Y-%m-%dT%H:%i:%s.%f')
    , plugin_name  
from plugin_names 
where str_to_date(`datetime`,'%Y-%m-%dT%H:%i:%s.%f') > '2017-10-29T06:16:49.426000' 
and str_to_date(`datetime`,'%Y-%m-%dT%H:%i:%s.%f') < '2017-12-29T06:16:49.314000'

Just wait until some enters in invalid date into that column.... e.g.

'2O17-02-30T00:44:48.53000'

and it isn't what you think it is

String to Datetime cast in Where clause Mysql and Impala

1 Answers1