InfluxDB: Time is getting reset to 0 when querying multiple fields using last()

I am trying to get latest entry for a measurement which contains 2 fields and 1 tag. I am getting the field values and tag but the time comes out as 0. I am using InfluxDB 1.7.6

Below is the measurement that I have created:
name: store
time closingTime openingTime storeId
---- ---------------- ----------------- -------
1558531688045098763 2100 900 store1
1558531688045098763 2200 1000 store3
1558531688045098763 2100 900 store2

When I run the query:
select last(*) from store

I get the result as:
name: store
time last_closingTime last_openingTime
---- ---------------- ----------------
0 2200 1000

The time value is coming out as 0

If I run the query as:
select last(openingTime) from store
then the time value is proper.

Why is the time getting set to 0 when I query for multiple fields?

Hello @karthy,

Thanks for your question. I’m not sure. When I do
SELECT last(*) FROM "telegraf"."autogen"."cpu" WHERE time > now() - 5m
I get the correct timestamp and 10 output fields as expected. Does that query work for you?


My account was on hold for a week. So couldn’t respond earlier. Here are my observations:

> select last(openingTime) from store
name: store
time                last
----                ----
1558588804795889058 1000

Above time value is correct


> select last(*) from store where time > (now() - 5d)
name: store
time                last_closingTime last_openingTime
----                ---------------- ----------------
1558505585465654313 2100             1000

Time value is wrong. If I re-run the same command again:

> select last(*) from store where time > (now() - 5d)
name: store
time                last_closingTime last_openingTime
----                ---------------- ----------------
1558505587073732722 2100             1000

The time value is different.

Thanks for including that info!

Hmm when I use a Epoch Converter,
I notice that your dates are:
Saturday, September 15, 6908 9:49:06.565 PM
Saturday, September 15, 6908 9:51:47.373 PM

It looks like last() is working correctly since it’s grabbing the most recent value each time you run it, but maybe your data collection is off?

How are you collecting data? Do you have a sample dataset I can share?

To double check whether or not last() is working correctly on your machine, try collecting system stats and running the query I included before.

The time is in nanoseconds - if you just take the seconds part for epoch conversion it is proper. I am testing with a sample data. My entire dataset is below:

time                closingTime openingTime storeId timezone
----                ----------- ----------- ------- --------
1558588762171803698 2100        915         store1  Asia/Kolkata
1558588762171803698 2100        1015        store3  America/Los_Angeles
1558588762171803698 2130        945         store2  Asia/Kolkata
1558588804795889058 2100        1000        store3  America/Los_Angeles
1558588804795889058 2130        930         store2  Asia/Kolkata
1558588804795889058 2100        900         store1  Asia/Kolkata

Hello @karthy,

I’m getting weird behavior too. Last(*) is returning the timestamp at submission time. Specifying time > (now()-5d) shouldn’t work at all since your timestamps are really odd. Last(*) works great for me for historical data with proper timestamps (in the same millennia. Although I agree, theoretically that shouldn’t cause problems. You could create an issue?). I would try fixing your timestamps or verifying that you get the expected behavior with different data.

You could try the query I wrote above using the system stats data and telegraf.


You could use the NOAA dataset and run

SELECT last(*) FROM "NOAA_water_database"."autogen"."h2o_temperature"
name: h2o_temperature
time                 last_degrees
----                 ------------
2015-09-18T21:42:00Z 67

You can follow these instructions to get the dataset