What would be the best choice among DATETIME and TIMESTAMP for storing the below data(to the right side, i have written as per my understanding):
Now I am confuse between Datetime / Timestamp.
1. Since Datetime doesn't' store the timezone, how to store the activities time in DB?
my assumption : store the date time in any specific format in DB(suppose i convert the date time in IST and stored that converted time in DB) and during the time of retrieval it will be the responsibility of front end application to convert that IST to user specific timezone.
2. Using Timestamp instead of Datetime
Yes there will be benefit in terms of space as Timestamp require 4bytes whereas Datetime require 8bytes. Drawback is that it has year limitation - 1970 - 2038.
It's true that those MySQL data types don't contain a time zone. But the MySQL server has a time zone, and that's the time zone it will use to interpret the data types. You should run your application with the same time zone, otherwise bad stuff is going to happen.
(Both MySQL and your application will get their time zone from the system they are running in, although it's probably possible to override either. I'd advise just running them both in IST, in fact you're probably already doing that.)
Or we might never have existed at all. Freaky. So we should cherish everything. Even this tiny ad: