Getting incorrect time from InfluxQL query

InfluxDB 1 InfluxQL
1

I have an influxQL query:

SELECT sum(“metrics written”) FROM (
SELECT max(“metrics_written”) - min(“metrics_written”) as “metrics written”
FROM “tsesre_gms”.“anonymous”.“internal_write”
WHERE time > now() - 5m AND “alias” =~ /^output_.*_cp_v1$/
GROUP BY “alias”, “cp_node”
)

When i ran this query, the timestamp which i am getting with the result is "01/01/1970 05:30:00
". I know this is because influx is using epoch 0 because of the use of aggregate functions. But i am not sure how to get current timestamp with this.

I tried adding “time()” with the GROUP BY clause, but still i am getting the same result.

SELECT sum(“metrics written”) FROM (
SELECT max(“metrics_written”) - min(“metrics_written”) as “metrics written”
FROM “tsesre_gms”.“anonymous”.“internal_write”
WHERE time > now() - 5m AND “alias” =~ /^output_.*_cp_v1$/
GROUP BY “alias”, “cp_node”, “time(5m)”
)

Would appreciate if someone can help me with this.

Thanks.

2

Hello @Rishav_Kumar_Jha,

Can you try something like:

SELECT SUM("metrics_written_difference") AS "total_metrics_written", now() AS "current_timestamp"
FROM (
    SELECT 
        SUM(“metrics written”) AS "sum",
        MAX("metrics_written") - MIN("metrics_written") AS "metrics_written_difference"
    FROM "tsesre_gms"."anonymous"."internal_write"
    WHERE time > now() - 5m AND "alias" =~ /^output_.*_cp_v1$/
    GROUP BY "alias", "cp_node", time(5m)
)
3

based on the documentation InfluxQL date and time functions | InfluxDB Cloud Serverless Documentation now() function should only be used with WHERE clause.

4

@Rishav_Kumar_Jha,
Thanks. Hmm l didn’t realize you were using Cloud Severless and SQL, based off your tags I assumed you were using InfluxQL.
Are you using SQL or InfluxQL?

5

@Rishav_Kumar_Jha PS what are you doing with InfluxDB? I love to learn about what users are getting up to.

6

@Anaisdg
We are planning to create a pipeline using Telegraf agent at various sites and store the metrics from each site at a single place using influxdb.
Clients will be offloading their metrics either via HTTP or MQTT.

1 Like