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?
Hi,
My account was on hold for a week. So couldn’t respond earlier. Here are my observations:
Exp1:
> select last(openingTime) from store
name: store
time last
---- ----
1558588804795889058 1000
Above time value is correct
Exp2:
> 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
and
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.
Or
You could use the NOAA dataset and run
SELECT last(*) FROM "NOAA_water_database"."autogen"."h2o_temperature"
name: h2o_temperature
You
time last_degrees
---- ------------
2015-09-18T21:42:00Z 67
You can follow these instructions to get the dataset
