Influx aggregation query return wrong time

Question

I'm a new influxdb user and my influxDB version is 1.7.1. I have problem with time in influx.

I have a measurement called lv_table, Listing_id and event_type are tags and event_id is field

select *  from "lv_table" limit 10 
name: lv_table
time             Listing_id event_id event_type
----             ---------- -------- ----------
1542711774019000 105202696  4        "leads"
1542711774020000 105497566  66       "view"
1542711774021000 95532296   66       "view"
1542711774021000 98830991   1        "leads"
1542711774022000 105456186  66       "view"
1542711774023000 94326731   66       "view"
1542711774025000 104584666  66       "view"
1542711774028000 105603346  66       "view"
1542711774035000 99913981   66       "view"
1542711774037000 105430516  3        "leads"

but when I use "precision RFC3339" in cli, that query return date in 1970 year like '1970-01-18T20:31:51.774019Z' but correct time is '2018-11-20T20:31:51.774019Z'

> precision RFC3339
> select *  from "lv_table" limit 10 
name: lv_table
time                        Listing_id event_id event_type
----                        ---------- -------- ----------
1970-01-18T20:31:51.774019Z 105202696  4        "leads"
1970-01-18T20:31:51.77402Z  105497566  66       "view"
1970-01-18T20:31:51.774021Z 95532296   66       "view"
1970-01-18T20:31:51.774021Z 98830991   1        "leads"
1970-01-18T20:31:51.774022Z 105456186  66       "view"
1970-01-18T20:31:51.774023Z 94326731   66       "view"
1970-01-18T20:31:51.774025Z 104584666  66       "view"
1970-01-18T20:31:51.774028Z 105603346  66       "view"
1970-01-18T20:31:51.774035Z 99913981   66       "view"
1970-01-18T20:31:51.774037Z 105430516  1        "leads"
>

What's the problem and how can I fix it?

In aggregation query it gets worth ... and show time in 2016 but it should be in 2018-11-20 date

1468800000000000 = Monday, July 18, 2016 12:00:00 AM

select count(*) as count_leads  from "lv_table" where "event_type" = '"leads"' and time < 1542745800000000 group by Listing_id,time(1d)
>...
name: lv_table
tags: Listing_id=99965506
time             count_leads_event_id
----             --------------------
1468800000000000 1

name: lv_table
tags: Listing_id=99965771
time             count_leads_event_id
----             --------------------
1468800000000000 2

name: lv_table
tags: Listing_id=99966146
time             count_leads_event_id
----             --------------------
1468800000000000 1

name: lv_table
tags: Listing_id=99966736
time             count_leads_event_id
----             --------------------
1468800000000000 3
...

If I don't use group by on time, time will be equal 0

Yuri Lachin Yuri Lachin · Accepted Answer · 2018-11-22T16:39:07

Influxdb stores timestamps internally as nanoseconds since Jan 1 1970, so your first datapoint is actually interpreted as: 1542711774019000 ns -> 1542711 s -> Sun Jan 18 20:31:51 UTC 1970 and this is what you see when use "precision RFC3339" in cli.

Not sure how you got

1468800000000000 = Monday, July 18, 2016 12:00:00 AM

1468800000000000 ns -> 1468800 s -> Sun Jan 18 00:00:00 UTC 1970 This is your first datapoint timestamp rounded to 1 day.

You didn't describe how your put data into table. In Influx HTTP API you can specify precision of input timestamp data with an optional query parameter (see InfluxDB HTTP API reference).

Precision setting in influx tells how to treat input timestamp data. Timestamp value on input is always integer (not rfc3339 string). And this integer is interpreted according to precision setting.

precision=[ns,u,ms,s,m,h] Optional Sets the precision for the supplied Unix time values. InfluxDB assumes that timestamps are in nanoseconds if you do not specify precision (see Influx docs here)

It also affects output format for query result. See examples below:

```

> precision ns
> insert demo value="precisionNS TS treated as nanoseconds" 1543220939000000000
> precision s
> insert demo value="precisionS TS treated as seconds" 1543220940
> precision ms
> insert demo value="precisionMS TS treated as ms" 1543220940123
> precision rfc3339
> select * from demo
name: demo
time                           value
----                           -----
2018-11-26T08:28:59Z           precisionNS TS treated as nanoseconds
2018-11-26T08:28:59.123456789Z precisionNS TS treated as nanoseconds
2018-11-26T08:29:00Z           precisionS TS treated as seconds
2018-11-26T08:29:00.123Z       precisionMS TS treated as ms
> 
> precision s
> select * from demo
name: demo
time       value
----       -----
1543220939 precisionNS TS treated as nanoseconds
1543220939 precisionNS TS treated as nanoseconds
1543220940 precisionS TS treated as seconds
1543220940 precisionMS TS treated as ms
> 
> precision ns
> select * from demo
name: demo
time                value
----                -----
1543220939000000000 precisionNS TS treated as nanoseconds
1543220939123456789 precisionNS TS treated as nanoseconds
1543220940000000000 precisionS TS treated as seconds
1543220940123000000 precisionMS TS treated as ms

```

Influx aggregation query return wrong time

1 Answers