XF 2.3 After import from vBulletin to Xenforo all posting timestamps seemed to be one hour off

[Grover]

After my vBulletin 3.8.11 board was imported to a (test) XenForo board, I noticed that all timestamps on every posting seems to be one hour off.

At least, when I looked at those postings whilst logged in under my administrator account. Investigating it further, it seemed that inside my own Account preferences my Time Zone was set to: "(UTC) Dublin, Edinburgh, Lisbon, London", but my board is located in The Netherlands, so I need the Time Zone for every user to be on: "(UTC+01:00) Central European Time".

Surprised that my own administrator account was set to: "(UTC) Dublin, Edinburgh, Lisbon, London", I looked up some users via the ACP and some of them had their Time zone set to UTC and some had their Time zone set to (UTC+01:00)".

(I can't check if my users had these Time zones set like that on the source vBulletin board, because I am confronted with serious issues at the moment accessing my live vBulletin board).

But what surprises me a bit (as a newbie to the XF Admin Control Panel) that there seems to be no option to (re)set the Time zone preferences for all users with 1 click. But maybe specific cases like these are better left to a SQL query?

Talking about that, I was used inside vBulletin's ACP to run SQL queries, but also that seems to be not available in Xenforo? Why is that?

• What I would like to know is, how can I check (with what SQL query) how many (and which) users have (UTC) set and how many (and which) users have (UTC+01:00) set?
• And if there are too many users with (UTC) set, how (with what SQL query), can I set every users to have (UTC +01:00) in their Account preferences?

And am I correct (novice webmaster) that I need to run these SQL queries through PhpMyAdmin instead of in Xenforo's ACP? Since there seems to be no possibility to run a SQL query via de ACP. And... there is also no option to search for these User Preferences via 'Search for Users' either?

Thanks for any feedback.

 

[Alpha1]

Yes, you run queries through phpmyadmin.
For things like user rebuilds use CLI commands trough SSH/CLI software like Putty or SecureCRT.

Time zone should be imported correctly from vbulletin. It seems to me that this is something that needs to be addressed as part of the import project.

 

[Grover]

Thanks to this antique post from @Jake Bunch, I was able to run these queries through PhpMyAdmin, with these values:
-
<option value="Europe/London">(UTC) Dublin, Edinburgh, Lisbon, London</option>
<option value="Europe/Amsterdam">(UTC+01:00) Central European Time</option>

So:

---

SELECT COUNT(*)
FROM xf_user
WHERE timezone = 'Europe/London'

Which resulted in 276 users who apparently have their time zone set to UTC

---

SELECT COUNT(*)
FROM xf_user
WHERE timezone = 'Europe/Amsterdam';

Which resulted in 40133 users who apparently have their time zone set to UTC+01:00.

Interesting findings

 

[Grover]

I (novice Webmaster) was also able to get a list from all these 276 users who apparently have their time zone set to UTC.

Doing this:

SELECT user_id, username
FROM xf_user
WHERE timezone = 'Europe/London';

And then export this list to an CSV file to get it into Excel.

 

[Grover]

Thanks to this antique post from @Jake Bunch,

And that's why I (still) love forums, compared to social media platforms like Facebook. You can't find anything back easily (or at all) in endless comment-threads on Facebook for example. Here on www.xenforo.com it took me a few secs to find that helpful post by @Jake Bunch.