Timezones in MySQL

Question

I'm playing with timezones in MySQL.

I need to assign people to timezones, and so I looked in mysql.time_zone_data.

Australia seems to have 5 independent timezones [1], so why does mysql.time_zone_data have 23 options?

Australia/ACT
Australia/Adelaide
Australia/Brisbane
Australia/Broken_Hill
Australia/Canberra
Australia/Currie
Australia/Darwin
Australia/Eucla
Australia/Hobart
Australia/LHI
Australia/Lindeman
Australia/Lord_Howe
Australia/Melbourne
Australia/NSW
Australia/North
Australia/Perth
Australia/Queensland
Australia/South
Australia/Sydney
Australia/Tasmania
Australia/Victoria
Australia/West
Australia/Yancowinna

[1] http://www.timetemperature.com/australia/australia_time_zones.shtml

Answer 1

why does mysql.time_zone_data have 23 options?

Usually because each of those mini-regions has historically had different time rules. They may be using the same timezones now, but if you want to reliably convert a time that might be in the past, you'll need to know which exact set of rules the locale has not just now, but for as far back in history as timezones have been stably legislated.

This is what makes timezone databases so absurdly large. Timezones are a horror.

Answer 2

The same reason, why there are several options in your OS, ...

Not everyone knows in which timezone his town is. So there are some huge cities (which are in the same zone) for selection. So you can look for a city near your location and automatically select the correct timezone.

For example: Berlin and Munich are in the same zone as well as Canberra and Sydney