SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    change varchar timestamp to DATETIME type

    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. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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($timestamp04);
    $tmonth substr($timestamp5,3);
    $tday substr($timestamp,9);
    $ttime substr($timestamp,12);


    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. #4
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't need a PHP script to do this.

    I have simulated your case:
    Table structure for my sample data is:
    Code:
    CREATE TABLE `test_datetime` (
      `my_time` VARCHAR(255) NOT NULL COMMENT 'Test date/time column'
    );
    Then, convert the data into datetime format first.
    Code:
    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:
    Code:
    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.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bimalpoudel View Post
    You don't need a PHP script to do this.
    nor do you need that humoungous sql script

    the STR_TO_DATE function was designed just for these situations
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by autofocus; Aug 1, 2011 at 17:29. Reason: tested query

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by autofocus View Post
    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.
    i don't think so

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah it worked. i copy pasted exact query .

    yeah i have changed datatype to datetime


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •