Sample datasets

Timescale have created several sample datasets to help you get started using TimescaleDB. These datasets vary in database size, number of time intervals, and number of values for the partition field.

Each gzip archive contains a single .sql file to create the necessary hypertables within the database, and several .csv files that contain the data to be copied into those tables. These files presume the database you are importing them to has already been set up with the TimescaleDB extension.

Device ops: these datasets include metrics such as CPU, memory, and network, that are collected from mobile devices. Click on the name to download.

  • devices_small

    1,000 devices recorded over 1,000 time intervals - 39 MB

  • devices_med

    5,000 devices recorded over 2,000 time intervals - 390 MB

  • devices_big

    3,000 devices recorded over 10,000 time intervals - 1.2 GB

For more details and example usage, see device ops datasets.

Weather: these datasets include metrics like temperature and humidity data from a variety of locations. Click on the name to download.

  • weather_small

    1,000 locations over 1,000 two-minute intervals - 8.1 MB

  • weather_med

    1,000 locations over 15,000 two-minute intervals - 115 MB

  • weather_big

    2,000 locations over 20,000 two-minute intervals - 305 MB

For more details and example usage, see weather datasets.

Importing

Briefly, the import steps are:

  1. Setup a database with TimescaleDB.
  2. Unzip the archive.
  3. Import the .sql file to create the hypertables via psql.
  4. Import the data from .csv files via psql.

Each dataset has a name in the format of [dataset]_[size].tar.gz. For example, devices_small.tar.gz is dataset devices and size small. Each dataset contains one .sql file named [dataset].sql and a few CSV files named in the format [dataset]_[size]_[table].csv.

As an example, if you wanted to import the devices_small dataset, it creates two tables (device_info and a hypertable named readings) from devices.sql. Therefore, there are two CSV files: devices_small_readings.csv and devices_small_device_info.csv. So, to import this dataset into a TimescaleDB database named devices_small:

  2. # (1) unzip the archive
  4. tar -xvzf devices_small.tar.gz
  6. # (2) import the .sql file to the database
  8. psql -U postgres -d tsdb < devices.sql
  10. # (3) import data from .csv files to the database
  12. psql -U postgres -d tsdb -c "\COPY readings FROM devices_small_readings.csv CSV"
  14. psql -U postgres -d tsdb -c "\COPY device_info FROM devices_small_device_info.csv CSV"

The data is now ready for use.

tip

