1

I've been trying to figure out how to generate the same Unix epoch time that I see within InfluxDB next to measurement entries.

Let me start by saying I am trying to use the same date and time in all tests:

April 01, 2017 at 2:00AM CDT

If I view a measurement in InfluxDB, I see time stamps such as:

1491030000000000000

If I view that measurement in InfluxDB using the -precision rfc3339 it appears as:

2017-04-01T07:00:00Z

So I can see that InfluxDB used UTC

I cannot seem to generate that same timestamp through Python, however.

For instance, I've tried a few different ways:

>>> calendar.timegm(time.strptime('04/01/2017 02:00:00', '%m/%d/%Y %H:%M:%S'))
1491012000

>>> calendar.timegm(time.strptime('04/01/2017 07:00:00', '%m/%d/%Y %H:%M:%S'))
1491030000

>>> t = datetime.datetime(2017,04,01,02,00,00)
>>> print "Epoch Seconds:", time.mktime(t.timetuple())
Epoch Seconds: 1491030000.0

The last two samples above at least appear to give me the same number, but it's much shorter than what InfluxDB has. I am assuming that is related to the precision, InfluxDB does things down to nanosecond I think?

Python Result: 1491030000
Influx Result: 1491030000000000000

If I try to enter a measurement into InfluxDB using the result Python gives me it ends up showing as:

1491030000 = 1970-01-01T00:00:01.49103Z

So I have to add on the extra nine 0's.

I suppose there are a few ways to do this programmatically within Python if it's as simple as adding on nine 0's to the result. But I would like to know why I can't seem to generate the same precision level in just one conversion.

I have a CSV file with tons of old timestamps that are simply, "4/1/17 2:00". Every day at 2 am there is a measurement.

I need to be able to convert that to the proper format that InfluxDB needs "1491030000000000000" to insert all these old measurements.

A better understanding of what is going on and why is more important than how to programmatically solve this in Python. Although I would be grateful to responses that can do both; explain the issue and what I am seeing and why as well as ideas on how to take a CSV with one column that contains time stamps that appear as "4/1/17 2:00" and convert them to timestamps that appear as "1491030000000000000" either in a separate file or in a second column.

ddevalco
  • 1,209
  • 10
  • 20

2 Answers2

2

InfluxDB can be told to return epoch timestamps in second precision in order to work more easily with tools/libraries that do not support nanosecond precision out of the box, like Python.

Set epoch=s in query parameters to enable this.

See influx HTTP API timestamp format documentation.

danny
  • 5,140
  • 1
  • 19
  • 31
0

Something like this should work to solve your current problem. I didn't have a test csv to try this on, but it will likely work for you. It will take whatever csv file you put where "old.csv" is and create a second csv with the timestamp in nanoseconds.

import time
import datetime
import csv

def convertToNano(date):
    s = date
    secondsTimestamp = time.mktime(datetime.datetime.strptime(s, "%d/%m/%y %H:%M").timetuple())
    nanoTimestamp = str(secondsTimestamp).replace(".0", "000000000")
    return nanoTimestamp
with open('old.csv', 'rb') as old_csv:
    csv_reader = csv.reader(old_csv)
    with open('new.csv', 'wb') as new_csv:
        csv_writer = csv.writer(new_csv)
        for i, row in enumerate(csv_reader):
            if i != 0:
                # Put whatever rows the data appears in and the row you want the data to go in here
                row.append(convertToNano(row[<location of date in the row>]))
                csv_writer.writerow(row)

As to why this is happening, after reading this it seems like you aren't the only one getting frustrated by this issue. It seems as though influxdb just happens to be using a different type of precision then most python modules. I didn't really see any way to get around it than doing the string manipulation of the date conversion unfortunately.

Benjamin Commet
  • 350
  • 3
  • 8