This depends on a whole lot of factors. The unlock time differs based on the game, the region you're in and even who you are.
For example, Deus Ex: Human Revolution unlocks August 23, 2011 in the US, August 25, 2011 in Australia, August 26, 2011 in Europe and September 8, 2011 in Japan. This will also mean that it is usually midnight at those dates in those regions.
Also notable, Portal 2 unlocked earlier for some users than others, namely those who got a Golden Potato. (Approximately 9 hours earlier.)
However, you can always just check the store page on Steam. It will have a bar showing when it will unlock. For example, for me the page for Deus Ex shows this:
That's midnight on Thursday in my timezone.
I know of only one way that this can happen in windows, and has nothing to do with Steam other than their not-so-great decision to install all content into Program Files.
If you are on Windows 7 or Windows Vista, and you have UAC set to ON, then change it later to OFF, this can happen.
UAC does thing evil thing where if a program tries to write to a "protected" area like Program Files, EVEN IF YOU ARE ADMINISTRATOR, when you get a UAC prompt and give permission, it actually writes any files that would normally go into that directory into a "virtualized" directory somewhere else.
If you turn off UAC, that will no longer happen, and as an awesome bonus, it will no longer know about the files that it virtualized.
So in your case, if you had UAC on, installed a ton of games, then later got sick of UAC and turned it off, this would happen as you described.
See the second paragraph in "Features" in the article on User Account Control.
If you have done this and turn on UAC again, your content will come back, but be a TOTAL mess because if you've downloaded more stuff with UAC off, then that will be invisible when you turn UAC on, and vice versa.
this drove me a little crazy once before I realized what was happening.
i'm curious to know if this is your issue.
Best Answer
So it turns out I was an idiot and tried to sign in using a really old account I haven't used in almost three years now so don't make the mistake I did!