1 Topic by Rei (edited by Rei 2007-09-13 17:16)

  • Rei
  • Member
  • Offline
  • Registered: 2007-09-13
  • Posts: 15

Topic: [Solved] changing timestamp, mysql query command needed

Hi,
I moved the forum from a server which is having 13hours difference from my current server (my local time too).
Now all the date/time of the existing posts being shown 13 hours behind the actual date/time posted. e.g. the post showing 2007-09-12 10:00:00 currently, while the actual date/time should be 2007-09-12 23:00:00.

Anybody know of any MySQL query command that i can use to change the timestamp of all posts at 1 go?

  • Registered: 2006-08-16
  • Posts: 92

Re: [Solved] changing timestamp, mysql query command needed

It's not in mysql, punbb use timestamps. Timestamp is a format popularized by the *NIX operating systems that stores the amount of time that has passed since January 1, 1970.

You have too set timezone go to the punbb admin panel -> options -> Server timezone, and change it...

Post hoc ergo propter hoc

Tjalve's Website

3 Reply by Rei

  • Rei
  • Member
  • Offline
  • Registered: 2007-09-13
  • Posts: 15

Re: [Solved] changing timestamp, mysql query command needed

Hmm... i try to explain better...

My current server is in local time zone now, thus the current time (live) appear on the board is current and accurate, time zone difference is set at 0.

If i set the time zone to 13 hours difference, yes, all the old posts would have correct time/date appears on the board, but the current time (live) and all the new posts would be off by 13hours.

That's why i need to change the "posted" time being recorded in the mysql database instead of using the time zone feature.

I hope i make it clearer now. Thanks for your reply though.

  • Registered: 2006-08-16
  • Posts: 92

Re: [Solved] changing timestamp, mysql query command needed

ah, stupid me...

UPDATE lala SET column_name = column_name + 13 * 60 * 60;
Only prob is that you have too find and run that on all the timestamps tongue

Thats the only way i know off, hope that helped more than my first post smile

Post hoc ergo propter hoc

Tjalve's Website

5 Reply by Rei

  • Rei
  • Member
  • Offline
  • Registered: 2007-09-13
  • Posts: 15

Re: [Solved] changing timestamp, mysql query command needed

no.. it's because i never make it clear earlier on. smile

for e.g. in "topics" table, i need to update "posted" field and "last_post" field.
How should i type the command?

I'm using phpMyAdmin.

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: [Solved] changing timestamp, mysql query command needed

UPDATE topics SET posted = posted + (13 * 60 * 60), last_post = last_post + (13 * 60 * 60) WHERE posted + (13 * 60 * 60) <= UNIX_TIMESTAMP()

That should work.

7 Reply by Rei

  • Rei
  • Member
  • Offline
  • Registered: 2007-09-13
  • Posts: 15

Re: [Solved] changing timestamp, mysql query command needed

Thank you! I try it out now...

8 Reply by Rei

  • Rei
  • Member
  • Offline
  • Registered: 2007-09-13
  • Posts: 15

Re: [Solved] changing timestamp, mysql query command needed

It works! smile