Question 394675 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Modify string from one column before inserting into another

I have imported a CSV file into mysql 5.7 and need to modify the date so that I can manage it later.

The imported data is currently in a column called localDate which needs to be modified. It looks like this:

Mon Jun 24 2024 17:12:18 GMT-0400 (Eastern Daylight Time)

I already have the PHP code to put the date how I need it:

// extract date from string.
  $original_date =  substr($row['localDate'], 3,12);
// convert to yyyy-mm-dd
  $local_date = date("Y-m-d", strtotime($original_date));

2024-06-24

Basically I just need to know how to copy from the localDate column to the new local_date column while updating the date using my variable name $local_date

Tags:

2

Answers


  1. 0

    You’re probably on the right track. But i don’t think there is any need of substr function. PHP can handle this in better way.

    $original_date = "Mon Jun 24 2024 17:12:18 GMT-0400 (Eastern Daylight Time)";
$dateString = preg_replace('/s+GMT.*$/', '', $original_date); // it will remove timezone related information from the string
$local_date = date('Y-m-d',strtotime($dateString));

    Passing the string containing timezone will throw exception. Instead let you extract the datetime information from it.

    Using substr is not a good way, What if in feature you will have more chars then 12? At that time your logic will failed to parse the datetime.

    Login or Signup to reply.
  2. 0

    As mentioned in the comments by @Barmar and me, doing updates in a loop is generally always a bad idea since every update has to be shoved across the wire to your database. Even on a small table this will be a slow solution. Instead, adding this new column and running a single update statement will be much faster.

    CREATE TABLE imported_csv (id int, localDate VARCHAR(100));
INSERT INTO imported_csv VALUES (1, 'Mon Jun 24 2024 17:12:18 GMT-0400 (Eastern Daylight Time)'), (2, 'Tue Jun 25 2024 13:22:44 GMT-0400 (Eastern Daylight Time)');

ALTER TABLE imported_csv ADD COLUMN local_date DATE;

/*One update statement to update all records*/
UPDATE imported_csv
SET local_date = STR_TO_DATE(SUBSTRING(localDate, 5, 11), "%M %d %Y");

SELECT * FROM imported_csv;

    +-----------------------------------------------------------+------------+
|                         localDate                         | local_date |
+-----------------------------------------------------------+------------+
| Mon Jun 24 2024 17:12:18 GMT-0400 (Eastern Daylight Time) | 2024-06-24 |
| Tue Jun 25 2024 13:22:44 GMT-0400 (Eastern Daylight Time) | 2024-06-25 |
+-----------------------------------------------------------+------------+

    https://dbfiddle.uk/XlLjW0fP

    A good rule of thumb is that if you find yourself solving a problem by running sql (any sql) in a loop, there is likely a better set-based way of solving it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search