Gossamer Forum
Home : Products : Gossamer Forum : Discussion :

Re: [bretzke] Manually changing posting date

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:

Code:
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:

Code:
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
jason@gossamer-threads.com
Subject Author Views Date
Thread Manually changing posting date bretzke 2732 Jun 16, 2004, 6:48 AM
Thread Re: [bretzke] Manually changing posting date
Jagerman 2652 Jun 16, 2004, 1:21 PM
Post Re: [Jagerman] Manually changing posting date
bretzke 2651 Jun 16, 2004, 1:59 PM