VictoriaMetrics: last_over_time not working as expected

Describe the bug

last_over_time not working as expected

To Reproduce

following example shows that last_over_time returns 1607535665 which is mostly the current time.

/api/v1/query?query=last_over_time(vital_hr{user_id="5fce28b3d77c0500087f3d24"}[24h])

{
  "status": "success",
  "data": {
    "resultType": "vector",
    "result": [
      {
        "metric": {
          "__name__": "vital_hr",
          "device_id": "5fce28a9d77c0500087f3d1f",
          "user_id": "5fce28a5d77c0500087f3d1c"
        },
        "value": [
          1607535665,
          "78"
        ]
      }
    ]
  }
}

raw export shows timestamp as 1607529090505 which is correct.

/api/v1/export?match[]=vital_hr{user_id="5fce28b3d77c0500087f3d24"}

{
  "metric": {
    "__name__": "vital_hr",
    "device_id": "5fce28a9d77c0500087f3d1f",
    "user_id": "5fce28a5d77c0500087f3d1c"
  },
  "values": [
    78
  ],
  "timestamps": [
    1607529090505
  ]
}

Expected behavior

last_over_time should return latest timestamp for the value not the current timestamp.

Version

using victoria-metrics-single helm chart version 0.6.6 (app version: 1.49.0)

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 20

Commits related to this issue

rollup.go: add `timestamp_with_name(m[d])` function See https://github.com/VictoriaMetrics/VictoriaMetrics/issues/949#issuecomment-995222388 for details — committed to VictoriaMetrics/metricsql by valyala 3 years ago
app/vmselect/promql: add `timestamp_with_name(m[d])` function This function works the same as `timestamp()`, but doesn't remove source time series names. See https://github.com/VictoriaMetrics/Victo... — committed to VictoriaMetrics/VictoriaMetrics by valyala 3 years ago
app/vmselect/promql: add `timestamp_with_name(m[d])` function This function works the same as `timestamp()`, but doesn't remove source time series names. See https://github.com/VictoriaMetrics/Victo... — committed to VictoriaMetrics/VictoriaMetrics by valyala 3 years ago
app/vmselect/promql: implement `keep_metric_names` modifier for transform and rollup functions Updates https://github.com/VictoriaMetrics/VictoriaMetrics/issues/949 — committed to VictoriaMetrics/VictoriaMetrics by valyala 2 years ago
app/vmselect/promql: implement `keep_metric_names` modifier for transform and rollup functions Updates https://github.com/VictoriaMetrics/VictoriaMetrics/issues/949 — committed to VictoriaMetrics/VictoriaMetrics by valyala 2 years ago
app/vmselect/promql: properly keep metric names when optimized path is used for aggregate function calculations For example, `sum(rate(...) keep_metric_names) by (__name__)` didn't leave the original... — committed to VictoriaMetrics/VictoriaMetrics by valyala 2 years ago
app/vmselect/promql: properly keep metric names when optimized path is used for aggregate function calculations For example, `sum(rate(...) keep_metric_names) by (__name__)` didn't leave the original... — committed to VictoriaMetrics/VictoriaMetrics by valyala 2 years ago
app/vmselect/promql: add `timestamp_with_name(m[d])` function This function works the same as `timestamp()`, but doesn't remove source time series names. See https://github.com/VictoriaMetrics/Victo... — committed to viperstars/vmquerier by valyala 3 years ago
app/vmselect/promql: implement `keep_metric_names` modifier for transform and rollup functions Updates https://github.com/VictoriaMetrics/VictoriaMetrics/issues/949 — committed to viperstars/vmquerier by valyala 2 years ago
app/vmselect/promql: properly keep metric names when optimized path is used for aggregate function calculations For example, `sum(rate(...) keep_metric_names) by (__name__)` didn't leave the original... — committed to viperstars/vmquerier by valyala 2 years ago

Most upvoted comments

FYI, the keep_metric_names modifier can be applied to all the rollup functions and transform functions starting from VictoriaMetrics v1.72.0.

Closing this issue as fixed, since there are a few workarounds exist.

valyala on Jan 18, 2022

Aha, even better. Love it. Thanks.

mactyr on Dec 15, 2021

I’m encountering this same need to know the latest value for a number of metrics along with the actual timestamps of the latest values – this comes up quite often in my IoT application. For now, I am following the suggestion to iterate over the results of last_over_time() to get each metric’s timestamp() but it is pretty awkward and, I imagine, slower than getting the desired data in one query if such a thing were possible. (It doesn’t seem like any of the later suggestions worked reliably for the issue reporter.)

Could MetricsQL have an extension like last_over_time_with_timestamp() that behaves like last_over_time() but returns the actual timestamps of the last points?

mactyr on Dec 9, 2021

Is it not simply possible to get the latest value and timestamp in a single call?

It is should be possible. Try the following MetricsQL query:

(
  alias(timestamp(vital_hr{user_id="5fce28b3d77c0500087f3d24"}[24h]), "timestamp"),
  last_over_time(vital_hr{user_id="5fce28b3d77c0500087f3d24"}[24h]),
)

It will return vital_hr and timestamp time series in a single response. Then you can match the returned time series by their labels.

valyala on Dec 11, 2020

This is expected behavior.

VictoriaMetrics follows Prometheus data model for queries sent to /api/v1/query and /api/v1/query_range handlers. This means the following:

a single calculated datapoint is returned from /api/v1/query with timestamp equal to time query arg. If time isn’t specified, then the current time is used.
fixed number of calculated datapoints is returned from /api/v1/query_range on a time range specified by start and end query args. The distance between the returned datapoints always equals to step.

See more details about this behavior at https://prometheus.io/docs/prometheus/latest/querying/basics/#staleness .

If you want actual timestamp for the last value, then either use /api/v1/export API, which returns all the raw datapoints stored in the database or use timestamp function from MetricsQL: timestamp(vital_hr{user_id="5fce28b3d77c0500087f3d24"}[24h]) should return actual timestamp for the last value for the given time series.

valyala on Dec 11, 2020