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.