Change varchar timestamp to DATETIME type

Databases
1

Hi

I started on already running web app today where it is required now to sort documents by datetime. the timestamp in table is “2011-Aug-01 11:10” and its datatype is varchar.

i noticed above today as i was not getting latest info from table. then i checked the type and it is varchar. i can’t change it straight away to timestamp/datetime as format is not correct. is there any kind of update query which i can run on column to make it right format then change it to datetime data type?

is it possible. ?

if it can’t be done in mysql i am thinking about doing following way.

  1. go through each row
  2. change time format from “2011-Aug-01 11:10” to “2011-08-01 11:12:13” format
  3. change mysql datatype to datetime [i think mysql won’t complain as time is in right format]

now what format is exactly similar to mysql datetime datatype. is date(‘c’) the right choice. ?

there are already 5000+ rows in it.

thanks in advance

2

create a new column with the correct data type. Do an update on your table to update the values from the incorrect column to the new column. use STR_TO_DATE to fix the date formatting.

3

Thanks for reply

after posting the question i started working on it. fixed using following code.

but from next time i will remember above option.


$dbh = new PDO('mysql:host=localhost;dbname=simpledb', 'simpledb', 'simpledb');
    foreach($dbh->query('SELECT documentuid, timestamp from documents') as $row) {

    $timenew = updateTimeFormat($row['timestamp']);

    

    $stmt = $dbh->prepare("update documents set timestamp = ? where documentuid = ?");
    $stmt->bindParam(1, $timenew);
    $stmt->bindParam(2, $row['documentuid']);
    $stmt->execute();

    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
function updateTimeFormat($timestamp){
   
$tyear = substr($timestamp, 0, 4);
$tmonth = substr($timestamp, 5,3);
$tday = substr($timestamp,9, 2 );
$ttime = substr($timestamp,12, 5 );


switch($tmonth){
    case 'Jan':
        $tmonth = '01';
        break;
    case 'Feb':
        $tmonth = '02';
        break;
    case 'Mar':
        $tmonth = '03';
        break;
    case 'Apr':
        $tmonth = '04';
        break;
    case 'May':
        $tmonth = '05';
        break;
    case 'Jun':
        $tmonth = '06';
        break;
    case 'Jul':
        $tmonth = '07';
        break;
    case 'Aug':
        $tmonth = '08';
        break;
    case 'Sep':
        $tmonth = '09';
        break;
    case 'Oct':
        $tmonth = '10';
        break;
    case 'Nov':
        $tmonth = '11';
        break;
    case 'Dec':
        $tmonth = '12';
        break;
}

$timenew = $tyear . '-' . $tmonth . '-' . $tday . ' ' . $ttime . ':00';
    return $timenew;
4

You don’t need a PHP script to do this.

I have simulated your case:
Table structure for my sample data is:


CREATE TABLE `test_datetime` (
  `my_time` VARCHAR(255) NOT NULL COMMENT 'Test date/time column'
);

Then, convert the data into datetime format first.


UPDATE my_time SET my_time = UPPER(my_time); # Converts month names to upper case
UPDATE test_datetime SET my_time = REPLACE(my_time, 'JAN', '01');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'FEB', '02');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'MAR', '03');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'APR', '04');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'MAY', '05');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'JUN', '06');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'JUL', '07');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'AUG', '08');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'SEP', '09');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'OCT', '10');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'NOV', '11');
UPDATE test_datetime SET my_time = REPLACE(my_time, 'DEC', '12');

There are only 12 queries to convert the month names.

Finally, modify the field type:


ALTER TABLE `test_datetime`
CHANGE `my_time` `my_time` DATETIME NOT NULL COMMENT 'Test date/time column';

Be sure to change the script that feeds data into the database.
The input should be in datetime format only.
Any other forms of entry are likely to be converted to 0000-00-00 00:00:00

But take your full database backup first, in case you will ruin the data.

5

nor do you need that humoungous sql script

the STR_TO_DATE function was designed just for these situations

6

Thanks for replies

now i know how to do it easy way

my question : As i had previous format as “2011-Aug-01 11:10” without seconds in it what should be the query to add seconds in timestamp?
is this one right
STR_TO_DATE(“2011-Aug-01 11:10”, ‘%Y-%M-%d %h:%i:00’ )

thanks

EDIT: tested the query on test table as “update user set created = STR_TO_DATE(created, ‘%Y-%M-%d %h:%i:00’ )” and it worked.

thanks

7

i don’t think so :slight_smile:

you originally said your VARCHAR column had data in it like 2011-Aug-01 11:10

the %M format code is for the full month name

the 3-char month abbreviation is %b

if %M actually did work, you lucked out on an undocumented “feature”

besides, unless you’ve also changed the datatype of the column, it’s still a VARCHAR and i’m not sure what good you’ve done switching one string for another

8

yeah it worked. i copy pasted exact query .

yeah i have changed datatype to datetime