How to convert string field to timestamp

I have a CSV file with a timestamp field (2018-04-04T00:03:04Z).
I tried creating a table with a timestamp field and fill the table with the CSV, but an error is generated:

Incorrect datetime value

Then my table isn’t filled at all. A query returns 0 lines.

I’m filling the table with the Table Data Import Wizard.

I created the table with the date field as a string and everything goes right, but now I need to convert this field in a timestamp field anyway in order to manipulate the date.

I tried to use str_to_date(date_sale, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") but it returned null values.

My current table:

create table `nf`(
    `id` int(10) not null
    , `id_produt` varchar(100) not null 
    , `id_category` int(10) not null
    , `date_sale` varchar(20)
    , `date_delivery` varchar(20) 
, primary key (`id`)

Could anyone help with this?

>Solution :

If converting to local time is not necessary you could just use str_to_time with correct format:

select str_to_date('2018-04-04T00:03:04Z', '%Y-%m-%dT%H:%i:%sZ')
-- 2018-04-04 00:03:04

If the timezone conversion is required then you must make sure that timezone related tables are setup correctly then use convert_tz function:

select convert_tz(str_to_date('2018-04-04T00:03:04Z', '%Y-%m-%dT%H:%i:%sZ'), 'zulu', 'system')

Leave a Reply