0

In my DB I am storing the users last login date/time using NOW(). I realize this stores using my servers timezone which I have no problem with. While I plan to use this for my own purposes (they say they never logged in or yada yada) I also want to be able to display this last login time on their profile for them to see as well.

What would be the best practice for this? At the moment I am thinking I should just let them choose/edit the timezone from their profile and then this in turn will convert the NOW() value stored in the DB to their time. This would require another column in the DB, but would allow me to do the proper calculation for their time as well as list the current timezone they have selected for their profile.

For instance, I'm in est so I could show 2013-09-04 02:46:05 EST to them on their account profile, but also let them edit their timezone to correct it for their display while keeping the original est date in the DB (my servers config tz).

I realize the giving the user their last login date/time seems kind of useless, but for this website it actually will have some value to them so I want to make sure it reflects 'their' time and makes sense to them.

Is this the best practice or better ideas out there?

user756659
  • 3,372
  • 13
  • 55
  • 110

1 Answers1

1

You cannot find out the timezone of the user from the server. Period.

I strongly recommend you store the login time using UTC (from getutcdate()) as it is unambiguous. If you use local time then daylight savings transitions result in "repeated hours" where the same time will be recorded for events which occur an hour apart. This makes it impossible to work out what time the event actually occurred.

If you don't know the timezone of the user, just display UTC and tell the user it is UTC so they are not confused.

However if you want to know the timezone of the user, you must get the client software to tell you, perhaps by asking the user, and storing this in his preferences. Alternatively you may be able to guess from JavaScript.

See also this question, which discusses the timezone issue in more detail:

Community
  • 1
  • 1
Ben
  • 34,935
  • 6
  • 74
  • 113
  • I understand all of what you said... and yes, I realize there is no way for me to automatically know the users tz. I was just wondering if it was best practice to allow them to select their timezone in their user profile and then display for it. If they don't select it would just be UTC. – user756659 Sep 05 '13 at 06:12
  • @user756659, I would say it is nice to have, and maybe essential in a Facebook-like environment where the user experience is more important than what it actually does, but not essential in business-facing products, where you can demand a little more of the user. – Ben Sep 05 '13 at 08:48