SELECT id,name from account where lastmodifieddate = yesterday
This will span from midnight to midnight, local time. If you run it at 11 PM, you won't include the current day's activity, only records between 47 and 23 hours before the time the script ran.
Will lastmodifieddate capture creation and modification of record, both?
Yes, last modified date is set to the created date on initial creation, so simply filtering on that field would be sufficient (but there's a better way).
What if I want to capture those records where only specific fields have changed? For example if an opportunity's stage has changed then I need that but if total amount has changed I don't?
You can't do that. There are tools to help you replicate data that's specific to whatever criteria you want, but there's no direct query for that.
In the general case, you should use the Replication API to make sure you don't miss any updates. This API covers gaps that you might miss if you're too strict with your queries, without giving you overlapping data if you're too liberal with your queries. The reason why we recommend this API is because it also takes into account in-flight requests (requests that are being processed by the server but cannot be queried yet). It's generally recommended that you use this API more frequently than daily, and is designed to be run as frequently as 12 times an hour (every five minutes).
If you want notifications about certain updates, such as a specific field changing, you need to keep track of those changes somehow. If you want to keep the logic on the local side, consider setting up field tracking on the object's field; you can then query the history for specific field changes. If you want to have salesforce notify you, set up workflow outbound message, which will let you specify trigger conditions, and you'll receive asynchronous notifications when your criteria is met.