Converting timestamps to date/time from an export

InfluxDB 1
1

I am in the process of migrating my data from Influx1.8.1 to MySQL, and having difficulty with the timestamps. I have managed to export the data to csv files using the suggested command

influx -database ‘name_of_database’ -execute ‘SELECT * FROM table’ -format csv > export.csv

but then get a timestamp which looks as it does below.

screenshot

I have then read that this is epoch nanoseconds (from this article ) but am not sure if this is still the case. Is there a way that I can convert them to a more legible date and time format ? Either by re-exporting them with a different “precision” or some kind of formula that I can apply to convert them into a date and time field ? Also reading the information on another previous post , the suggested solution doesn’t work at all. Any help would be greatly appreciated

2

That’s epoch time in nanoseconds

epoch_time

Epoch time is the amount of time that has elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.

By default, InfluxDB assumes that all epoch timestamps are in nanoseconds. Include a duration literal at the end of the epoch timestamp to indicate a precision other than nanoseconds.

About how to convert it, that’s entirely up to the tools you have… in the worst case some simple math will do the trick

3

Thanks ! That’s pretty helpful, but I am still kinda stuck with it.

I have managed to get a date out of it using Excel and the following formula :
=(B2/86400000000000)+DATE(1970,1,1) which returns the date (which is more than I was able to get before) but still have no idea how to get the time from that - any math that you can suggest I can do on it to get the time too ?

4

in excel, and in most RDMS dates and timestamps are basically numbers, where 1 is equal to 1 day, anything less than a day is just a the decimal decimal part… therefore as a human-readable sample:
1.0 = 1d
1.5 = 1d 12h
3.25 = 3d 6h

In excel, right now the time 19/01/2022 16:28:31 is a numeric value of 44580,6864690972 and the decimals go on and on…

If your division is not truncating the decimal part (and I don’t think so), you already have the time.
In this case I think excel is just hiding it behind formatting… because taht’s what excel does most of the time… change the format and use a custom one like dd/mm/yyyy hh:mm:ss or just set the column as a number and you will see the full data.

when dealing with the DB you can use the numeric format just fine most of the time

1 Like
5

Brilliant ! Thank you so much !!

I didn’t realise that it was just an Excel formatting problem. And then also just reading it into a pandas dataframe on Python would mean that I didn’t even have to convert it that it would then recognise it as the required date and time format !