The standard COPY command in PostgreSQL is single threaded. To speed up importing the larger sample datasets, you can use the parallel importer instead.

  2. # To access your database (for example: `tsdb`)
  3. `
  4. psql -U postgres -h localhost -d tsdb

Device ops datasets

After importing one of these datasets (devices_small, devices_med, devices_big), you have a plain PostgreSQL table called device_info and a hypertable called readings. The device_info table has static metadata about each device, such as the OS name and manufacturer. The readings hypertable tracks data sent from each device, for example CPU activity, or memory levels. Hypertables are exposed as a single table, so you can query them and join them with the metadata as you would normal SQL tables, as shown in the example queries in this section.

Schemas

  2. Table "public.device_info"
  4. Column       | Type | Modifiers
  6. -------------+------+-----------
  8. device_id    | text |
  10. api_version  | text |
  12. manufacturer | text |
  14. model        | text |
  16. os_name      | text |
  18. Table "public.readings"
  20. Column              |       Type       | Modifiers
  22. --------------------+------------------+-----------
  24. time                | bigint           |
  26. device_id           | text             |
  28. battery_level       | double precision |
  30. battery_status      | text             |
  32. battery_temperature | double precision |
  34. bssid               | text             |
  36. cpu_avg_1min        | double precision |
  38. cpu_avg_5min        | double precision |
  40. cpu_avg_15min       | double precision |
  42. mem_free            | double precision |
  44. mem_used            | double precision |
  46. rssi                | double precision |
  48. ssid                | text             |
  50. Indexes:
  52.   "readings_device_id_time_idx" btree (device_id, "time" DESC)
  54.   "readings_time_idx" btree ("time" DESC)

Example queries

Uses devices_med dataset

10 most recent battery temperature readings for charging devices

  2. SELECT time, device_id, battery_temperature
  4. FROM readings
  6. WHERE battery_status = 'charging'
  8. ORDER BY time DESC LIMIT 10;
  10. time                   | device_id  | battery_temperature
  12. -----------------------+------------+---------------------
  14. 2016-11-15 23:39:30-05 | demo004887 |                99.3
  16. 2016-11-15 23:39:30-05 | demo004882 |               100.8
  18. 2016-11-15 23:39:30-05 | demo004862 |                95.7
  20. 2016-11-15 23:39:30-05 | demo004844 |                95.5
  22. 2016-11-15 23:39:30-05 | demo004841 |                95.4
  24. 2016-11-15 23:39:30-05 | demo004804 |               101.6
  26. 2016-11-15 23:39:30-05 | demo004784 |               100.6
  28. 2016-11-15 23:39:30-05 | demo004760 |                99.1
  30. 2016-11-15 23:39:30-05 | demo004731 |                97.9
  32. 2016-11-15 23:39:30-05 | demo004729 |                99.6
  34. (10 rows)

Busiest devices (1 min avg) whose battery level is below 33% and is not charging

  2. SELECT time, readings.device_id, cpu_avg_1min,
  4. battery_level, battery_status, device_info.model
  6. FROM readings
  8. JOIN device_info ON readings.device_id = device_info.device_id
  10. WHERE battery_level < 33 AND battery_status = 'discharging'
  12. ORDER BY cpu_avg_1min DESC, time DESC LIMIT 5;
  14. time                   | device_id  | cpu_avg_1min | battery_level | battery_status |  model
  16. -----------------------+------------+--------------+---------------+----------------+---------
  18. 2016-11-15 23:30:00-05 | demo003764 |        98.99 |            32 | discharging    | focus
  20. 2016-11-15 22:54:30-05 | demo001935 |        98.99 |            30 | discharging    | pinto
  22. 2016-11-15 19:10:30-05 | demo000695 |        98.99 |            23 | discharging    | focus
  24. 2016-11-15 16:46:00-05 | demo002784 |        98.99 |            18 | discharging    | pinto
  26. 2016-11-15 14:58:30-05 | demo004978 |        98.99 |            22 | discharging    | mustang
  28. (5 rows)
  30. SELECT date_trunc('hour', time) "hour",
  32. min(battery_level) min_battery_level,
  34. max(battery_level) max_battery_level
  36. FROM readings r
  38. WHERE r.device_id IN (
  40.     SELECT DISTINCT device_id FROM device_info
  42.     WHERE model = 'pinto' OR model = 'focus'
  44. ) GROUP BY "hour" ORDER BY "hour" ASC LIMIT 12;
  46. hour                   | min_battery_level | max_battery_level
  48. -----------------------+-------------------+-------------------
  50. 2016-11-15 07:00:00-05 |                17 |                99
  52. 2016-11-15 08:00:00-05 |                11 |                98
  54. 2016-11-15 09:00:00-05 |                 6 |                97
  56. 2016-11-15 10:00:00-05 |                 6 |                97
  58. 2016-11-15 11:00:00-05 |                 6 |                97
  60. 2016-11-15 12:00:00-05 |                 6 |                97
  62. 2016-11-15 13:00:00-05 |                 6 |                97
  64. 2016-11-15 14:00:00-05 |                 6 |                98
  66. 2016-11-15 15:00:00-05 |                 6 |               100
  68. 2016-11-15 16:00:00-05 |                 6 |               100
  70. 2016-11-15 17:00:00-05 |                 6 |               100
  72. 2016-11-15 18:00:00-05 |                 6 |               100
  74. (12 rows)

Weather datasets

After importing one of these datasets (weather_small, weather_med, weather_big), you notice a plain PostgreSQL table called locations and a hypertable called conditions. The locations table has metadata about each of the locations, such as its name and environmental type. The conditions hypertable tracks readings of temperature and humidity from those locations. Because hypertables are exposed as a single table, you can query them and join them with the metadata as you would normal SQL tables, as shown in the example queries in this section.

Schemas

  2. Table "public.locations"
  4. Column      | Type | Modifiers
  6. ------------+------+-----------
  8. device_id   | text |
  10. location    | text |
  12. environment | text |
  14. Table "public.conditions"
  16. Column      |           Type           | Modifiers
  18. ------------+--------------------------+-----------
  20. time        | timestamp with time zone | not null
  22. device_id   | text                     |
  24. temperature | double precision         |
  26. humidity    | double precision         |
  28. Indexes:
  30. "conditions_device_id_time_idx" btree (device_id, "time" DESC)
  32. "conditions_time_idx" btree ("time" DESC)

Example queries

Uses weather_med dataset.

Last ten readings

  2. SELECT * FROM conditions c ORDER BY time DESC LIMIT 10;
  4. time                   |     device_id      |    temperature     |      humidity
  6. -----------------------+--------------------+--------------------+--------------------
  8. 2016-12-06 02:58:00-05 | weather-pro-000000 |  84.10000000000034 |  83.70000000000053
  10. 2016-12-06 02:58:00-05 | weather-pro-000001 | 35.999999999999915 |  51.79999999999994
  12. 2016-12-06 02:58:00-05 | weather-pro-000002 |  68.90000000000006 |  63.09999999999999
  14. 2016-12-06 02:58:00-05 | weather-pro-000003 |  83.70000000000041 |  84.69999999999989
  16. 2016-12-06 02:58:00-05 | weather-pro-000004 |  83.10000000000039 |  84.00000000000051
  18. 2016-12-06 02:58:00-05 | weather-pro-000005 |  85.10000000000034 |  81.70000000000017
  20. 2016-12-06 02:58:00-05 | weather-pro-000006 |  61.09999999999999 | 49.800000000000026
  22. 2016-12-06 02:58:00-05 | weather-pro-000007 |   82.9000000000004 |  84.80000000000047
  24. 2016-12-06 02:58:00-05 | weather-pro-000008 | 58.599999999999966 |               40.2
  26. 2016-12-06 02:58:00-05 | weather-pro-000009 | 61.000000000000014 | 49.399999999999906
  28. (10 rows)

Last 10 readings from ‘outside’ locations

  2. SELECT time, c.device_id, location,
  4. trunc(temperature, 2) temperature, trunc(humidity, 2) humidity
  6. FROM conditions c
  8. INNER JOIN locations l ON c.device_id = l.device_id
  10. WHERE l.environment = 'outside'
  12. ORDER BY time DESC LIMIT 10;
  14. time                   |     device_id      |   location    | temperature | humidity
  16. -----------------------+--------------------+---------------+-------------+----------
  18. 2016-12-06 02:58:00-05 | weather-pro-000000 | field-000000  |       84.10 |    83.70
  20. 2016-12-06 02:58:00-05 | weather-pro-000001 | arctic-000000 |       35.99 |    51.79
  22. 2016-12-06 02:58:00-05 | weather-pro-000003 | swamp-000000  |       83.70 |    84.69
  24. 2016-12-06 02:58:00-05 | weather-pro-000004 | field-000001  |       83.10 |    84.00
  26. 2016-12-06 02:58:00-05 | weather-pro-000005 | swamp-000001  |       85.10 |    81.70
  28. 2016-12-06 02:58:00-05 | weather-pro-000007 | field-000002  |       82.90 |    84.80
  30. 2016-12-06 02:58:00-05 | weather-pro-000014 | field-000003  |       84.50 |    83.90
  32. 2016-12-06 02:58:00-05 | weather-pro-000015 | swamp-000002  |       85.50 |    66.00
  34. 2016-12-06 02:58:00-05 | weather-pro-000017 | arctic-000001 |       35.29 |    50.59
  36. 2016-12-06 02:58:00-05 | weather-pro-000019 | arctic-000002 |       36.09 |    48.80
  38. (10 rows)

Hourly average, min, and max temperatures for “field” locations

  2. SELECT date_trunc('hour', time) "hour",
  4. trunc(avg(temperature), 2) avg_temp,
  6. trunc(min(temperature), 2) min_temp,
  8. trunc(max(temperature), 2) max_temp
  10. FROM conditions c
  12. WHERE c.device_id IN (
  14.     SELECT device_id FROM locations
  16.     WHERE location LIKE 'field-%'
  18. ) GROUP BY "hour" ORDER BY "hour" ASC LIMIT 24;
  20. hour                   | avg_temp | min_temp | max_temp
  22. -----------------------+----------+----------+----------
  24. 2016-11-15 07:00:00-05 |    73.80 |    68.00 |    79.09
  26. 2016-11-15 08:00:00-05 |    74.80 |    68.69 |    80.29
  28. 2016-11-15 09:00:00-05 |    75.75 |    69.39 |    81.19
  30. 2016-11-15 10:00:00-05 |    76.75 |    70.09 |    82.29
  32. 2016-11-15 11:00:00-05 |    77.77 |    70.79 |    83.39
  34. 2016-11-15 12:00:00-05 |    78.76 |    71.69 |    84.49
  36. 2016-11-15 13:00:00-05 |    79.73 |    72.69 |    85.29
  38. 2016-11-15 14:00:00-05 |    80.72 |    73.49 |    86.99
  40. 2016-11-15 15:00:00-05 |    81.73 |    74.29 |    88.39
  42. 2016-11-15 16:00:00-05 |    82.70 |    75.09 |    88.89
  44. 2016-11-15 17:00:00-05 |    83.70 |    76.19 |    89.99
  46. 2016-11-15 18:00:00-05 |    84.67 |    77.09 |    90.00
  48. 2016-11-15 19:00:00-05 |    85.64 |    78.19 |    90.00
  50. 2016-11-15 20:00:00-05 |    86.53 |    78.59 |    90.00
  52. 2016-11-15 21:00:00-05 |    86.40 |    78.49 |    90.00
  54. 2016-11-15 22:00:00-05 |    85.39 |    77.29 |    89.30
  56. 2016-11-15 23:00:00-05 |    84.40 |    76.19 |    88.70
  58. 2016-11-16 00:00:00-05 |    83.39 |    75.39 |    87.90
  60. 2016-11-16 01:00:00-05 |    82.40 |    74.39 |    87.10
  62. 2016-11-16 02:00:00-05 |    81.40 |    73.29 |    86.29
  64. 2016-11-16 03:00:00-05 |    80.38 |    71.89 |    85.40
  66. 2016-11-16 04:00:00-05 |    79.41 |    70.59 |    84.40
  68. 2016-11-16 05:00:00-05 |    78.39 |    69.49 |    83.60
  70. 2016-11-16 06:00:00-05 |    78.42 |    69.49 |    84.40
  72. (24 rows)