Gossamer Forum
Home : Products : Gossamer Forum : Discussion :

Manually changing posting date

Quote Reply
Manually changing posting date
This may be a Jason question, but if anyone else knows...

I would like to change the date of a GF post. This would be a one time thing, but I need to do this for about 30 posts. I think that MySQLMan is the best tool for this. It appears that there are two dates in the POST table post_time and post_latest_reply. All of these will be original posts (without replys).

It appears that the dates are in some MySQL format (example):

post_time : 1029704387
post_latest_reply : 970295613

Is there a Excel formula/transformation I can use to translate so I know what to change it to?


Quote Reply
Re: [bretzke] Manually changing posting date In reply to
post_time is a unix timestamp - in perl, this is just a call to 'time()', and it is a timezone-independant representation of the time as seconds since Jan 1st, 1970, 0:00 GMT. post_latest_reply is the same thing, except that it is actually 2000000000 - time(), as MySQL will only properly handle an index on a value that goes _up_, not one that goes down, and the default and most common forum view is sorting with newest first - in other words descending - so the inverted value works properly.

In any event, you can use MySQL's 'UNIX_TIMESTAMP()' function to give you a unix time. A few usage examples:

mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP("2004-04-05"), UNIX_TIMESTAMP("2004-04-05 13:57");
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP("2004-04-05") | UNIX_TIMESTAMP("2004-04-05 13:57") |
| 1087416579 | 1081148400 | 1081198620 |

mysql> SELECT 2000000000 - UNIX_TIMESTAMP("2004-04-05 13:57");
| 2000000000 - UNIX_TIMESTAMP("2004-04-05 13:57") |
| 918801380 |

So, assuming April 5th, 2004, 1:57pm was the time you wanted, you could use the following query, replacing the red bits appropriately:

UPDATE gforum_Post
SET post_time = UNIX_TIMESTAMP("2004-04-05 13:57"), post_latest_reply = 2000000000 - UNIX_TIMESTAMP("2004-04-05 13:57")
WHERE post_id IN (123, 234, 345, 456, 567, 678, 789, 890, 901);

For other users who see this post, keep in mind that this will not work properly on anything other than root posts.

Jason Rhinelander
Gossamer Threads
Quote Reply
Re: [Jagerman] Manually changing posting date In reply to

Thanks Jason.