Copy data from one measurement to another but change in precision

1

Hi,

I have one measurement with x fields (e.g. 30) and want to split this up into x measurements. I know I can do

select fieldname into newmeasurement from oldmeasurement

However I also want to change the precision (former ns new s). How can I achieve that?

2

Hello @j.koopmann,
There isn’t an InfluxQL query that lest you change the precision.
But you can elect to query data with a different precision with client libraries.
Why do you need to change precision?

3

I suppose I do not “need” it. As mentioned in the other post in the past I was putting all incoming values (with very different time intervals for the values) into one Influx measurement resulting in tons of measurement lines and >30 fields with only one field per line. Since I did not change the precision it was written in ns.

I am now changing this to one value/temperature type to one Influx measurement. Since the values come at most every few seconds I now changed the precision to 1s on the writes.

Now I am trying to get the historic values from the old measurement “table” (with ns precision) to the new measurement tables with s precision. I read somewhere that you should not mix precision in one measurement hence my question.

I guess it will not do any harm and I can simply “select into” the values from the old table to the new ones and ignore the two different precisions.

4

You can change the precision with a SELECT INTO query, but it may take some time to write it properly

SELECT mean(_field_) AS _field_ INTO _destination_ FROM _source_ GROUP BY time(1s),*
  1. you must aggregate and alias all the fields properly
  • The “correct” aggregation function depends on what you want to calculate
  • aliasing is needed to keep the same field name (or it will become ie: mean.field) or match the one in the destination
  1. Group by the wanted time unit, this will decrease precision
  2. Group by all the tags needed in the destination, if all tags are needed use *

I suggest you test it first on a small subset of data and write it in a temporary location as it’s quite easy to mess up entire measurements if the query is not properly written

5

Thanks. This is nearly what I am looking for.

What I have:

name: KWL
time                KWL_Abluft KWL_Aussenluft KWL_Fortluft KWL_Waermetauscher KWL_Zuluft
----                ---------- -------------- ------------ ------------------ ----------
1704737758852287640            16
1704737767957853123            15
1704737795265889726            16
1704737813480143306            15
1704737822582907860            16
1704737831684598552            15
1704737849897999681            16
1704737859018412295            15
1704737868105527497            16
1704737877215105303            15
1704737880415295551 17
1704737880452345541                                                           14
1704737880486217245                           16
1704737880558844168                                        14
1704737895429726506            16
1704737904529861258            15
1704737913635179211            16
1704737986465900250            15
1704738041105797296            16
1704738323582456946                                        15
1704738332687924607                                        14
1704738368884227321            15

(limited of course).

From that I would love to extract only KWL_Aussenluft and that on a second precision. When I try your approach I get:

> select mean(KWL_Aussenluft) from KWL group by time(1s) limit 50;
name: KWL
time                mean
----                ----
1704737758000000000 16
1704737759000000000
1704737760000000000
1704737761000000000
1704737762000000000
1704737763000000000
1704737764000000000
1704737765000000000
1704737766000000000
1704737767000000000 15
1704737768000000000
1704737769000000000
1704737770000000000
1704737771000000000

I want only the lines which have real values and then select INTO them into a new measurement. I could do this easily without changing the precision and probably this is what I am going to do and accept that the data from the past weeks is on ns precision and everything new on 1s. Unless you have another hint for me…

6

A datapoint witohut fields simply does not exists… so an insert won’t generate any empty rows.

if you want a cleaner select just use proper FILL

select mean(KWL_Aussenluft) AS KWL_Aussenluft from KWL group by time(1s) fill(none)