Win a copy of Succeeding with AI this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
  • Junilu Lacar
  • Tim Cooke
  • Jeanne Boyarsky
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • salvin francis
  • fred rosenberger
  • Frits Walraven

MYSQL : storing dates and time of user activities in DB

Ranch Hand
Posts: 649
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Please suggest me the best I should choose.
Posts: 25482
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
Thread Boost feature
    Bookmark Topic Watch Topic
  • New Topic