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

user_17571613 0
Viewed 969

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?

timestamp datetime mysql sql
4 Answers

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

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';
user_160276640

If your date is in a string dd-mm-yyyy or d-m-y then you first need to convert the string into the same date format. Then reformat the resulting date into the format the server uses for DATE_SUB i.e %Y-%m-d. This way you are ensuring you are comparing a date to a date, so use CURDATE instead of NOW. The statement is as follows:

SELECT * FROM table WHERE date_format(str_to_date(str_date_searched, '%d-%m-%Y'), '%Y-%m-%d')  >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
user_99647050

You should use datetime or timestamp.

You can try to help yourself using:

SET SESSION SQL_MODE='ALLOW_INVALID_DATES'
user_175716830
Related