Question

You have been asked to design a database capable of logging project (work) hours for a geographically dispersed workforce.

• Start and end times may be logged in different geographical locations
• Start times may begin in one location and end in another
• Calculated work hours must be accurate to the minute

Would the difference between a simple start and end date-time recorded in the database satisfy this requirement?

[ why or why not? ... support your answer. ]

Answer 1

No, a simple start and end date-time recorded in the database would not satisfy the requirement mentioned above. Since the workforce is geographically distributed the log of time-stamps made will differ with time-zones. This might have been okay if there was only the need to calculate working hours, with the workforce not moving across time-zones.

But that not the case altogether.It is mentioned that Start times may begin in one location and end in another. Therefore errors in calculation of work hours even seems possible.

Solution:

The normal thing one might do is to use the getdate() or the sysdatetime() functions to create timestamps, most probably in the format 'YYYY-MM-DD HH-MM-SS'. However we need time-zone to be involved.

The function getutcdate() creates GMT(Greenwich Mean Time) timestamps with time-zone offset. The data is non-deterministic(change with time) regarding location details retrieved from the host operating system. But this mechanism can ensure that the work hours calculated are accurate provided that the server does not change it's location.

This function however does not return any data regarding the time-zone. Let's assume that the requirement may also demand this. In such a case comparing the outputs of getdate() and getutcdate() helps to calculate the time-zone the client makes the stamp from. To do this divide the difference of the outputs by 60 to get hour offset and the remainder(modulus) operator provides minute offset.

Hope this answers your question. If you have any doubts place them in the comments.If you are satisfied with the answer please leave in a thumbs up.It really matters.

Thank You.