How do I handle the timezones for every Chat message

Question

I am creating a web chat application using ASP.NET. I have got the chat system to work as messages are sent and relayed back to the clients. But there was something I noticed but never thought it would be a problem. I am from England and the chat application is sitting on servers in America and noticed when the message displayed the time, the times were situated in American time zones. How would I go about setting the timezones that will correspond to the user's timezone.

Answer 1

Store your data in UTC. Always. So instead of storing GETDATE(), store GETUTCDATE() (or SYSUTCDATETIME() in SQL Server 2008+).

This makes it very easy to convert all times in a chat to whatever time zone the user is in. And there are plenty of facilities in ASP.NET to help you with this conversion if you don't want to do it in SQL.

Answer 2

I am changing the date zone to the Brazilian time that was my need.

VALUES (GETDATE () AT TIME ZONE 'UTC' AT TIME ZONE 'E. South America Standard Time');