Dev Notes

Various Cheat Sheets and Resources by David Egan/Carawebs.

Manipulating date format MySQL

WordPress, mySQL
David Egan

Importing an “alien” date column into a WordPress format - necessary because the old date column contained data in the “VARCHAR” format. WordPress requires date to be in the “DATE” format.

Delete unnecessary columns.

Rename columns to suit WordPress schema.

Rename the old date column to “OLDDATE”.

Create a new column with the data-type “DATE”, named “post_date”:

  • Open phpMyAdmin
  • Select “Structure”
  • Add a new Column
  • Name appropriately (e.g. WP date = “post_date”)
  • Select Type “DATE”

Run the following SQL command:

UPDATE tablename SET post_date = str_to_date( OLDDATE, '%d/%m/%Y')

Where old dates are in string data-format, dd/mm/yyyy.

Manipulate Column Data

Copy data from ColumnA Data to ColumnB:

UPDATE tablename SET columnB = columnA

Delete table data: TRUNCATE tablename

Import Table Data, phpMyAdmin

Organise data on a test table, then rename before exporting:

  • Select table
  • Select operations
  • Select “rename table to”
  • Give the table the same name as the target table

Click “Export” in top level menu.

Open the target DB, select the target table. Click import and select the recently exported DB.

comments powered by Disqus