# Querying a time series

Reading a time series is a `POST` with a JSON body, like every other OpenGate query:

```bash
POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/data
```

Copy this and change the identifiers:

```bash
curl --request POST \
     --header "X-ApiKey: <your-api-key>" \
     --header "Content-Type: application/json" \
     --data '{"filter": {"eq": {"Prov Identifier": "MyDevice1"}}, "sort": "EntityAscBucketDesc"}' \
     https://api.opengate.es/north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/data
```

The response is a `columns` array naming the fields and a `data` array of rows in that order:

```json
{
  "page": { "number": 26 },
  "columns": ["Bucket id", "Prov identifier", "Manufacturer", "ICC", "Daily sent bytes", "Daily received bytes", "Last presence", "Average Signal strength"],
  "data": [
    ["2021-04-06T12:00:00.000Z", "MyDevice1", "OpenGate", "icc1", 23500, 532, "IP", 75],
    ["2021-04-06T12:01:00.000Z", "MyDevice1", "OpenGate", "icc1", 3500, 14532, "IP", 65]
  ]
}
```

## The request body

| Clause | Accepts |
|---|---|
| `filter` | The standard [operators](../../data_lake/features/filtering/), keyed by `bucketColumn`, `identifierColumn`, `columns.name` or `context.name` |
| `sort` | A **string**: the `identifier` of one of the sorts declared in the time series, not a list of fields |
| `select` | The same keys as `filter` |
| `limit` | `start` and `size`, as everywhere else |

Two things differ from a plain Data Lake search, and both come from the time series being pre-computed:
you can only filter on columns declared filterable, and you can only sort by sorts declared in the
definition. See [Defining a time series](../defining/) for how those are declared, and
[Query dialects](../../query_dialects/) for the full comparison.

### Column order when you omit `select`

`columns` tells you the order, so read values off it rather than hardcoding positions. If you do depend on
the order, it is:

1. The `bucketColumn`, holding the end date of the bucket
2. The `identifierColumn`, holding `provision.administration.identifier._current.value`
3. The `context` columns
4. The aggregated `columns`

### Asking for a sort

You do not compose an ordering in the request. You name one that already exists:

```json
{ "filter": {}, "sort": "bucket_id_desc" }
```

Valid values are the `identifier` of any sort in the time series definition, **plus the automatically
exposed reverse of each one**. So a definition declaring `bucket_id_desc` gives you both directions without
declaring the second.

Read the definition to see what is available — `GET` the time series, or use `expand=sorts`, and the `sorts`
list comes back with the derived ones included. There is no fixed limit on how many sorts a definition can
hold; the constraint is the optimization unit budget, described in
[Defining a time series](../defining/#filtering-and-sorting-limits).

## Pagination and CSV

The response format changes what `limit` means, which catches people out:

| Body | JSON | CSV |
|---|---|---|
| `{"filter": {}, "limit": {"size": 500, "start": 1}}` | 500 rows from row 1 | 500 rows from row 1 |
| `{"filter": {}}` | Configured default page | **Everything** |
| `{"filter": {}, "limit": {}}` | Configured default page | **Error** — an incomplete `limit` is rejected |

CSV retrieval also **turns sorting off**, deliberately, so that large exports stay fast. If you need ordered
output, sort downstream or read JSON.

{{% notice style="warning" title="Complete retrieval is expensive" icon="triangle-exclamation" %}}
Omitting `limit` in CSV mode downloads the whole time series. Page it unless you truly want everything.
{{% /notice %}}

The CSV formatting itself — quoting character, escape character, end-of-line sequence and how nulls are
represented — is set through HTTP header options, and you are responsible for the result being well-formed.
The exact header names are not currently published, so ask your platform contact for them.

## Aggregated read: one row per device

Besides reading buckets, you can collapse **every bucket of a device into a single row**:

```bash
POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/dataset
```

Here `select.columns` describes the output variables, each with the source `column`, an `alias` for the
output name, and the `aggregation` function to apply across buckets:

```json
{
  "filter": {
    "gt": { "bucket_id": "device_200" }
  },
  "limit": { "start": 1, "size": 50 },
  "select": {
    "columns": [
      { "column": "temperature", "alias": "first", "aggregation": "FIRST" },
      { "column": "temperature", "alias": "last",  "aggregation": "LAST" },
      { "column": "temperature", "alias": "avg",   "aggregation": "AVG" },
      { "column": "temperature", "alias": "max",   "aggregation": "MAX" },
      { "column": "temperature", "alias": "min",   "aggregation": "MIN" },
      { "column": "cpu",         "alias": "p_avg", "aggregation": "AVG" },
      { "column": "cpu",         "alias": "p_count", "aggregation": "COUNT" }
    ]
  }
}
```

`filter` and `limit` behave as above, and CSV output is available too. Two rules are specific to this
endpoint:

- The output is **always sorted ascending by `identifierColumn`**.
- The `identifierColumn` is **always included**, added as the first column if you did not ask for it.

## Parquet export

For bulk analytical work, a time series can be exported to a Parquet file:

```bash
POST /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/export
GET  /north/v80/timeseries/provision/organizations/{organizationName}/{identifier}/export
```

`POST` starts the export, `GET` reports the state of the current one. The output order is decided
internally and cannot be changed.