How to store dates in InfluxDB

Question

I need to develop an InfluxDB Time Series. The time series needs to contain the following information:

time - The time the data was recorded. This will use the InfluxDB Time Field.
value - The value for the time series. A simple integer.
date - A separate date associated with the value. This date has nothing to do with the "time" field. It will be used to help narrow down queries.

My current line of thinking is to save the above "date" field as a separate "column" in the time series so that I can use the "where" clause to filter the data using that date. However I am struggling with how to represent that. Does InfluxDB support any kind of date or date/time fields? For the "time" field it seems to just use milliseconds. However if I try the same in a field with a different name, then the normal time queries don't work. So for example:

select * from myseries where time > now() - 1d

The above query will work just fine.

vs

select * from myseries where date > now() - 1d

This query will fail with an error because it doesn't seem to know how to treat "date" as a time value.

Is there a better representation for dates in this scenario?

Jason Jason · Accepted Answer · 2016-09-09T07:36:18

InfluxDB data types can be only be one of: floats, ints, bools, or strings. The time field is a special exception.

You can use integers representing count-since-epoch for the date field. Nice convenient functions like now() don't seem work for that though (using v0.13):

insert test_dates date_int=1573405622000000000i,desc="years from now"
insert test_dates date_int=1373405661000000000i,desc="years ago"

Testing that with now()

select * from test_dates where date_int > now()

gives:

name: test_dates

time                    date_int                desc 
1473404302801938064     1573405622000000000     years from now
1473404315927493772     1373405661000000000     years ago

And:

select * from test_dates where date_int < now()

gives:

name: test_dates

time                    date_int                desc
1473462286404084162     1573405622000000000     years from now
1473462286408231540     1373405661000000000     years ago

Seems every date_int is somehow both greater than and less than now()

So the comparison isn't a syntax error if you use integers, but doesn't work the way we'd like.

One way to solve this is to create your own date-to-int conversion in the front-end app. Then a date comparison on the front-end is an int comparison within InfluxDB. Clunky, but those are the data types we have.

The date as stored in InfluxDB could be a single epoch-based int, or store separate int fields for year, month, day within InfluxDB. The queries are bigger and slower in the latter case, but it's easier to read and debug.

Good luck!

How to store dates in InfluxDB

1 Answers