Create a new _time column from a short date column stored as string

Fluxlang
, ,
1

Hi there,
I’ve got a point in time dataset including information about how many online shopping slots (count) that are available for different stores (chain, name) per date (YYYY-mm-dd).

I would like to make a bar chart with the stacked counts per day and I’d like to remove the current timestamp (_time) from the table and replace it with a new timestamp compiled using the date. That way I would spread the series out over time again since they are all stacked with the same timestamp (when the measurement was taken).

I’ve managed to get this far:
import “date”
import “strings”

from(bucket: “shopping”)
|> range(start: -5m)
|> filter(fn: ® => r["_measurement"] == “supermarket_slots_history” and r["_field"] =~ /count|day|month/)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> filter(fn: ® => r[“month”] == date.month(t: r["_time"]) and r[“day”] > date.monthDay(t: r["_time"]))
|> filter(fn: ® => r[“month”] == date.month(t: r["_time"]) and r[“day”] <= date.monthDay(t: r["_time"])+8)
|> drop(columns: [“host”, “id”, “day”, “daynumberofweek”, “month”, “_measurement”])
|> aggregateWindow(every: 5m, column: “count”, fn: last)
|> set(key: “newtime”, value: “YYYY-mm-ddT00:00:00Z”)
|> group(columns: [“chain”, “name”, “date”])
|> sort(columns: [“date”, “chain”, “name”])

I thought that I could drop the _time column but I would need to create a new timestamp column first and found the set() function. I’m not sure if I can get the date value within that function though? Maybe concatenate my date value with a static ‘T00:00:00Z’ and feed it to the time() function and use that new timestamp as the _time column.
I included the strings library to use strings.joinStr but got stuck.

#group FALSE FALSE FALSE FALSE FALSE TRUE TRUE FALSE TRUE FALSE FALSE
#datatype string long dateTime:RFC3339 dateTime:RFC3339 dateTime:RFC3339 string string string string double string
#default _result
result table _time _start _stop chain date dayofweek name count newtime
0 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 14/04/2020 Tuesday Regent 0 YYYY-mm-ddT00:00:00
1 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 14/04/2020 Tuesday Tikipunga 0 YYYY-mm-ddT00:00:00
2 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 14/04/2020 Tuesday Whangarei 0 YYYY-mm-ddT00:00:00
3 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 15/04/2020 Wednesday Regent 0 YYYY-mm-ddT00:00:00
4 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 15/04/2020 Wednesday Tikipunga 0 YYYY-mm-ddT00:00:00
5 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 15/04/2020 Wednesday Whangarei 0 YYYY-mm-ddT00:00:00
6 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 16/04/2020 Thursday Regent 0 YYYY-mm-ddT00:00:00
7 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 16/04/2020 Thursday Tikipunga 0 YYYY-mm-ddT00:00:00
8 2020-04-13T13:35:00Z 2020-04-13T13:31:32.804053556Z 2020-04-13T13:36:32.804053556Z Countdown 16/04/2020 Thursday Whangarei 0 YYYY-mm-ddT00:00:00
2

@aastroem I think a simpler solution would be duplicate your _time column, then use the date.truncate() function to truncate your newtime values to a specified unit:

import "date"
import "strings"

from(bucket: "shopping")
  |> range(start: -5m)
  |> filter(fn: (r) => r["_measurement"] == "supermarket_slots_history" and r["_field"] =~ /count|day|month/)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r["month"] == date.month(t: r["_time"]) and r["day"] > date.monthDay(t: r["_time"]))
  |> filter(fn: (r) => r["month"] == date.month(t: r["_time"]) and r["day"] <= date.monthDay(t: r["_time"])+8)
  |> drop(columns: ["host", "id", "day", "daynumberofweek", "month", "_measurement"])
  |> aggregateWindow(every: 5m, column: "count", fn: last)
  |> duplicate(column: "_time", as: "newtime")
  |> map(fn: (r) => ({ r with newtime: date.truncate(t: r.newtime, unit: 1d) }))
  |> group(columns: ["chain", "name", "date"])
  |> sort(columns: ["date", "chain", "name"])