How to convert date string "yyyy-mm-ddThh:mm:ssZ" to timestamp-CodePudding

I have a CSV file with timestamps such as 2018-04-04T00:03:04Z. I created a table with a timestamp field and fill the table with the data from CSV, but an error is generated:

Incorrect datetime value

and my table isn't filled at all; doing a select query returns 0 rows.

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

Earlier 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'm filling the table with the Table Data Import Wizard.

Could anyone help with this?

CodePudding user response：

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')

CodePudding user response：

As I said at first you should update your dates to valid format:

UPDATE nf
SET
  date_delivery = DATE_FORMAT(
    STR_TO_DATE(date_delivery, '%Y-%m-%dT%H:%i:%sZ'), '%Y-%m-%d %H:%I'
  ),
  date_sale = DATE_FORMAT(
    STR_TO_DATE(date_sale, '%Y-%m-%dT%H:%i:%sZ'), '%Y-%m-%d %H:%I'
  )
WHERE 1;

After this step you need to modify your table:

ALTER TABLE nf
    MODIFY date_sale TIMESTAMP,
    MODIFY date_delivery TIMESTAMP;

And when you are inserting data don't forget to format it:

INSERT INTO nf (id, id_produt, id_category, date_sale, date_delivery)
VALUES
(31, 3, 4, STR_TO_DATE('2012-12-12T00:24Z', '%Y-%m-%dT%H:%i:%sZ'), STR_TO_DATE('2012-12-12T00:24:00Z', '%Y-%m-%dT%H:%iZ:%s')),
(44, 3, 4, STR_TO_DATE('2012-12-12T00:24Z', '%Y-%m-%dT%H:%i:%sZ'), STR_TO_DATE('2012-12-12T00:24:00Z', '%Y-%m-%dT%H:%iZ:%s'))

This way is better because you can operate your data as datetime format in sql.

For example if you have timestamp formats in DB than next query does not compare your string date_sale with the string '2013-12-12':

SELECT * FROM nf WHERE date_sale > '2013-12-12';

CodePudding user response：

You should use datetime or timestamp.

You can try to help yourself using:

SET SESSION SQL_MODE='ALLOW_INVALID_DATES'