Search code examples
sqlhadoopimpala

How to convert this String to Timestamp

i have a column with the Data like this "08.06.2017/10:20:46". Data Type is string. i want to convert it into timestamp. i tried CAST("08.06.2017/10:20:46" AS TIMESTAMP) but it doesn't work Can you please help me to convert it?? Thanks

Solution

  • For mysql, there is a function called STR_TO_DATE

    You should call it like this:

    STR_TO_DATE(string , format)

    Take a look at Format specifiers

    In your case I'd try with

    STR_TO_DATE('08.06.2017/10:20:46','%d.%m.%Y/%H:%i:%s')

    Edit: sorry about the mysql stuff, don't know if I'm supposed to remove it or not... Anyways, for impala, this could get you started:

    cast(unix_timestamp('08.06.2017/10:20:46', "dd.MM.yyyy/HH:mm:ss") as timestamp)

    The casting is because unix_timestamp function returns a bigint (take a look here for more information about the impala datetime functions)