Heatmap - Date column automatically append hour. How to format and remove

[h52huang]

Hi, I sent an question but seems like it was not delivered by my poor network.

I have a heatmap. For raw events, I have a column as Date where example values are like 2018-05-21. my X contains all timewindow string, 01:00:00-01:00:20 for 24 hours. Z is a count value.

On heatmap, when I hover, it shows:
x: 01:00:00-01:00:20
y: 2018-05-21 06h (or 12h, or 00h)
z: 12

Is there a way to remove the automatically appended hour characters on the heatmap please? They are not in the raw data column. Do I do data formation and then hide? How to do it please? Could someone give an example please? Or, how can I resolve this in more straightforward ways please?

Thank you very much.

[aljohnson_splun · Accepted solution]

I see. I think the visualization's framework, plotly.js, is automatically converting your date into a a string.

Can you try converting your timestamp to a different format before you do chart? For example,

| eval date_time_as_unix_timestamp = strptime(Date, "%F")
| eval Date = strftime(date_time_as_unix_timestamp, "%Y %b %d")

[aljohnson_splun]

Can you post a few rows of the dataset (or one equivalent) so that I can help you?

[h52huang]

@aljohnson_splunk @aljohnson
Thank you very much.

Date is strings of dates like "2018-05-07"
TimeWindow is string of fixed 20-min window for 24 hours.

TimeWindow 2018-05-07 2018-05-08 2018-05-09 2018-05-10 2018-05-11
00:00:00--00:20:00 0 3 1 6 4
00:20:00--00:40:00 0 1 2 4 0
00:40:00--01:00:00 0 0 2 1 4
01:00:00--01:20:00 0 0 2 4 0
01:20:00--01:40:00 0 8 1 2 4
01:40:00--02:00:00 0 9 2 1 2
02:00:00--02:20:00 0 3 2 2 4
... More windows...

The query I used for the heatmap is:
index="blabla" | chart sum(My_count_column) by TimeWindow Date | sort TimeWindow

When I hover on the heatmap, it shows as:
x: 02:00:00--02:20:00
y: May 08 00h ---> This has been converted from string '2018-05-05" to date with hour information. I want to keep it as '2018-05-08' please.
z: 3