12

I just need to make a SQL table of time zones - at this point, my only need is to populate a drop-down list on an online form. I'm envisioning something simple with columns for ISO code, name, UTC offset, and perhaps a list of representative cities. I thought I would be able to easily find something online to copy-and-paste, but haven't been able to locate anything.

I was directed by this question to the tz database, which is in binary form and seems like overkill for what I need. Alternatively I could piece this together from sites like TimeAndDate.com, but that seems like more work than should be necessary.

Or am I going about this the wrong way - e.g. should I be getting this information from the server's OS?

Community
  • 1
  • 1
Herb Caudill
  • 50,043
  • 39
  • 124
  • 173
  • Depends on what you are trying to do and why it is important to know. You can use javascripts date-object on the online-form to find out what timezone the user has configured their computer to be in (not necessarily where they are at the moment) – some Oct 15 '09 at 11:35

6 Answers6

35

the list of all timezone are here:

select * from sys.time_zone_info

Enjoy!

Nelson Gomes Matias
  • 1,787
  • 5
  • 22
  • 35
14

Are you on .NET 3.5 ? You can easily get a list of timezones in .NET 3.5 and then store that information (or at least whatever you need of it) in your SQL Server database.

You could iterate over all timezones available and known to .NET 3.5 and store the relevant info to a SQL Server table:

ReadOnlyCollection<TimeZoneInfo> timeZones = TimeZoneInfo.GetSystemTimeZones();

foreach (TimeZoneInfo timeZone in timeZones)
{
    // store whatever you need to store to a SQL Server table
}

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
6

I took marc_s answer a step further - here's the code to create a simple timezone table and .net code that generates the inserts for each UTC record:

--TSQL TO CREATE THE TABLE
CREATE TABLE [dbo].[TimeZones] (
[TimeZoneID]           INT              IDENTITY (1, 1) NOT NULL,
[DisplayName]  VARCHAR(100) NOT NULL,
[StandardName]        VARCHAR (100)    NOT NULL,
[HasDST]    BIT  NOT NULL,
[UTCOffset]      INT NOT NULL
CONSTRAINT [PK_TimeZones] PRIMARY KEY CLUSTERED ([TimeZoneID] ASC)
);
GO

To generate the insert statements, I created a default web .net project using visual studio and in the view I pasted this, ran the project and then copied the rendered code (remember to copy it from view source, not from directly the html page):

System.Collections.ObjectModel.ReadOnlyCollection<TimeZoneInfo> timeZones = TimeZoneInfo.GetSystemTimeZones();

foreach (TimeZoneInfo timeZone in timeZones)
{
    Response.Write("INSERT INTO TimeZones (DisplayName, StandardName, HasDST, UTCOffset) VALUES ('" + timeZone.DisplayName.Replace("'", "''") + "', '" + timeZone.StandardName.Replace("'", "''") + "', '" + timeZone.SupportsDaylightSavingTime + "', '" + timeZone.BaseUtcOffset + "')" + Environment.NewLine);
}

Hope this helps

Losbear
  • 3,255
  • 1
  • 32
  • 28
1

I you want a good list to copy/paste from : http://en.wikipedia.org/wiki/Timezones

Andrew
  • 26,629
  • 5
  • 63
  • 86
1

Get it from the OS.

In that you've tagged this asp.net have a look at this example of how to enumerate timezones.

Matt Lacey
  • 65,560
  • 11
  • 91
  • 143
0

I know this question was asked long time ago but for anyone that might still need it, here is where you can find an sql data of timezone list https://timezonedb.com/download

Emmanuel Okechukwu
  • 1
  • 1