0

I use InfluxDB as a time series database. It is quite nice to play with that type of infrastructure. However, I faced an annoying problem I don't know how to solve it. It seems hard to query the database when it is with less than second precision because the time variation is a bit skewed. Originally I ask to write with 0.5 second precision, but I did not get that exact precision in the database.

> select price from TSLA_0p5s limit 100
name: TSLA_0p5s
time                midprice
----                --------
2015-07-15T09:00:00Z        267.1
2015-07-15T09:00:00.499500032Z  267.1
2015-07-15T09:00:01Z        267.1
2015-07-15T09:00:01.499500032Z  267.1
2015-07-15T09:00:02Z        267.1
2015-07-15T09:00:02.499500032Z  267.1
2015-07-15T09:00:03Z        267.1
2015-07-15T09:00:03.499500032Z  267.1
2015-07-15T09:00:04Z        267.1
2015-07-15T09:00:04.499500032Z  267.1
2015-07-15T09:00:05Z        267.1
2015-07-15T09:00:05.499500032Z  267.1
2015-07-15T09:00:06Z        267.1
2015-07-15T09:00:06.499500032Z  267.1
2015-07-15T09:00:07Z        267.1
2015-07-15T09:00:07.499500032Z  267.1
2015-07-15T09:00:08Z        267.1
2015-07-15T09:00:08.499500032Z  267.1
2015-07-15T09:00:09Z        267.1
2015-07-15T09:00:09.499500032Z  267.1
2015-07-15T09:00:10Z        267.1
2015-07-15T09:00:10.499500032Z  267.1
2015-07-15T09:00:11Z        267.1
2015-07-15T09:00:11.499500032Z  267.1
2015-07-15T09:00:12Z        267.1
2015-07-15T09:00:12.499500032Z  267.1

In the above sample from the database, you can see that the variation between timestamps are not regular. When I wrote the data into the database with influxdb-python, the timedelta was unique and set to 0.5 second. Here you can notice that

2015-07-15T09:00:00.499500032Z - 2015-07-15T09:00:00Z = 0.49950032 second (**)

and

2015-07-15T09:00:01Z - 2015-07-15T09:00:00.499500032Z = 0.50049968 second (***)

from influxdb import InfluxDBClient

client = InfluxDBClient("localhost", 8086, username, password, "data")

delta_intraday = timedelta(seconds=0.5)
current_time = datetime.datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
next_time = current_time + delta_intraday

start_time = datetime.datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
end_time = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")

def generateDataFromDb():
    while next_time < end_time:

        fetch_items = client.query(
            "select * from "
            + DB_NAME
            + " WHERE time >= '"
            + current_time.isoformat().replace("T", " ")
            + "' AND time <= '"
            + next_time.isoformat().replace("T", " ")
            + "';"
        )
        fetch_points = fetch_items.get_points()

        data = []
        data.extend(ts_fetch_items_gen(fetch_points))

        data = np.array(data)  
        data = ts_extract(data, keys)

        yield np.array(data)

        current_time = next_time
        next_time = next_time + delta_intraday

dataGenerator = generateDataFromDb(FROM_DAY, TO_DAY, delta=1)

for i, data in enumerate(dataGenerator):
    print("{}- The datashape is {}".format(i, data.shape))

Here don't bother with ts_fetch_items_gen() and ts_extract().

The output from the above code is

0- The datashape is (2, 103)
1- The datashape is (103,)
2- The datashape is (2, 103)
3- The datashape is (103,)
4- The datashape is (2, 103)
5- The datashape is (103,)
6- The datashape is (2, 103)
7- The datashape is (103,)
8- The datashape is (2, 103)
9- The datashape is (103,)
10- The datashape is (2, 103)
11- The datashape is (103,)
12- The datashape is (2, 103)
13- The datashape is (103,)
14- The datashape is (2, 103)
15- The datashape is (103,)
16- The datashape is (2, 103)
17- The datashape is (103,)
18- The datashape is (2, 103)
19- The datashape is (103,)
...

Because of (**) and (***), I got two different datashapes in the above output, i.e. (103,) and (2, 103).

Is there a way to round the timestamps to the nearest tenth, i.e 0.49950032 --> 0.5, before querying the database?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.