"It's time for the weather report" is the correct statement, "it's time at the weather report" is not used because it's incorrect. The word at is used to express a location or the time when an event happens. For example, "She lives at his house" or "We go to sleep at 10 o'clock." Yes, the phrase can be used when having a conversation, but as an individual phrase, not as a conversation topic. For example, "Oh it's already time for the weather report" "Let's watch it."

Only the first is grammatically correct ("for" would indicate that you are going to talk about the weather report); the second scenario doesn't make any sense. The only time in which I would hear "it's time for the weather REPORT" would likely be on tv during the news.