> For the complete documentation index, see [llms.txt](https://docs.gridstatus.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.gridstatus.io/developers/snowflake-guides/resampling-time-series-data.md).

# Resampling Time-Series Data

The example below shows how to resample data to longer intervals using tables available in our Snowflake Marketplace Listing. You can use the same approach with other datasets, such as converting 5-minute data to hourly averages. Choose an aggregation that fits the data: for example, average prices or sum interval energy.

## Hourly Average Prices

We can calculate hourly average settlement point prices (SPP) from [ERCOT SPP Real Time 15 Min](https://www.gridstatus.io/datasets/ercot_spp_real_time_15_min) by averaging the four 15-minute prices in each hour. Prices are in $/MWh.

This query returns all settlement points starting at the beginning of the hour 24 hours ago, including the current partial hour.

{% hint style="info" %}
Change the `WHERE` clause to select the data you need. Calculating averages over the entire history of a dataset can be expensive, so you may want to filter by a different start time or a specific location, such as `AND location = 'HB_NORTH'`. Keep time filters on hour boundaries to include the full hour.
{% endhint %}

```sql
WITH hourly AS (
  SELECT
    DATE_TRUNC('hour', interval_start_utc) AS interval_start_utc,
    location,
    location_type,
    AVG(spp) AS spp
  FROM
    ercot_spp_real_time_15_min
  GROUP BY
    DATE_TRUNC('hour', interval_start_utc),
    location,
    location_type
)
SELECT
  interval_start_utc,
  DATEADD('hour', 1, interval_start_utc) AS interval_end_utc,
  CONVERT_TIMEZONE('America/Chicago', interval_start_utc) AS interval_start_local,
  CONVERT_TIMEZONE(
    'America/Chicago', DATEADD('hour', 1, interval_start_utc)
  ) AS interval_end_local,
  location,
  location_type,
  spp
FROM
  hourly
WHERE
  interval_start_utc >= DATEADD(
    'hour', -24, DATE_TRUNC('hour', CURRENT_TIMESTAMP())
  )
ORDER BY
  interval_start_utc,
  location,
  location_type;
```

The query averages each location and location type separately. It groups intervals into UTC hours, then converts the timestamps to ERCOT's local time (`America/Chicago`). This keeps the repeated hour when daylight saving time ends separate, with a different UTC offset for each occurrence.

`AVG(spp)` ignores null prices. If an hour has fewer than four prices, the query averages the available values. Hours with no source rows are not returned.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.gridstatus.io/developers/snowflake-guides/resampling-time-series-data.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.