Best way to get timestamps from integer

[user642]

Hello, I have a Col with Integer 1 to 12 and need to get Timestamp. For example

1 => 2024-01-01
2= > 2024-02-01
…

whats the best way to do this?

thanks

[scott]

@user642 It depends on what the integer represents. In your example, it appears to be the month. Is that what it always represents in your dataset?

[user642]

This is where im actually at:

from(bucket: “langzeit”)
|> range(start: date.add(d:-1mo,to: v.timeRangeStart), stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Solarthermie”)
|> filter(fn: (r) => r[“_field”] == “Ertrag/kWh”)
|> set(key: “_field”, value: “Ertrag”)
|> drop(columns: [“source”,“groupaddress”,“host”,“unit”])
|> sort(columns: [“_time”])
|> increase()
|> interpolate.linear(every: 1d)
|> aggregateWindow(every: 1mo, fn: last , createEmpty: true, timeSrc: “_start”)
|> difference(nonNegative: true,initialZero: true)
|> map(fn: (r) => ({r with Monat: date.month(t: r._time)}))
|> map(fn: (r) => ({r with Jahr: date.year(t: r._time)}))
|> pivot(rowKey: [“Monat”], columnKey: [“Jahr”], valueColumn: “_value”)
|> map(fn: (r) => ({
r with Monat:
if r.Monat == 1 then “Januar”
else if r.Monat == 2 then “Februar”
else if r.Monat == 3 then “März”
else if r.Monat == 4 then “April”
else if r.Monat == 5 then “Mai”
else if r.Monat == 6 then “Juni”
else if r.Monat == 7 then “Juli”
else if r.Monat == 8 then “August”
else if r.Monat == 9 then “September”
else if r.Monat == 10 then “Oktober”
else if r.Monat == 11 then “November”
else “Dezember”
}))

But in the end I lost my _time column and just have an integer 1-12 which I convert to String

At first i tried with “timeShift” but doesnt work due to leap year.

I want to compare my Meter data month by month, so i need _time with months but without years

[user642]

I think i found a solution but only with Grafana

At first convert int 1 to 12 to String. And then convert String back into Time

I dont know why but converting int to string at first necessary. Doesnt work without.

my “years” are now all “2025”. But doenst matter because I have only interesst in Month and can just use “unit” in Grafana to get rid of everything else

But:

Is there a better way?

Perhaps something to convert 2023-01-01 and 2024-01-01 into 2025-01-01 ? Just change the yearvalues of _time to another value?

[scott]

@user642 I’m not sure I fully understand the purpose of the query. What do you want the query results to look like?

[user642]

I have meter data and I want to compare the comsumption month by month.
I want to compare the consumption at 2023-01-01 with 2024-01-01. 2023-02-01 with 2024-02-01 …

I use

|> map(fn: (r) => ({r with Monat: date.month(t: r._time)}))

to get rid of everyting else then month

[scott]

If it works, it works . I did update your query above to be a bit more concise:

import "date"
import "dict"
import "interpolate"

getMonthName = (monthN) => {
    monthNames = [
        1: "Januar",
        2: "Februar",
        3: "März",
        4: "April",
        5: "Mai",
        6: "Juni",
        7: "Juli",
        8: "August",
        9: "September",
        10: "Oktober",
        11: "November",
        12: "Dezember"
    ]

    return dict.get(dict: monthNames, key: monthN, default: "Unbekannt")
}

from(bucket: "langzeit")
    |> range(start: date.add(d:-1mo,to: v.timeRangeStart), stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Solarthermie")
    |> filter(fn: (r) => r["_field"] == "Ertrag/kWh")
    |> drop(columns: ["source","groupaddress","host","unit"])
    |> increase()
    |> interpolate.linear(every: 1d)
    |> aggregateWindow(every: 1mo, fn: last , createEmpty: true, timeSrc: "_start")
    |> difference(nonNegative: true,initialZero: true)
    |> map(fn: (r) => ({
        r with
        Monat: getMonthName(monthN: date.month(t: r._time)),
        Jahr: date.year(t: r._time)
    }))
    |> pivot(rowKey: ["Monat"], columnKey: ["Jahr"], valueColumn: "_value")

[user642]

THX
But this leads to one problem:

“Monat” is String (“Januar” , “Feburar” …) . So I cannot use Grafana Transformations.
I can transform Strings like “1” “2” … into time but not words. Because I have Grafana to tell how to interpret that Strings to transform them into time.

“Input format” MM works for Strings like “1” “2” “3” but not with words.

If I dont convert “Monat” into “Time” I got this:

So there is always that Label “Monat” in Grafana when using “Tooltip”

I dont want that. For me its okay when there is just the name of the month itself is displayed like here

But this is only displayed in that form if “Monat” is type “Time” and not “String”

I think Grafana will always display the column Label as long as is something else than time

So I think I have to decide having german month names but with that Label “Monat” in Grafana Tooltip.
Or having english names, convert that column into Time and get rid of the Column Label in Grafana.

But its okay.

Maybe its better to let Grafana translate the english month names into german if possible.