Timzone conversion to IST

Question

Timzone conversion to IST

sram24_mca

SSCrazy

Points: 2633
More actions
October 3, 2018 at 11:52 pm

#369019

Hi,
We have convert timezone to IST format on hosting server. Server is in US and Clients are in India. In SQL what is the best way to convert timezone from EST to IST. we are using datetime datatype and getdate() as default date.
Regards,
Ram

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

I'm not quite sure what you mean here. Do you mean that now the server is in the US, set to a US timezone and all data is stored as US time? Or just stored as some time?

If you change the host server to a new timezone, all new data will get written in the host server timezone. If you need to adjust old data, you'll need to issue update statements to the existing data, using math to calculate the difference.

Thom A SSC Guru Points: 99126 More actions · Answer 2

datetime doesn't store any details of what timezone the value is applicable to. In my opinion, If you're working with multiple timezones, idealing you want to either know the timezone (by using datetimeoffset) or storing it in UTC (for example, by using GETUTCDATE). Are either of these an option?

For what your asked, EST is UTC - 5 and IST is UTC+ 5.5, so the you could simply use the expression DATEADD(MINUTE,630,YourDateTimeColumn). This, however, may not give you the correct value if you are using GETDATE() though. GETDATE returns the current date and time of the host server, and, areas with EST (normally) observe Day Light Savings Time. Right now, those in (normally) EST timezones are using EDT, which is UTC -4. If the Server you are using is also, therefore, observing EDT then the expression DATEADD(MINUTE,630,YourDateTimeColumn) will be wrong. If this is a case, this becomes more complex, as you need to have detail on when EDT/EST start and end by date and time (and it still won't be 100% accurate when the clocks go back).

So, this kind of brings us back to my question. Can you change to using a UTC datetime, or use datetimeoffset instead?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sram24_mca SSCrazy Points: 2633 More actions · Answer 3

Steve Jones - SSC Editor - Thursday, October 4, 2018 9:50 AM
I'm not quite sure what you mean here. Do you mean that now the server is in the US, set to a US timezone and all data is stored as US time? Or just stored as some time?
If you change the host server to a new timezone, all new data will get written in the host server timezone. If you need to adjust old data, you'll need to issue update statements to the existing data, using math to calculate the difference.

We will have to Convert EST datetime to IST datetime. Webapplication is hosted in US EST timeformat and customers are accessing the application from IST timezone. In database, all datetime should store in IST timezone. Hope this clears your doubt..!

sram24_mca SSCrazy Points: 2633 More actions · Answer 4

Thom A - Thursday, October 4, 2018 11:32 AM
datetime doesn't store any details of what timezone the value is applicable to. In my opinion, If you're working with multiple timezones, idealing you want to either know the timezone (by using datetimeoffset) or storing it in UTC (for example, by using GETUTCDATE). Are either of these an option?
For what your asked, EST is UTC - 5 and IST is UTC+ 5.5, so the you could simply use the expression DATEADD(MINUTE,630,YourDateTimeColumn). This, however, may not give you the correct value if you are using GETDATE() though. GETDATE returns the current date and time of the host server, and, areas with EST (normally) observe Day Light Savings Time. Right now, those in (normally) EST timezones are using EDT, which is UTC -4. If the Server you are using is also, therefore, observing EDT then the expression DATEADD(MINUTE,630,YourDateTimeColumn) will be wrong. If this is a case, this becomes more complex, as you need to have detail on when EDT/EST start and end by date and time (and it still won't be 100% accurate when the clocks go back).
So, this kind of brings us back to my question. Can you change to using a UTC datetime, or use datetimeoffset instead?

We tried with UTC datetime in sample table , but there is slight difference in hours between IST - UTC and EST- UTC conversion

Thom A SSC Guru Points: 99126 More actions · Answer 5

sram24_mca - Thursday, October 4, 2018 11:21 PM
We tried with UTC datetime in sample table , but there is slight difference in hours between IST - UTC and EST- UTC conversion

Slight? They're about 10 hours apart. I don't see your point here. Were then displaying IST to those in America..? Why? You display the relevant time for there they are.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sram24_mca SSCrazy Points: 2633 More actions · Answer 6

Thom A - Friday, October 5, 2018 12:28 AM
sram24_mca - Thursday, October 4, 2018 11:21 PM
We tried with UTC datetime in sample table , but there is slight difference in hours between IST - UTC and EST- UTC conversion
Slight? They're about 10 hours apart. I don't see your point here. Were then displaying IST to those in America..? Why? You display the relevant time for there they are.

Slight? - changes in UTC time display in US hosting server and India hosting server. Simple We will have to display IST time in application but database is in US. Any help?

Thom A SSC Guru Points: 99126 More actions · Answer 7

sram24_mca - Friday, October 5, 2018 2:48 AM
Thom A - Friday, October 5, 2018 12:28 AM
sram24_mca - Thursday, October 4, 2018 11:21 PM
We tried with UTC datetime in sample table , but there is slight difference in hours between IST - UTC and EST- UTC conversion
Slight? They're about 10 hours apart. I don't see your point here. Were then displaying IST to those in America..? Why? You display the relevant time for there they are.
Slight? - changes in UTC time display in US hosting server and India hosting server. Simple We will have to display IST time in application but database is in US. Any help?

I've already given all the insight I can here. DATEADD is going to get you to the answer, or at least close depending on if the server observes EDT (I gave the expression in my previous post). if it does observe EDT though, unless you store dates and times in UTC or with timezone information it'll never be 100% accurate.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99126 More actions · Answer 8

Perhaps this will be better explained with sample data and SQL:

USE Sandbox;
GO
CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
           EST datetime,
           UTC datetime,
           OffSet datetimeoffset(0));
GO
--Let's insert some sample data
INSERT INTO dbo.SomeTable (EST,
          UTC,
          OffSet)
VALUES ('2018-02-17T11:12:13','2018-02-17T18:12:13','2018-02-17 11:12:13 -05:00'),
    ('2018-02-28T21:17:24','2018-03-01T02:17:13','2018-02-28 02:17:13 -05:00'),
    --Now, let's add some "problems"
    ('2018-03-11T01:54:58','2018-03-11T06:54:58','2018-03-11 01:54:58 -05:00'),--before the clocks go forward
    ('2018-03-11T03:02:57','2018-03-11T07:02:57','2018-03-11 03:02:57 -04:00'),--after the the clocks go forward
    --Wait, the first column jumped an hour, why did the second only incrase by a few minutes?
    --Timezones!
    --Let's go the other way.
    ('2018-11-04T01:45:17','2018-11-04T05:45:17','2018-11-04 01:45:17 -04:00'), --before the clocks go back
    ('2018-11-04T01:03:17','2018-11-04T06:03:17','2018-11-04 01:03:17 -05:00'); --After the clocks go back
    --Wait, what? The value of the first column is "before" the prior row?
GO
--Let's have a look at the data
SELECT *
FROM dbo.SomeTable;
GO
--So, what happens if we just applied a "standard" DATEADD
SELECT EST,
    DATEADD(MINUTE, 630,EST) AS IST
FROM dbo.SomeTable ST
ORDER BY ID;
--Huh, some of those times are wrong. Specifically IDs 4 and 5.
--When daylight saving starts and ends is known, so let's use a "table" to help out:
SELECT ST.EST,
    DATEADD(MINUTE, CASE WHEN ST.EST BETWEEN DS.StartDatetTime AND DS.EndDateTime THEN 570 ELSE 630 END, EST) AS IST
FROM dbo.SomeTable ST
 CROSS APPLY (VALUES ('2018-03-11T02:00:00','2018-11-04T02:00:00')) DS(StartDatetTime, EndDateTime);
--ID's 4 and 5 are correct now Yes! Oh... 6 is wrong now. (that's ebcause it was in the hour AFTER the clocks moved back
--And now, what it would look like if you used UTC or datetimeoffset
SELECT UTC,
    DATEADD(MINUTE, 330,UTC) AS IST,
    OffSet AS OffsetEST,
    SWITCHOFFSET(Offset,'+05:30') AS OffSetIST
FROM dbo.SomeTable;
GO
DROP TABLE dbo.SomeTable;

So, like I said before, using only GETDATE() and datetime is going to cause you an issue if one of those timezones observes DayLight Savings time (EST does). You can get close, but it will never be 100% accurate without using UTC or timezone data.

For completion, if you wanted to calculate EST from UTC then:

SELECT UTC,
   DATEADD(MINUTE, 330,UTC) AS IST,
    DATEADD(MINUTE, CASE WHEN UTC BETWEEN DS.ESTStart AND DS.ESTEnd THEN -240 ELSE -300 END, UTC) AS EST,
    OffSet AS OffsetEST,
    SWITCHOFFSET (Offset,'+05:30') AS OffSetIST
FROM dbo.SomeTable
   CROSS APPLY (VALUES ('2018-03-11T07:00:00','2018-11-04T06:00:00')) DS(ESTStart, ESTEnd);

Note that you still need detail on the Start and End dates for UTC to EST/EDT.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sram24_mca SSCrazy Points: 2633 More actions · Answer 9

Thom A - Friday, October 5, 2018 3:27 AM
Perhaps this will be better explained with sample data and SQL:
USE Sandbox;
GO
CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
           EST datetime,
          UTC datetime,
          OffSet datetimeoffset(0));
GO
--Let's insert some sample data
INSERT INTO dbo.SomeTable (EST,
          UTC,
         OffSet)
VALUES ('2018-02-17T11:12:13','2018-02-17T18:12:13','2018-02-17 11:12:13 -05:00'),
('2018-02-28T21:17:24','2018-03-01T02:17:13','2018-02-28 02:17:13 -05:00'),
   --Now, let's add some "problems"
   ('2018-03-11T01:54:58','2018-03-11T06:54:58','2018-03-11 01:54:58 -05:00'),--before the clocks go forward
   ('2018-03-11T03:02:57','2018-03-11T07:02:57','2018-03-11 03:02:57 -04:00'),--after the the clocks go forward
   --Wait, the first column jumped an hour, why did the second only incrase by a few minutes?
   --Timezones!
   --Let's go the other way.
   ('2018-11-04T01:45:17','2018-11-04T05:45:17','2018-11-04 01:45:17 -04:00'), --before the clocks go back
   ('2018-11-04T01:03:17','2018-11-04T06:03:17','2018-11-04 01:03:17 -05:00'); --After the clocks go back
   --Wait, what? The value of the first column is "before" the prior row?
GO
--Let's have a look at the data
SELECT *
FROM dbo.SomeTable;
GO
--So, what happens if we just applied a "standard" DATEADD
SELECT EST,
   DATEADD(MINUTE, 630,EST) AS IST
FROM dbo.SomeTable ST
ORDER BY ID;
--Huh, some of those times are wrong. Specifically IDs 4 and 5.
--When daylight saving starts and ends is known, so let's use a "table" to help out:
SELECT ST.EST,
   DATEADD(MINUTE, CASE WHEN ST.EST BETWEEN DS.StartDatetTime AND DS.EndDateTime THEN 570 ELSE 630 END, EST) AS IST
FROM dbo.SomeTable ST
CROSS APPLY (VALUES ('2018-03-11T02:00:00','2018-11-04T02:00:00')) DS(StartDatetTime, EndDateTime);
--ID's 4 and 5 are correct now Yes! Oh... 6 is wrong now. (that's ebcause it was in the hour AFTER the clocks moved back
--And now, what it would look like if you used UTC or datetimeoffset
SELECT UTC,
   DATEADD(MINUTE, 330,UTC) AS IST,
   OffSet AS OffsetEST,
   SWITCHOFFSET(Offset,'+05:30') AS OffSetIST
FROM dbo.SomeTable;
GO
DROP TABLE dbo.SomeTable;
So, like I said before, using only GETDATE() and datetime is going to cause you an issue if one of those timezones observes DayLight Savings time (EST does). You can get close, but it will never be 100% accurate without using UTC or timezone data.
For completion, if you wanted to calculate EST from UTC then:
SELECT UTC,
   DATEADD(MINUTE, 330,UTC) AS IST,
   DATEADD(MINUTE, CASE WHEN UTC BETWEEN DS.ESTStart AND DS.ESTEnd THEN -240 ELSE -300 END, UTC) AS EST,
   OffSet AS OffsetEST,
   SWITCHOFFSET (Offset,'+05:30') AS OffSetIST
FROM dbo.SomeTable
CROSS APPLY (VALUES ('2018-03-11T07:00:00','2018-11-04T06:00:00')) DS(ESTStart, ESTEnd);
Note that you still need detail on the Start and End dates for UTC to EST/EDT.

Thank you

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

Quick summary of what I'd do here.

Since I'm changing the timezone of server, all existing data will display incorrectly, while all new data is correct. Therefore, I need to update the existing data.

1. find out the min/max of values that exist
2. find the proper DST dates for the range
3. TEST THIS - write a query (or series of queries for each time range ) to update data.
   Here I'd actually use multiple queries because it's simple. For example, for 2018, I'd do this:
   update table
   set datevalue = dateadd(hours, -10, datevalue)
   where datevalue >= '2018-01-01' and datevalue < '2018-03-11'
   update table
   set datevalue = dateadd(hours, -9, datevalue)
   where datevalue >= '2018-03-11'
That is the 2018 date. Check the math as it might be -10, -11, but you have to determine this.
4. Quiese the application, stop all users from accessing
5. update the data
6. change the time zone
7 add users back.

You could get by without 4,7, but make sure your queries work well and you might work in batches as otherwise you'll lock the table.