[RESOLVED] How to set current timestamp on field when importing using phpmyadmin
Results 1 to 5 of 5

Thread: [RESOLVED] How to set current timestamp on field when importing using phpmyadmin

  1. #1
    Shamola
    Join Date
    Dec 2007
    Posts
    43

    resolved [RESOLVED] How to set current timestamp on field when importing using phpmyadmin

    Hello,

    I am trying to import the following into a table:
    1,tim@google.com,tim,clements,www.test.com,CURRENT_TIMESTAMP
    2,tom@google.com,tom,clements,www.tom.com,CURRENT_TIMESTAMP
    3,jon@google.com,terry,clements,www.jon.com,CURRENT_TIMESTAMP

    I was hoping the last field would be set to the current time and date but it is set as 0000-00-00 00:00:00.

    It is set as a timestamp field with the default set to CURRENT_TIMESTAMP

    Is that possible?
    Thanks,
    Tim.

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    The string "CURRENT_TIMESTAMP" is far different than using CURRENT_TIMESTAMP in a SQL query. You must be doing the former.

    One possibility would be to omit that column altogether from the import data; that way, MySQL will simply utilize the default value you've defined rather than trying to interpret the string "CURRENT_TIMESTAMP" as a timestamp.

  3. #3
    Shamola
    Join Date
    Dec 2007
    Posts
    43
    Thanks for the suggestion.

    I had tried this but I get the following message:

    "Invalid column count in CSV input on line 1"

    I tried setting the value to blank and 0 and that doesn't error but just sets the field to 0000-00-00 00:00:00.

    Interesting I tried setting it the timestamp to "2013-07-27 13:24:42" and that resulted in "0000-00-00 00:00:00" too. When I insert a field manually and leave that field blank it gets created correctly so I am quite sure the field is created correctly.

    Thanks,
    Tim.

  4. #4
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    If you remove the values, did you also remember to remove the corresponding column name from the column list? How exactly is it that you're importing this data into your SQL database?

  5. #5
    Shamola
    Join Date
    Dec 2007
    Posts
    43
    I was using the "Import" tab in phpMyAdmin, then browsing for the CSV file which was in the following format:

    1,tim@google.com,tim,clements,www.test.com,CURRENT_TIMESTAMP
    2,tom@google.com,tom,clements,www.tom.com,CURRENT_TIMESTAMP
    3,jon@google.com,terry,clements,www.jon.com,CURRENT_TIMESTAMP

    That resulted in the timestamp being set as 0000-00-00 00:00:00.

    I then tried:
    1,tim@google.com,tim,clements,www.test.com
    2,tom@google.com,tom,clements,www.tom.com
    3,jon@google.com,terry,clements,www.jon.com

    The above resulted in the error "Invalid column count in CSV input on line 1".

    Your last question prompted me to add column names and then specify on the import tab which columns I wanted to enter:

    contribid,email,name,surname,url
    1,tim@google.com,tim,clements,www.test.com
    2,tom@google.com,tom,clements,www.tom.com
    3,jon@google.com,terry,clements,www.jon.com

    That did the trick!
    Thanks!
    Tim.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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