Much of the time, the usage of these prepositions is governed by custom and idiom rather that hard and fast rules. Here are some examples from my dialect, standard American English.
He is in school. This means that he is currently a student. He could be inside the school right now, or not.
He is at school. This means that he is on school property right now. He could be a student, a teacher, or a visitor.
As for weekend, I do things on or during the weekend.
Speakers of other dialects may have other answers.