Skip to content
Start free
Start free
Explore

Try CrateDB Live: Explore Queries

Scenario: Weather Monitoring
  • 1. Choose Scenario
  • 2. Get Ready
  • 3. Run CrateDB
  • 4. Import Data
  • 5. Explore Queries
  • 6. More Queries
  • 7. Connect
  • 8. Next Steps

A. Generic Queries

A1. What is the time range?

 
SELECT
  min(measurement_time) AS start_ts,
  max(measurement_time) AS end_ts
FROM
  demo.climate_data;

CrateDB stores timestamps in UTC without timezone information, represented as milliseconds since the Unix epoch (1970-01-01T00:00:00Z). The console displays both the numeric value and human-readable format.

A2. Distribution of temperature

 
SELECT
  min(data['temperature']) -273.15 AS min_temp,
  avg(data['temperature']) -273.15 AS avg_temp,
  max(data['temperature']) -273.15 AS max_temp
FROM
  demo.climate_data;

This aggregation query returns minimum, maximum, and average temperatures across all data. The AS keyword creates aliases for clarity. The -273.15 converts from Kelvin to Celsius.

A3. Min and Max temperature per day

 
SELECT
  date_trunc('day', measurement_time) AS day,
  min(data['temperature']) -273.15 AS min_temp,
  max(data['temperature']) -273.15 AS max_temp
FROM
  demo.climate_data
GROUP BY
  date_trunc('day', measurement_time)
ORDER BY
  date_trunc('day', measurement_time);

The date_trunc function truncates timestamps to a specified precision, enabling per-day aggregations.

A4. Ten hottest points

 
SELECT
  measurement_time,
  geo_location,
  data['temperature'] -273.15 AS temp
FROM
  demo.climate_data
ORDER BY
  temp DESC
LIMIT
  10;

Uses ORDER BY for descending temperature sort and LIMIT to return only 10 rows. Geo_location returns coordinates as a GEO_POINT.

A5. Wind speed calculations

 
SELECT
  min(sqrt(power(data['u10'], 2) + power(data['v10'], 2))) AS min_ws,
  avg(sqrt(power(data['u10'], 2) + power(data['v10'], 2))) AS avg_ws,
  max(sqrt(power(data['u10'], 2) + power(data['v10'], 2))) AS max_ws
FROM demo.climate_data;

Uses Pythagoras' theorem with power and sqrt functions to calculate wind speeds in metres per second.

A6. Improve with Common Table Expression

 
WITH wind_speed AS (
    SELECT
        sqrt(power(data['u10'], 2) + power(data['v10'], 2)) AS ws
    FROM demo.climate_data
)
SELECT
    MIN(ws) AS min_ws,
    AVG(ws) AS avg_ws,
    MAX(ws) AS max_ws
FROM wind_speed;

A CTE (common table expression) calculates the mathematical expression once, improving efficiency.

A7. Top 10 windiest observations

 
SELECT
  measurement_time,
  geo_location,
  sqrt(power(data['u10'], 2) + power(data['v10'], 2)) AS wind_speed
FROM
  demo.climate_data
ORDER BY
  wind_speed DESC
LIMIT 10;

A8. Wind direction calculated using trigonometry

 
SELECT
  date_trunc('hour', measurement_time) AS hour,
  avg(sqrt(power(data['u10'], 2) + power(data['v10'], 2))) AS avg_speed,
  avg(
    mod(degrees(atan2(data['u10'], data['v10'])) + 360, 360)
  ) AS avg_dir_deg
FROM demo.climate_data
GROUP BY date_trunc('hour', measurement_time)
ORDER BY date_trunc('hour', measurement_time);

Uses trigonometric functions (ATAN2, DEGREES) and MOD to calculate wind direction in degrees (0-360).