CODEWITHSUNDEEP
sql-server
Craig
Craig

Reputation: 18704

Handling timezones

I have a view on a database that gets me the status of some work items. For example, it gets a start date, and end date, and then does some maths based on GetDate() to return me a 'Work Remaining' type of answer.

Problem is, the database server is in the United States and I am sitting in Australia.

So, the time here now is 19h33 on the 20th of July, and GetDate() on the server returns 02h33 on the 20th.

How should timezones be handled? Should my userprofile have a timezone field, and I deduct or add hours on the client side? Problem with that is that everything needs to manipulate dates and times. That is, the application, and any reports.

I have a view, for example, that returns me the remaining hours of a particular piece of work. It uses GETDATE() to calculate this:

ALTER VIEW [dbo].[vwSprintSummary] AS
SELECT 
    SprintId, 
    SprintName, 
    MIN(DateValue) AS FirstSprintDate,
    MAX(DateValue) AS LastSprintDate,
    SprintEndDate,
    COUNT(DISTINCT PersonId) AS AssignedPeople, 
    COUNT(DISTINCT ProjectResourceId) AS AssignedRoles, 
    SUM(AssignedProductiveHours * CanBurnDown) AS Capacity,
    SUM(CASE WHEN CAST(GETDATE() AS DATE) <= DateValue THEN AssignedProductiveHours* CanBurnDown ELSE 0 END) AS RemainingCapacity
FROM [vwSprintDailyBreakdown]
GROUP BY SprintName, SprintId, SprintStartDate, SprintEndDate
GO

Somehow, I need to change the GetDate() to a 'DATEADD...' so that I can get answers in the correct time zone?

In this case, a 'fix' would be to change the GETDATE() to DATEADD(HOUR, 17, GETDATE())

Hardcoded, I know, but in essence, that would fix a problem.

Is there no way to somehow tell the server my zone, and get it to return dates based on my location?

Upvotes: 1

Views: 199

Answers (1)

Dai
Dai

Reputation: 155280

Generally speaking, store all information in your database in UTC and do all business-logic processing in UTC too, only convert to a local timezone in your presentation-logic layer - this approach will save you no-end of trouble, and means that the database server can (and should) remain blissfully unaware of your timezone.

In my own projects I tend to trust the webserver's clock rather than the database server's clock, so I never use GETUTCDATE() on the SQL Server, instead I provide all dates and times to the as a SQL parameter, e.g. a query to find "orders placed today" will provide its own definition of "today" (i.e. by providing a minimum and maximum date/time value).

Upvotes: 3

Related Questions