Timezone change calculations according to UTC as per users timezone

Question

We have one table named Record in db which has some recods. It has below columns

Id(uniqueidentifier)
SubmittedDate(DateTime)
RecordNumber(uniqueidentifier)

below line represent one row of record,

Id                                        RecordNumber                    SubmittedDate
'CD458C6D-9F45-41C1-85D8-E3D8287A82B4', 'EBB5DE3A-4B14-4112-AF51-0F8367FE3383' '2016-11-02 08:27:17.300'

User can submit record from any of timezone. As part of best practice we are saving DateTime in UTC. So, as per users timezone we are converting datetime into UTC and saving it table.

Now, I have to return result as number of records submitted by user by dates. We have to return records as per users timezone.

if user has submitted 10 records on 22Nov2016 at 01:00 AM and no records submitted on date 21Nov2016 and user belongs to IST timezone. At server side we are converting 22Nov2016 01:00AM into IST and storing it as 21Nov2016 09:00PM UTC

When same user queries to get records for last 5 days on date 24 Nov we are adding offset of current users timezone i.e.(+5:30) to SubmittedDate column and fetching records accordingly. So, that we should get correct number of records which user has submitted as per users timezone. After adding UTC offset to SubmittedDate date I will be getting correct number of records i.e. 10 for date of 22Nov2016.

This solution won't work when we have changes in UTC offset during daylight savings i.e. when timezone converts from PST to PDT and vice-versa.

To overcome this we have added timezone column in table of user when user has submitted recods

Id(uniqueidentifier) SubmittedDate(DateTime) RecordNumber(uniqueidentifier) SubmittedTimeZone(varchar)

So, a) When user query record from timezone where daylight savings are on and submitted record is also submitted when there is daylight savings were on we are not doing anything to offset.

b) When user query record from timezone where daylight savings are on and submitted record is submitted when there is daylight savings were off in that case we are adding 1 hour to offset.

c)When user query record from timezone where daylight savings are off and submitted record is submitted when there is daylight savings were on in that case we are adding -1 hour to offset.

d) When user query record from timezone where daylight savings are off are and submitted record is also submitted when there is daylight savings were off we are not doing anything to offset.

Is this proper way of handling timezone/daylight savings for different timezones?

Note: We are identifying submitted record is also submitted when there is daylight savings were on by column SubmittedTimeZone value has last two words as 'DAYLIGHT TIME' like 'ALASKA DAYLIGHT TIME','EASTERN DAYLIGHT TIME'

Here it's possible that daylight savings are on in one time timezone is off on another timezone. Possibly we have to calculate offset for requesting users timezone on basis of submitted date and submitted timezone columns.

score 0 · Accepted Answer · edited May 23 '17 at 12:33

In this case we cannot add offset to Submitted datetime as offset going to change with the effect of daylight savings.

So, The offset when user submits records and when he query number of records submitted can be different. Which will result in incorrect number of records.

Solution to this will be get the timezone of requesting user and convert submittedDatetime value for for each record into requesting users timezone and then do the calculations of number of records submitted as per dates.

Note: For conversion of DateTime into particular datetime

Timezone change calculations according to UTC as per users timezone

1 Answers1