WarcraftRealms.com

to improve activity tracking info.

Currently the only way to see which realms could use more census tracking during a certain hour of the day is to go to the activity page and visually scan the chart

There ought to be a better way!

You know that is a great suggestion!

here is an idea I am working on.

//warcraftrealms.com/ mostwanted4bh.php

The number in the top right is the current UTC hour... {this is just for debug at this time}

The numbers in the table are the realms with the least census runs during the current hour (for the last 30 days)

Caveat : the data is some what bogus.. as I still need to adjust the time slot on the realms for their actual time zones.
This is just an example display

edit: link broken.. multiple bots were hitting that not ready page.

The EU data is now correct
The multi-zone US/Oceanic realms need more work.

Actually EU data is correct only 1/2 of the year..
I think I have the US/Oceanic realms up to the same status as the EU zones.

But there are still issues:
Need to figure out how to get my queries to work with DST/Summer time vs standard time. (something Rollie never considered nor made provisions to handle.)
Need to figure out how to join table data were one of the two tables doesn't have any data for the selected time period. i.e the '0' entries. sigh

Quirks seen:
on some refreshes The full data doesn't display.. i.e. column will have less then the 20 item cut off.
and what we also see in the standard most wanted page...
since we cut off at 20 items,
where there are more realms at the highest number displayed a random list will get displayed,
depending on which queries finish first.

I sure know how to open a can of worms.
Did you know that computer programs that need to track Time zone data.. have to know about
580+ different locations that have a time zone
3000+ different unique Timezones
30,000+ different time zone transition times.
The later two are due to countries/locations changing their minds as to what shift if any should happen and when during the year they should happen.

The name was changed to protect the guilty. There are no innocents.

I have a fairly high confidence that the data displayed is valid.
Zero count hours now are tracked.
Standard vs Daylight/Summer time is correctly handled.

But I am thinking we will need to cache the results (as is done with the census pages) so only one set of calculations are run per hour.
This page takes a lot of resources to get the data.

I have to admit that at this time depending on the time of day.
there are so many realms with no current census runs during the current hour we just have zeros across the board.

Had a lot of problems due to politicians who can't stop fiddling with time tracking.

The database engine MySQL has some issues with time tracking and lots of programmers say work around the problem by doing all time crunching outside of the database and then insert the data as UTC.
(no time zone change for DST)

As it is of the realms we track, two of the seven times zones resisted DST testing due to issues that the timezone database maintainers need to fix..
currently they don't recognize Brazil's BRT and Australia's AEST as valid abbreviations

Well my high confidence just got shot down.
The compare with nothing feature to get the ZERO counts is not quite right..
I may have lost validity either with my optimizations or with the DB software optimizing.
or maybe it is working... but my check data is cached and out of date.. hmmm.

I guess I need to do some more raw database queries instead of accessing the data via the web site pages.

sigh... deluded by bad data.

For now this page addition idea is being tossed into the scrap heap.

In the early days of Warcraftrealms.com Rollie started the activity tracking but since the code wasn't complete it allowed over 30,000 bad records into the table.. null data for everything except the timestamp.

30k records sound bad... but it is a tiny number compared to the current 11 million activity records.

Because the bad records were giving me false data, I removed the bad records.
Now that I don't have false data.. I can't strictly within the database get a match for missing census runs during the current hour and last 30 days.

I am probably going to have to solve the problem using the same solution as the activity pages.
But it gets messier..
Instead of 1 realm 2 factions 24 hours...
It will be 1 hour 2 factions and some unknown number of realms ( up to 470 or so)
And since I will have to do all the Time Zone work instead of letting the DB do the work..
it gets more complex.
Edit: and like the activity pages this data will need to be cached to avoid performance impacts.