IP address database of Self-Judgment time zone and Time Interval

Some days ago, because users are scattered in different countries, a need was required to manually determine the time zone where users are located, there are several solutions for converting the time into the local time. The IP address database is simpler than the local time, there are many companies that offer IP addresses to their countries, regions, and time zones. Most of them require money. Otherwise, there will be a small amount of free-of-charge data, when I found a completely free-of-charge ipinfodb, I would like to share with you how to use this information to complete the time zone.

 

New Resource

 

Address: http://ipinfodb.com/

 

Information under the province: http://ipinfodb.com/ip_database.php

There are two types of information, city and country. Different cities are in the same time zone like Taiwan, but large countries like the United States and Canada, different cities have different time zones. Therefore, if only country information is available, there is no correct time zone, the city is classified into two types: small or complete. If the small under the primary node is used, it should be enough because it is easier for me to select a table, so I use the one Ables format.

 

Time zone lower region: http://ipinfodb.com/timezonedatabase.php

 

There are two formats: SQL and CSV. SQL is the MySQL format, not the standard SQL. If your data indexing engine is not mysql, do not renew it, I use ms SQL, so I want to export CSV files.

There are a total of 6 cases in the split of the two regions.

• Ip_group_country.csv does not add an IP address to the corresponding country. Because the city table contains the country information, this table is unavailable.
• Ip_group_city.csv: the city in which IP maps are added, including the country information and base standards.
• Iso1_6_countries.csv does not add country counterparts, such as Tw = Taiwan. The information is included in the city.
• Fips_regions.csv addedFEderalINformationPRocessingSTandards (State data processing standard) region, the time zone for urban response.
• Timezones.csv is the region name when the region is selected, for example, Asia/Taipei. The region is unavailable during the selected period, but it can be used to display information.
• Timezones_data.csv: the UTC offset that is applied to the new time zone.

I use the Import and Export Wizard of ms SQL to import data. I have encountered some minor issues during the import process.

1. The commit row in the commit case is {lf }.

2. The text is separated by delimiter.

3. The first line is the information.

 

The input format is string. There are three data records that must be written. Otherwise, the calculation may result in errors.

• Ip_group_city.csvIp_start (bigint ).
• Timezones.csvStart (bigint),Gmtoff (INT).

Note: Because ip_start is not a writable int, ms SQL does not have this format, so use bigint.

 

Material format description Ip_group_city

In the int format of ip_start IP, you can open the small computing terminal (win7) to calculate what the value means.

 

Latitude and longpolling are economic degrees.

You only need to retrieve the first region that is larger than the current IP address when querying the hosts file. The SQL statement is as follows:

 
Select Top1 *FromIp_group_cityWhereIp_start <= 1249717604Order ByIp_start DESC
 

Fips_regions.csv can obtain country_code and region_code from the ip_group_city data table to determine the timezone. The size of timezones_data.csv start and gmtoff is the number of seconds. Start is in MySQL format and is based on the 1970/1/1 period. You can use the following SQL statement to obtain the correct time.

 
SelectDateadd (SS, 339102000,'2014/1/1') -- 1980-09-29 19:00:00. 000
 

Abbreviation refers to the time format

• CST Central Standard Time-US Central Standard Time
• Central Daylight Time-the period of the U.S. summer order takes a few minutes and I haven't fully understood it yet, so I have not decided on it in this example.

Select the largest shard of start.

 

Example

This is an example of an IP address, including the IP address, location, time zone, and other information.

1. Obtain the IP address first.

 
String IP;
If(String. isnullorempty (this. Request. querystring ["ip"])
{
IP = This. Request. userhostaddress;// Userhostaddress can be used to obtain the IP
}
Else
{
IP = This. Request. querystring ["ip"];// Use the numeric value if there is a numeric value
}
 

2. Convert the IP address into a uint

String [] temp = IP. Split ('.');
// The IP address does not have a valid region, so uint is used.
Uint ipint = (uint. parse (temp [0]) <24) + (uint. parse (temp [1]) <16) + (uint. parse (temp [2]) <8) + uint. parse (temp [3]);
 

3. Obtain information

 // Obtain the information using LINQ. 
Ipinfodatacontext ipinfo = New Ipinfodatacontext ();

 // City information 
VaR city = ipinfo. ip_group_cities
. Where (x => X. ip_start> = ipint)
. Orderby (x => X. ip_start)
. First ();

 // Region information 
Var region = ipinfo. fips_regions
. Where (x => X. country_code = city. country_code & X. Code = city. region_code)
. First ();

 // Time Zone name 
VaR timezone = ipinfo. timezones
. Where (x => X. ID = region. timezone)
. First ();

 // Time Zone Information 
VaR timezonedata = ipinfo. timezones_datas
. Where (x => X. timezone = region. timezone)
. Orderbydescending (x => X. Start)
. First ();

4. Information Presentation

 
This. Zonelabel. innertext = timezone. Name;
This. Locallabel. innertext =String. Format ("{0} ({1 })", City. region_name, city. country_name );;
This. Offsetlabel. innertext = (timezonedata. gmtoff/3600). tostring ();
This. Localtimelabel. innertext = datetime. utcnow. addseconds ((Double) Timezonedata. gmtoff). tostring ();
This. Map. Attributes. Add ("Src",String. Format ("Http://maps.google.com.tw/maps? F = Q & source = s_q & Q = {0} + {1} & Z = 7 & Output = embed", City. Latitude, city. longpolling ));
 

Conclusion

When using this solution, there are several shortcomings:

• The IP address location will not know if it changes. If it is rejected, there may be post-sale services. The cost-free resources may be the latest.
• VPN information must be manually created.