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

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")

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

@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).

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?

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.

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

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: