January 12, 2017 at 4:53 am
Hi Expert,
I have a table where i am logging my app navigation log and its in different time zone. i want it to sort it by GMT. Please help me. Ignore questions marks and blanks for now.Below is the data.
CREATE TABLE #Temp
(
TimeZone VARCHAR(128)
,TimeStamp DATETIME
)
INSERT #Temp
SELECT 'AUS Eastern Standard Time','07/23/2014 05:56:12'
UNION SELECT 'Pacific Standard Time (Mexico)','11/26/2014 09:51:53'
UNION SELECT 'ora solare GMT','03/20/2014 09:47:32'
UNION SELECT 'Vest-Europa (normaltid)','12/20/2013 14:06:36'
UNION SELECT 'India Standard Time','12/31/2016 12:18:11'
UNION SELECT 'Caucasus Standard Time','12/30/2014 13:53:12'
UNION SELECT 'E. South America Standard Time','10/04/2013 16:11:46'
UNION SELECT 'Eastern Daylight Time','11/05/2016 09:04:01'
UNION SELECT 'Mountain Standard Time','12/31/2013 14:45:53'
UNION SELECT 'Romance (zomertijd)','10/25/2013 11:37:31'
UNION SELECT 'E. Australia Standard Time','12/20/2013 10:43:57'
UNION SELECT '???????','12/31/2013 03:21:32'
UNION SELECT 'Vest-Europa (sommertid)','10/24/2013 12:18:11'
UNION SELECT '???? ???','07/22/2013 09:03:26'
UNION SELECT 'W. Europe Daylight Time','10/21/2013 17:03:05'
UNION SELECT 'Arabian Standard Time','12/16/2013 16:41:40'
UNION SELECT 'Russian Standard Time','05/22/2013 16:30:01'
UNION SELECT 'GMT Daylight Time','10/28/2016 15:12:03'
UNION SELECT 'Mitteleuropäische Zeit','12/10/2013 20:46:36'
UNION SELECT 'Mountain Daylight Time','08/19/2013 15:10:59'
UNION SELECT 'Romance Standard Time','12/20/2013 13:03:26'
UNION SELECT 'Romance (standaardtijd)','12/27/2013 10:54:41'
UNION SELECT 'ora solare Europa occidentale','02/11/2014 12:41:36'
UNION SELECT 'Pacific Daylight Time','11/05/2016 14:46:46'
UNION SELECT 'Horário brasileiro de verão','10/24/2013 11:55:26'
UNION SELECT '?? (???)','11/29/2013 07:18:37'
UNION SELECT '?????????? ????? (????)','05/30/2013 19:12:09'
UNION SELECT 'ora legale Europa occidentale','09/09/2013 09:08:46'
UNION SELECT 'Atlantic Daylight Time','09/17/2013 14:01:18'
UNION SELECT 'Paris, Madrid (heure d’été)','10/14/2013 14:12:51'
UNION SELECT 'W. Europe Standard Time','12/30/2013 12:14:24'
UNION SELECT 'Korea Standard Time','05/02/2014 02:42:44'
UNION SELECT 'Hora oficial do Brasil','10/09/2013 10:04:10'
UNION SELECT 'Hawaiian Standard Time','09/02/2015 14:09:18'
UNION SELECT 'Pacific Standard Time','12/31/2016 09:36:47'
UNION SELECT 'South Africa Standard Time','08/13/2014 17:37:14'
UNION SELECT '',''
UNION SELECT 'Mitteleuropäische Sommerzeit','08/08/2014 10:35:25'
UNION SELECT 'ora legale GMT','08/06/2014 10:53:47'
UNION SELECT 'GMT Standard Time','12/31/2013 10:18:36'
UNION SELECT 'West-Europa (zomertijd)','06/26/2014 16:32:31'
UNION SELECT 'West Asia Standard Time','09/07/2016 12:06:45'
UNION SELECT 'Central Daylight Time','10/31/2013 14:53:58'
UNION SELECT 'China Standard Time','12/31/2015 17:54:47'
UNION SELECT 'GMT Summer Time','05/18/2013 10:48:20'
UNION SELECT 'Pacific Daylight Time (Mexico)','06/29/2015 11:05:13'
UNION SELECT 'Romance Daylight Time','10/24/2013 12:14:34'
UNION SELECT '??????','12/31/2013 22:46:20'
UNION SELECT 'Malay Peninsula Standard Time','08/08/2014 10:55:05'
UNION SELECT 'Central Standard Time','12/23/2013 11:38:16'
UNION SELECT 'Coordinated Universal Time','08/05/2016 08:28:33'
UNION SELECT 'US Eastern Daylight Time','08/13/2014 17:43:17'
UNION SELECT 'Eastern Standard Time','12/27/2013 16:19:43'
UNION SELECT 'AUS Eastern Daylight Time','03/18/2014 12:05:45'
UNION SELECT 'Dateline Standard Time','03/17/2014 10:17:48'
UNION SELECT 'Egypt Standard Time','11/08/2013 02:31:06'
Thanks in Advance.
Regards,
Vijay
January 12, 2017 at 6:07 am
How does your app know what time zone it is logging the data in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2017 at 6:11 am
GilaMonster - Thursday, January 12, 2017 6:07 AMHow does your app know what time zone it is logging the data in?
Hi,
Based on TimeZone Column.
Regards,
Vijay
January 12, 2017 at 6:21 am
vijay.singh 46672 - Thursday, January 12, 2017 6:11 AMGilaMonster - Thursday, January 12, 2017 6:07 AMHow does your app know what time zone it is logging the data in?Hi,
Based on TimeZone Column.
Regards,
Vijay
And you've got a table somewhere with teh offsets for each timezone, along with the timezone name?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2017 at 7:05 am
GilaMonster - Thursday, January 12, 2017 6:21 AMvijay.singh 46672 - Thursday, January 12, 2017 6:11 AMGilaMonster - Thursday, January 12, 2017 6:07 AMHow does your app know what time zone it is logging the data in?Hi,
Based on TimeZone Column.
Regards,
VijayAnd you've got a table somewhere with teh offsets for each timezone, along with the timezone name?
Nope.
Regards,
Vijay
January 12, 2017 at 7:08 am
vijay.singh 46672 - Thursday, January 12, 2017 7:05 AMGilaMonster - Thursday, January 12, 2017 6:21 AMvijay.singh 46672 - Thursday, January 12, 2017 6:11 AMGilaMonster - Thursday, January 12, 2017 6:07 AMHow does your app know what time zone it is logging the data in?Hi,
Based on TimeZone Column.
Regards,
VijayAnd you've got a table somewhere with teh offsets for each timezone, along with the timezone name?
Nope.
Regards,
Vijay
I have Timezone name in the same table.
Regards,
Vijay
January 12, 2017 at 7:12 am
vijay.singh 46672 - Thursday, January 12, 2017 7:05 AMGilaMonster - Thursday, January 12, 2017 6:21 AMvijay.singh 46672 - Thursday, January 12, 2017 6:11 AMGilaMonster - Thursday, January 12, 2017 6:07 AMHow does your app know what time zone it is logging the data in?Hi,
Based on TimeZone Column.
Regards,
VijayAnd you've got a table somewhere with teh offsets for each timezone, along with the timezone name?
Nope.
Regards,
Vijay
The you'll need to create one (and since there are timezones with a half hour offset, I'd suggest making the offset column minutes from GMT, not hours). Once you've done that, you can join the two tables and use DATEADD.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2017 at 7:18 am
Since you posted in a SQL 2012 forum, I assume this doesn't apply to you, but if you have SQL Server 2016 or later, you can use the sys.time_zone_info view.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply