1

I have searched around and haven't found a specific answer to my question..

I am wondering the best practice for storing timestamp information.

Example: user1 logs in in Florida (-0500) at 3:00pm EST The admin logs into the system an hour later from California (-0800) at 4:00pm

I want the admin to see the log onscreen and see that user1 logged in 1 hour ago..

My brain is going nuts bc i feel that i am over complicating this..

I should be able to use UNIX time stamp and then adjust with the -0800 or -0300.

Do i store the users timezone offset in their profile information? Do i store the -0300 with the log entry.

Any input would be greatly appreciated!

JD Vangsness
  • 697
  • 3
  • 10
  • 27

3 Answers3

1

You are mixing up client timezone and server timezone.

As long as you do not care where your users sit, you have absolutely no problem as long as you do not change the server timezone. You can set a default timezone in MySQL and in PHP and this one will be used, independent of the timezone of the visitor.

If you want to respect the timezone of your visitors, it gets slightly more complicated. You need do transform a timestamp from one timezone into another. You can find information concerning that in the PHP manual if you want to achieve this with PHP. You can read this SO thread if you are interested in how MySQL stores dates internally (difference between TIMESTAMP and DATETIME type), which opens the possibility to use some of mysql's features, and which you should definitely take into account when you implement a global project.

Community
  • 1
  • 1
Ulrich Thomas Gabor
  • 6,584
  • 4
  • 27
  • 41
  • So what you are saying is that i should store the time stamps in the database based on the servers timezone. Then i store the users timezone offest and when presenting all of the times to the user logged in i use their offest? – JD Vangsness Feb 23 '14 at 17:35
  • Yes, this would be one possibility and probably the best way to go. – Ulrich Thomas Gabor Feb 23 '14 at 17:57
  • Another option is to save UTC in the database, but this requires a bit more planning and thinking. You might give this solution a thought too and decide for the one which fits your needs more. – Ulrich Thomas Gabor Feb 23 '14 at 18:00
  • After doing some thinking, about scale-ability, i should base all times off of UTC so no matter what happens with srever configurations and location in the future, the times will always be referenced off of the same base time. Thank you! – JD Vangsness Feb 23 '14 at 20:03
  • You might want to read this too: http://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc – Ulrich Thomas Gabor Feb 23 '14 at 20:29
0

Timezone can be configured dynamically in the PHP, so you don't need to store time offsets in your DB. Storing unix timestamp is a good solution.

Artur
  • 384
  • 1
  • 7
0

You can store timestamp as INTEGER datatype.

Then, you can use JS on client side to get the timezone of user.