Database export for posts/threads from last month?

[dutchbb]

Let's say I want to do a database export for the posts and threads, but I only want to export posts/threads of the last month, is this possible and how would I do this?

Thank you

[Eikinskjaldi]

both the post and thread tables contain a dateline field which contains the time of creation in 'seconds since the epoch". You can convert to and from a dateline and an actual date using the from_unixtime and unix_timestamp functions. However, what you really want to know is what is the unix time for 1 month ago.

If we assume naively that a month has 30 days, then the number of seconds in a month is 60x 60 x 24 x30 = 2592000

We can find out the time now by using the now function

So, one version which will do what you want to do is

"select * from post where unix_timestamp(now()) - dateline <= 2592000

[dutchbb]

Thank you, however how would I do the export to an sql file from that query?

[Eikinskjaldi]

Originally Posted by dutchbb

Thank you, however how would I do the export to an sql file from that query?

What do you mean by "sql file"?

You can export it in tab delimted format in two ways.
WAY 1
1) Write the query into a file, lets call it latestposts.sql (make sure you terminate the line with a semi colon)
2) invoke mysql redictecting input from the above script and redirecting the output to a result file

mysql -uusername -ppassword mydatabase < latestsposts.sql > results.txt

WAY 2
use the into outfile syntax directly from within mysql
select * from post where unix_timestamp(now()) - dateline <= 2592000 into outfile '/path/to/directory/results.txt'

Alternatively, capture it in vbulletin/php

PHP Code:

$db->query_read("select * from post where unix_timestamp(now()) - dateline <= 2592000");
while ($rows = $db->fetchrows()) {
     //do something with the data
} 


[dutchbb]

Originally Posted by Eikinskjaldi

What do you mean by "sql file"?

When I do a database backup in vbulletin admincp I get a sql file. I can import that file in another database. So what I want is to export the posts in the last month from one forum and import them in another.

I have read your post 10 times but Im sorry it just doesn't make any sense to me, I'm really a noob in this field. I'm going to ask my server admin if he can do what you are saying.

Thank you