Breakdown _time into date and time and then transpose

[ndaniel88]

Hello,

I have a table like this:

+---------------------+-------+
| _time | value |
+---------------------+-------+
| 2/11/2019 13:00 | 5 |

| 2/11/2019 14:00 | 4 |

| 2/11/2019 15:00 | 2 |
| 2/12/2019 13:00 | 3 |
| 2/12/2019 14:00 | 2 |
| 2/12/2019 15:00 | 1 |
| 2/13/2019 13:00 | 7 |
| 2/13/2019 14:00 | 6 |
| 2/13/2019 15:00 | 9 |
+------------------+---+-------+

And I need to transpose in something like this

+-------+-----------+-----------+-----------+
| _time | 2/11/19 | 2/12/19 | 2/13/19 |
+-------+-----------+-----------+-----------+
| 13:00 | 5 | 3 | 7 |
| 14:00 | 4 | 2 | 6 |
| 15:00 | 2 | 1 | 9 |
+-------+-----------+-----------+-----------+

I was able to transpose the row values as columns using:

| transpose 0 header_field=days

(days is a substr containing the date), but I don't have an idea on how to build the rest of the table. Any help is much appreciated.

Thanks in advance.

[vnravikumar · Accepted solution]

Hi @ndaniel88

Try like and let me know

index=_internal 
| timechart span=1h count 
| eval date=strftime(_time,"%m/%d/%Y") 
| eval time=strftime(_time,"%H:%M") 
| xyseries time date count

[ndaniel88]

Thanks! it works 🙂

xyseries is an interesting command, i had never used it before.

[Vijeta]

@ndaniel88 Try this

index=<your index>| eval Date=strftime(_time,"%m/%d/%Y")|eval Time=strftime(_time,"%H:%M:%S")
|chart limit=0 values(value) over Date by Time

[ndaniel88]

Thank you so much!, I just need to switch to "over Time by Date" and it works perfectly! 🙂