I have always ignored timestamp data type while designing a database instead used datetime data type whenever I had to store date and time. Recently I read, what exactly timestamp data type is for and I think it is worth sharing.
So I’ll begin with the reason, why I never used timestamp!
My perception was that both data types (timestamp and datetime) store date and time in database and are same.
Now, why I am saying that we must also consider timestamp is because of following reasons:
- Timestamp data type stores date and time but in UTC format, not in current zone format as datetime do. And when you fetch data, timestamp again convert that into current zone time. So suppose you are in USA and getting data from server which has time zone of USA, then you will get the date and time according to USA time zone.
- Timestamp data type column always get updated automatically when its row gets updated. So it can be useful to track when a particular row was updated last time.
These are the two main reasons I am aware of (apart from range and storage) and it is worth considering timestamp data type while designing the database.
If you know any more reasons then feel free to mention in comments.
Please comment with your real name using good manners.