Convert Timestamp Values Into Formatted Strings

Converting epoch timestamp to hour format using the format() function with a format specifier

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> result
logscale
format("%tH", field=@timestamp, as=hour)
table(hour)

Introduction

The format() function can be used to format values according to specified patterns, particularly useful for formatting epoch timestamps (milliseconds since 1970) into specific date/time string representations.

In this example, the format() function is used to format the @timestamp field (containing milliseconds since epoch) to show the time in HOUR format using the format specifier %tH.

Example incoming data might look like this:

@timestampactionuserstatus
1686837825000loginjohnsuccess
1686839112000logoutjohnsuccess
1686840753000loginalicesuccess
1686842415000downloadalicecompleted
1686844522000loginbobfailed
1686845745000loginbobsuccess

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    format("%tH", field=@timestamp, as=hour)

    Extracts the hour (in 24-hour format) from the epoch timestamp in @timestamp field using the format specifier %tH and returns the formatted results in a new field named hour. The parameter field specifies the input field containing the epoch timestamp (in milliseconds), and as defines the name of the output field. The pattern %tH specifically formats the hour in 24-hour format (00-23).

    Note that fields can only be used as date/time values if they are in milliseconds since the beginning of the Unix epoch, 1 January 1970 00:00:00 UTC. If the field is anything else, format outputs null.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1["Expression"] 2{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    table(hour)

    Displays the result of the hour field in a table.

  4. Event Result set.

Summary and Results

The query is used to convert epoch timestamps to readable hour format.

Sample output from the incoming example data:

hour
14
15
16

The hours are displayed in 24-hour format (00-23).