Help importing WordPress tables into vB tables?

[AFemaleProdigy]

I am working on merging/importing WordPress content into vB 4.1.8. Since the WordPress version is not one supported by Impex, I am having to import a lot of the content manually. Impex did allow me to import all of the user data and part of the WP posts data, but some of it would not transfer over.

In particular, I am attempting to copy over the WP post_date into vB dateline columns. I have noticed that the format for the dates is different. In vB, the dates look something like this "1311841061" and in WP they look like this "2011-07-14 04:35:36". Does anyone know how I can correctly convert that so it copies over in the correct format?

Thanks!

--------------- Added 1323235634 at 1323235634 ---------------

Also, does anyone have any advice for me as I am doing this merge manually without Impex for the first time? I haven't been able to find any mods, instructions, or anything that do this with vB 4.x. so I am winging it.

[Bob Meta]

I find this site to be very useful for converting dates to unix timestamps: http://www.epochconverter.com/. Hope this helps!

[LifesGreatestGift]

this may be a good guide for converting the table/column to the proper time format. (in phpmyadmin or something similar)

http://stackoverflow.com/questions/1281859/unix-timestamp-to-mysql-datetime

remember to backup before modifying.

[AFemaleProdigy]

Awesome! I will check out those links. Thanks!!

[AFemaleProdigy]

After much research and trial and error, I find myself a bit frustrated. I am on the right path, but can't get the queries quite right. Any assistance would be greatly appreciated.

I know I need to convert the database field type DATETIME to UNIXTIME. I figured out how to display the whole field in UNIXTIME with the query below, but it does not permanently convert or save it.

SELECT UNIX_TIMESTAMP(`post_date`) FROM `wp_posts`

I also tried this (total experiment... don't laugh ;)) with no luck...

ALTER TABLE `wp_posts` CHANGE `post_date` `post_date_old` int(11) NOT NULL
ALTER TABLE `wp_posts` ADD `post_date` UNIXTIME NOT NULL
UPDATE `wp_posts` SET `post_date`=FROM_DATETIME(post_date_old)

[kh99]

ALTER TABLE `wp_posts` CHANGE `post_date` `post_date_old` int(11) NOT NULL
ALTER TABLE `wp_posts` ADD `post_date` UNIXTIME NOT NULL
UPDATE `wp_posts` SET `post_date`=FROM_DATETIME(post_date_old)


I think I would have tried something like this - what happened when you tried?

[AFemaleProdigy]

I get this error...


Error

SQL query:

ALTER TABLE `wp_posts` CHANGE `post_date` `post_date_old` int( 11 ) NOT NULL ALTER TABLE `wp_posts` ADD `post_date` UNIXTIME NOT NULL UPDATE `wp_posts` SET `post_date` = FROM_DATETIME( post_date_old
)
MySQL said: https://vborg.vbsupport.ru/external/2012/01/58.png (http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html)
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `wp_posts` ADD `post_date` UNIXTIME NOT NULL
UPDATE `wp_posts` SET ' at line 2

[kh99]

Those are three separate queries, so they need to be executes separately. I think the error's saying it got confused when it reached the second query.

[AFemaleProdigy]

The first line executes properly. The second line didn't work.

[kh99]

Oh...oops, should have noticed that. I guess there is no "UNIXTIME" type, so you just want it to be int(10) .

[AFemaleProdigy]

Ok, so I now have the new column in int(10) type. Now the 3rd line does not work. I get the following error:


Error

SQL query:

UPDATE `wp_posts` SET `post_date` = FROM_DATETIME( post_date_old
)

MySQL said: https://vborg.vbsupport.ru/external/2012/01/58.png (http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html)
#1305 - FUNCTION bamatemp.FROM_DATETIME does not exist

[kh99]

I think the third one should be:

UPDATE `wp_posts` SET `post_date` = UNIX_TIMESTAMP( post_date_old
)

[AFemaleProdigy]

Okay... I got it now! It should be these queries...

ALTER TABLE `wp_posts` CHANGE `post_date` `post_date_old` datetime NOT NULL
ALTER TABLE `wp_posts` ADD `post_date` int(10) NOT NULL
UPDATE `wp_posts` SET `post_date`=UNIX_TIMESTAMP(post_date_old)

Thanks for all your help!! I really really appreciate it! :)