Stack graph by day of week / build date from integer hour:minute:second

1

I am trying to stack data by day of week in a graph on a common 24-hour time scale, and while I thought I had solved it am hung up on how to recreate a date as the timescale or to (reverse) truncate _time down to hour:minute:second.

Is there a better way to do this, or at least a way to fix my current issue?

import "date"
from(bucket: "Iota")
  |> range(start: -7d)
  |> filter(fn: (r) => r["ct"] == "PV")
  |> map(fn: (r) => ({r with weekday: date.weekDay(t: r._time)}))
//below does not work because date.h/m/s functions return integer not date forrmat
  |> map(fn: (r) => ({r with shorttime: 
      date.add(d: date.hour(t: r._time), to: 
      date.add(d: date.minute(t: r._time), to: 
      date.second(t: r._time)))}))
  |> pivot(rowKey: ["shorttime"], columnKey: ["weekday"], valueColumn: "_value")
2

Hi @Patrick808,
Could you use: data.truncate()
or better truncateTimeColumn().

3

@Jay_Clifford The truncate functions appear to only truncate in the traditional sense-- eliminating least significant figures. I am trying to “truncate” most significant figures so all I am left with are hours, minutes, and seconds (and presumably a “random” start date that is consistent for all entries).

4

Hi @Patrick808,
Sorry for my mistake :slight_smile: . Can you explain a little more about why you would want to do this rather than just windowing by a 24 hour period for the week?

Could you provide an example of the final result you are hoping to achieve?

5

My ultimate goal is to be able to overlay different days in a graphical or tabular form so that it is easy to visualize differences between days, so there is a single x-axis time scale (or in the table context a single pivoted table with columns for each day).

The value of the information in this case is not absolute numbers on a given day/time, but the relative value compared to another day.

In Excel, I would add a key column to date/time information for day of week, a new time column that just reflects time of day, and pivot with the new time column as the x-axis and days of week as individual series/columns.

6

@Anaisdg do you have any ideas how Patrick could achieve something like this?

7

@Patrick808
I’m not entirely sure if I understand what you’re trying to do, but it sounds like you could use the date package and then pivot: