IP2Location™ Olson Time Zone Database
IP2Location™ Olson Time Zone, or tz data, is a free data offered to our valued customers. This data enables you to retrieve the Olson Time Zone value of a given city name. This piece of information is very useful if you need it for web display, dropdown box selection or other coding requirements.
Last Update | 01 March, 2021 |
---|---|
Database Format | CSV Text File (Comma Delimited) |
Name | Type | Description |
---|---|---|
country_code | CHAR(2) | Two-character country code based on ISO 3166. |
region_name | VARCHAR(128) | Region or state name. |
city_name | VARCHAR(128) | City name. |
olson_tz | VARCHAR(30) | Olson time zone. |
Sample Database
"country_code","region_name","city_name","olson_tz" "AD","Andorra la Vella","Andorra la Vella","Europe/Andorra" "AD","Andorra la Vella","Santa Coloma","Europe/Andorra" "AD","Canillo","Canillo","Europe/Andorra" "AD","Canillo","El Tarter","Europe/Andorra" "AD","Canillo","Ransol","Europe/Andorra"
MySQL Statement
CREATE TABLE `ip2location_olson_timezone`( `country_code` CHAR(2), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `olson_tz` VARCHAR(30), INDEX `idx_country_code` (`country_code`), INDEX `idx_region_name` (`region_name`), INDEX `idx_city_name` (`city_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; LOAD DATA LOCAL INFILE 'IP2LOCATION-OLSON-TIMEZONE.CSV' INTO TABLE `ip2location_olson_timezone` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
<?php define('SERVER','server'); define('USER','user'); define ('PASSWORD','password'); define('DATABASE','database'); $conn = mysqli_connect(SERVER, USER, PASSWORD, DATABASE); ?> <!doctype html> <html> <head> <title>Olson Timezone</title> </head> <body> <h1>Olson Timezone</h1> <form action= "Olson_tz.php" method="post"/> <div> Country Code: <select name="countryCode"> <?php $sql = mysqli_query($conn, "SELECT distinct country_code FROM ip2location_olson_timezone"); while ($row = $sql->fetch_assoc()){ echo "<option value='". $row['country_code'] ."'>" .$row['country_code'] ."</option>"; } ?> </select> </div> <br /> <div> Region Name: <select name="regionName"> <?php $sql = mysqli_query($conn, "SELECT distinct region_name FROM ip2location_olson_timezone"); while ($row = $sql->fetch_assoc()){ echo "<option>" .$row['region_name'] ."</option>"; } ?> </select> </div> <br /> <div> City Name: <input type="text" name="cityName" text="" value="" /> </div> <br /> <div> <input type="submit" name="submit" value="Check"/> </div> </form> <?php if(isset($_POST['submit'])){ //check if form was submitted $countryCode = $_POST['countryCode']; //get countryCode text $regionName = $_POST["regionName"]; //get regionName text $cityName = $_POST['cityName']; //get cityName text $sqlDisplay = "Select olson_tz from ip2location_olson_timezone where country_code = '$countryCode' AND region_name = \"$regionName \" AND city_name = \"$cityName\""; $rsResult = mysqli_query($conn,$sqlDisplay) or die(mysqli_error($conn)); $num_rows = mysqli_num_rows($rsResult); if($num_rows ==0){ echo"<script type='text/javascript'>alert('Result not found, please try again!')</script>"; } else{ while ($row = mysqli_fetch_assoc($rsResult) or die (mysqli_error($conn))){ $olson_tz = $row['olson_tz']; echo"<br />"; echo"<table border='1'>"; echo"<tr>"; echo"<td>Country Code:</td>"; echo"<td>$countryCode</td>"; echo"</tr>"; echo"<tr>"; echo"<td>Region Name:</td>"; echo"<td>$regionName</td>"; echo"</tr>"; echo"<tr>"; echo"<td>City Name:</td>"; echo"<td>$cityName</td>"; echo"</tr>"; echo"<tr>"; echo"<td>Olson Timezone:</td>"; echo"<td>$olson_tz</td>"; echo"</tr>"; echo"</table>"; } } } mysqli_close($conn); ?> </body> </html>
public partial class olson_Timezone : System.Web.UI.Page { string MyConnectionString = "datasource=localhost;port=3306;Database=;username=;password="; protected void Page_Load(object sender, EventArgs e) { MySqlConnection connection = new MySqlConnection(MyConnectionString); connection.Open(); MySqlCommand SelectCommand = new MySqlCommand("select distinct country_code from ip2location_olson_timezone;", connection); MySqlDataAdapter adap = new MySqlDataAdapter(SelectCommand); DataTable dt = new DataTable(); adap.Fill(dt); DropDownList3.DataSource = dt; DropDownList3.DataBind(); DropDownList3.DataTextField = "country_code"; DropDownList3.DataBind(); select_RegionName(); } public void select_RegionName() { MySqlConnection connection = new MySqlConnection(MyConnectionString); connection.Open(); MySqlCommand SelectCommand2 = new MySqlCommand("select distinct region_name from ip2location_olson_timezone;", connection); MySqlDataAdapter adap2 = new MySqlDataAdapter(SelectCommand2); DataTable dt2 = new DataTable(); adap2.Fill(dt2); DropDownList2.DataSource = dt2; DropDownList2.DataBind(); DropDownList2.DataTextField = "region_name"; DropDownList2.DataBind(); } protected void check_Button(object sender, EventArgs e) { if (Request.Form["DropDownList3"].ToString() == "") { myLabel.Text = "Country code is empty."; } else if (Request.Form["DropDownList2"].ToString() == "") { myLabel.Text = "Region name is empty."; } else if (city_name.Text == "") { myLabel.Text = "City name is empty."; } else { string selectedRegion = Request.Form["DropDownList2"].ToString(); string selectedCountryCode = Request.Form["DropDownList3"].ToString(); string cityName = city_name.Text; MySqlConnection connection = new MySqlConnection(MyConnectionString); connection.Open(); string query = "Select olson_tz from ip2location_olson_timezone where country_code = '" + selectedCountryCode + "' AND region_name = @firstname AND city_name= @cityName"; MySqlCommand SelectCommand2 = new MySqlCommand(query, connection); MySqlParameter param = new MySqlParameter(); MySqlParameter param2 = new MySqlParameter(); param.ParameterName = "@firstname"; param.Value = selectedRegion; param2.ParameterName = "@cityName"; param2.Value = cityName; SelectCommand2.Parameters.Add(param); SelectCommand2.Parameters.Add(param2); MySqlDataReader reader = SelectCommand2.ExecuteReader(); if (reader.Read() == true) { myLabel.Text = "Country code: " + selectedCountryCode + "<br />Region name: " + selectedRegion + "<br />City Name: " + cityName + "<br />Olson Timezone: " + reader["olson_tz"].ToString(); } else { myLabel.Text = "Data not found."; } } } }
Imports System.Data.SqlClient Imports System.Diagnostics.Eventing Imports MySql.Data.MySqlClient Public Class Form1 Private Sub ddlCountryCode_SelectedIndexChanged(sender As Object, e As EventArgs) Handles MyBase.Load Dim connection As New MySqlConnection("Server=localhost;Database=;username=;password=") Dim sqlCommand As New MySqlCommand("select distinct country_code from ip2location_olson_timezone", connection) Dim adapter As New MySqlDataAdapter(sqlCommand) Dim table As New DataTable() adapter.Fill(table) ddlCountryCode.DataSource = table ddlCountryCode.DisplayMember = "country_code" ddlCountryCode.ValueMember = "country_code" End Sub Private Sub ddlRegionName_SelectedIndexChanged(sender As Object, e As EventArgs) Handles MyBase.Load Dim connection As New MySqlConnection("Server=localhost;Database=;username=;password=") Dim sqlCommand As New MySqlCommand("select distinct region_name from ip2location_olson_timezone", connection) Dim adapter As New MySqlDataAdapter(sqlCommand) Dim table As New DataTable() adapter.Fill(table) ddlRegionName.DataSource = table ddlRegionName.DisplayMember = "region_name" ddlRegionName.ValueMember = "country_code" End Sub Private Sub checkButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles checkButton.Click If (ddlCountryCode.Text = "") Then MessageBox.Show("Please choose the country code") ElseIf (ddlRegionName.Text = "") Then MessageBox.Show("Please choose the region name") ElseIf (TextBox1.Text = "") Then MessageBox.Show("Please fill in the city name") Else Dim selected_CountryCode = ddlCountryCode.Text Dim selected_RegionName = ddlRegionName.Text Dim city_name = TextBox1.Text Dim connection As New MySqlConnection("Server=localhost;Database=;username=;password=") Dim sql As String = "Select olson_tz from ip2location_olson_timezone where country_code ='" & selected_CountryCode & "' AND region_name = @regionName AND city_name = @cityName" Dim Command As New MySqlCommand(sql, connection) Command.Parameters.AddWithValue("@regionName", selected_RegionName) Command.Parameters.AddWithValue("@cityName", city_name) Dim adapter As New MySqlDataAdapter(Command) Dim table As New DataTable() adapter.Fill(table) If (table.Rows.Count = 0) Then MessageBox.Show("No data") Else Label4.Text = "Country code:" + selected_CountryCode + Environment.NewLine + "Region name: " + selected_RegionName + Environment.NewLine + "City name: " + city_name + Environment.NewLine + "Olson Timezone: " + table.Rows(0)(0).ToString() End If End If End Sub End Class
package ip2locationjava; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class IP2LocationJava { public static void main(String[] args) { Scanner reader = new Scanner(System.in); System.out.println("Enter your country code:"); String countryCode = reader.nextLine(); System.out.println("Enter your region name:"); String regionName = reader.nextLine(); System.out.println("Enter your city name:"); String cityName = reader.nextLine(); String databaseURL = "jdbc:mysql://localhost:3306/ip2location"; String user = "root"; String password = ""; Connection conn = null; String data =""; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(databaseURL, user, password); PreparedStatement statement =conn.prepareStatement("select olson_tz from ip2location_olson_timezone where country_code='"+countryCode +"' AND region_name = ? AND city_name= ?"); statement.setString(1, regionName); statement.setString(2, cityName); ResultSet rs = statement.executeQuery(); if(rs.next() == true) { data = "Country code:"+ countryCode+"\nRegion name:"+regionName+"\nCity Name:"+cityName+"\nOlson Timezone " + rs.getString("olson_tz"); System.out.println(data); }else{ System.out.println("No data"); } } catch (ClassNotFoundException ex) { System.out.println("Could not find database driver class"); ex.printStackTrace(); } catch (SQLException ex) { System.out.println("An error occurred."); ex.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } } } }
Attribution is required by mentioning the use of this data as follows, "The IP geolocation data is provided by https://www.ip2location.com", on your webpage and in the documentation or advertising.
IP2Location™ and IP2Proxy™ are registered trademarks of HEXASOFT. All other trademarks are the properties of their respective owners.