Yesterday in a format like ‘20140101’

FROM_UNIXTIME(UNIX_TIMESTAMP()-1*24*60*60,’YYYYMMdd’)

Versus the dumb version:

10000*year(from_unixtime(unix_timestamp())) + 100*month(from_unixtime(unix_timestamp())) + 1*day(from_unixtime(unix_timestamp()))

As you can see from the date functions in Hive documentation, where M actually stands for month in Hive where it stands for minute in POSIX..