4. Add time-series data

To showcase TimescaleDB and get you familiar with its features, we’ll need some sample data to play around with. We’ll use the real-world scenario of climate change. Data about the climate in a certain region is time-series data, as it describes how the climate in that area changes over time.

About the dataset

Our dataset comes from OpenWeatherMap and contains measurements for 10 cities in which Timescalers reside: New York City, San Francisco, Princeton, Austin, Stockholm, Lisbon, Pietermaritzburg, Nairobi, Toronto and Vienna.

The dataset contains weather metrics for each city from 1 January 1979 to 27 April 2021.

For each city, we record the following metrics:

  1. * time: timestamp of data calculation
  2. * timezone: Shift in seconds from UTC
  3. * city_name: City name
  4. * temp_c: Temperature in degrees celsius
  5. * feels_like_c:  This temperature parameter accounts for the human perception of weather
  6. * temp_min_c: Minimum temperature
  7. * temp_max_c Maximum temperature
  8. * pressure_hpa: Atmospheric pressure (on the sea level) in hPa
  9. * humidity_percent:  humidity as a percentage of
  10. * wind_speed_ms: Wind speed in meters per second
  11. * wind_deg: Wind direction, degrees (meteorological)
  12. * rain_1h: Rain volume for the last hour, mm
  13. * rain_3h: Rain volume for the last 3 hours, mm
  14. * snow_1h: Snow volume for the last hour, in mm
  15. * snow_3h: Snow volume for the last 3 hours, in mm
  16. * clouds_percent: Cloudiness as a percentage of
  17. * weather_id: Weather condition id

Accessing the dataset

We provide a csv file with commands for inserting the data into your TimescaleDB instance.

Download the CSV file (in ZIP format) below and insert it into your database from psql.

Download CSV:

weather_data.zip

After unzipping the file, use the following command (which assumes weather_data.csv is located in your current directory):

  1. -- copy data from weather_data.csv into weather_metrics
  2. \copy weather_metrics (time, timezone_shift, city_name, temp_c, feels_like_c, temp_min_c, temp_max_c, pressure_hpa, humidity_percent, wind_speed_ms, wind_deg, rain_1h_mm, rain_3h_mm, snow_1h_mm, snow_3h_mm, clouds_percent, weather_type_id) from './weather_data.csv' CSV HEADER;

Now that you’re up and running with historical data inside TimescaleDB and a method to ingest the latest data into your database, let’s start querying the data.