What is the best way to trim a Timestamp?

[bjs]

What is the best way to trim a timestamp formatted like 2022-01-06 01:51:23 UTC so that it only reflects the date and hour, like this  2022-01-06 01? I need to be able to search for events by just the date and hour.

[PickleRick]

Depends on the use case but in general it's usually best to operate on time stored as number (unix time), not on time strings.

So in this case, provided that you have your timestamp stored in a field call Time, you could just use the bin command with a proper bin.

| bin Time span=1d

If you want to manipulate strings, you could use regex, but substr should in this case be way faster

| makeresults
| eval Time="2022-01-06 01:51:23 UTC"
| eval JustDate=substr(Time,1,10)

[yuanliu]

OP wants hour of day, so

| bin span=1h@h _time

Also note that the span element is also accepted in timechart, so you may not even need to have a standalone bin command.

[PickleRick]

Ahh, didn't notice the hour part indeed. So the substring would have to be a bit longer. 13 characters?

Well spotted.

There is a subtle difference between timechart and bin if you want to do your stats not by time alone.

As you undoubtedly know

| timechart span=1h count by whatever

produces differently formatted results than

| bin span=1h _time
| stats count by _time whatever

Sure, you can use untable/xyseries to "convert" from one to another but  it's usually more straightforward to chose the form more suitable for further use without additional modifications.

[ITWhisperer]

Another difference to note is that timechart will fill in the gaps across the whole time span defined by the search (from earliest to latest), whereas stats by _time merely uses the times from the events found.