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 October, 2024 |
---|---|
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.