• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MYSQL : storing dates and time of user activities in DB

 
Ranch Hand
Posts: 658
2
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.)
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic