Changing date of many posts?

[Maxwell the Cat]

Is there a way to change the date/time of a lot of posts at once?

[Jake Bunce]

You would have to directly query the post table in the database. There is no front end for this.

[Maxwell the Cat]

You would have to directly query the post table in the database. There is no front end for this.

I figured I would have to edit the SQL tables directly. What would the syntax for this look like?

[Lats]

What do you need to do exactly?

[Maxwell the Cat]

What do you need to do exactly?

Basically, I set the date wrong on my forums. I caught it after a few days but now all the posts have the wrong date on them. I want to basically add three weeks to the date of all posts on my forums.

I know I need to execute a SQL query to do this. Hopefully someone can help me out with the syntax for this.

[Lats]

The query would be...

UPDATE
   post
SET
   dateline = dateline + 1814400

[Maxwell the Cat]

Is the date field a number in seconds?

[Steve Machol]

The dateline column in the post table is in Unix timestamp format, which is the number of seconds since midnight Jan. 1, 1970. You can use this online tool to convert Unix Timestamps to date and time:

http://www.onlineconversion.com/unix_time.htm

Just remember to enter in the GMT values for the server and not the date and time in your specific timezone.

[josef]

The query would be...

UPDATE
   post
SET
   dateline = dateline + 1814400

how to do that for specific posts based on date

means to change posts dated on xx/xx/xxxx to xx/xx/xxxx

??

[Lats]

You would add a where clause such as...

WHERE 
 post.dateline > 1199145600
AND
 post.dateline < 1230768000

[josef]

i put

UPDATE
post
WHERE
post.dateline > 1231977600
AND
post.dateline < 1232063999
SET
dateline = dateline - 1231891200


but comes with error in syntax

[Lats]

Try this...

UPDATE
   post
SET
   dateline = dateline - 1231891200
WHERE
   post.dateline > 1231977600
AND
   post.dateline < 1232063999

[josef]

Try this...

UPDATE
   post
SET
   dateline = dateline - 1231891200
WHERE
   post.dateline > 1231977600
AND
   post.dateline < 1232063999

ok thats good

but is

dateline = dateline - 1231891200

correct to return one day for all posts ???

or what is the correct equation ??

[bigwater]

SET dateline = dateline - 1231891200 is never going to accomplish what you're trying to do. 1231891200 is 14258 days earlier than dateline, and as of now is approximately "the beggining of time" as far as unix goes... 39.06 years ago, or early January 1970. So you'd basically be subtracting 39 years from the current date.

If your need to adjust your timestamp back by exactly one day, it would be SET dateline - 86400. 86400 is the number of seconds in one day. If you need to go back several days, weeks, whatever... multiply 86400 x the number of days you want to go back and use that number.

[josef]

what i need only to change the date ....not the time

i need to keep the time as it is and update only the date