formatDate Search Operator

The formatDate operator allows you to format dates in log files as a string in the format you require, such as US date formatting, European formatting, and timestamps. 

Syntax

formatDate(<date> [, <format> [, <timeZone>]]) as <field>

Returns:

A date String, in US-style date format if no format is specified. The date is in the local timezone of the user if no timeZone is specified.

Parameters:

• date - milliseconds (13 digits), as a Long. You can also use formatDate with the Now operator.
• format - any valid date and time pattern String accepted by Java’s SimpleDateFormat. For more details about specifying the format see Timestamps, Time Zones, Time Ranges, and Date Formats.
• timeZone - a String, such as "America/Los_Angeles" or "Europe/London"

info

Convert the date parameter to Long if necessary. Passing a String can produce the error: "Multiple definitions found for function formatDate(String, String)." The solution is to cast the date parameter using the toLong operator.

Examples

Date format yyyy-MM-dd

Use the following query to return results for the current date using the date format yyyy-MM-dd.

* | formatDate(now(), "yyyy-MM-dd") as today

This creates the today column, and returns the following results.

European date format** **dd-MM-yyyy

Use the following query to create a today column, and return the results using the European date format of day, month, year, dd-MM-yyyy.

* | formatDate(now(),"dd-MM-yyyy") as today

This returns the following results:

US date format with a timestamp

This example creates a today column and uses the US date format with a timestamp, MM-dd-yyyy HH:mm.

* | formatDate(now(), "MM-dd-yyyy HH:mm", "America/New_York") as today

Which returns results like:

Find messages with incorrect timestamps

This query allows you to find messages with incorrect timestamps.

* | formatDate(_receipttime, "MM/dd/yyyy HH:mm:ss:SSS") as receiptDate
| formatDate(_messageTime, "MM/dd/yyyy HH:mm:ss:SSS") as messageDate
| _receiptTime - _messageTime as delay
| delay / 60000 as delayInMinutes

This query produces results like this:

Determine age of log messages

This query lets you determine the age of your log messages.

* | formatDate(_messageTime, "MM/dd/yyyy HH:mm:ss:SSS") as messageDate
| formatDate(now(), "MM/dd/yyyy HH:mm:ss:SSS") as today
| now() as currentTime
| currentTime - _messageTime as messageAge
| messageAge / (60*1000) as messageAgeInMinutes

Which produces results like this:

Messages by Day of the Week

To get the day of the week from your logs, you can reference your log's timestamps, which are stored as the metadata field _messageTime. You can also parse out any dates in your logs and use the formatDate operator to get the day of the week.

Beginning with the _messageTime field, you can determine the day of the week, and then remove the days you don't want using the formatDate operator. This example query provides results only for Mondays:

| formatDate(_messagetime, "EEE") as day
| where day="Mon"

This example query provides only weekday results:

| formatDate(_messagetime, "EEE") as day
| where !(day="Sat" or day="Sun")

If you don't use _messageTime, and instead parse out another timestamp, you can convert it to milliseconds and determine the day this way:

| parseDate(parsedtime, "MM/dd/yyyy HH:mm:ss a") as inMilliseconds

Format a milliseconds (13 digits) epoch value

With the following example query:

_sourceCategory=sourceCategory
| parse "] [*][*][*].[*]" as (user, datasource, session, command)
| count, min(_messageTime), max(_messageTime) by session

You get the following results:

| # | session | _count | _min | _max | |--||-|-|-| |  1 | 7oEmE+KLpk1nVYpF | 22 | 1.35844e+12 | 1.35844e+12 | |  2 | 6uklr9UDkTOg79je | 412 | 1.35844e+12 | 1.35844e+12 | |  3 | q0K6ztX9IvpZWh1p | 18 | 1.35844e+12 | 1.35844e+12 |

In the results, the _min and _max values are displayed as an epoch value. You can format these epoch values into a readable date with an experimental operator, toLong.

• toLong casts the data into a Long data type as milliseconds.

Normally, to convert the epoch time into a date formatted string you'd do something like this:

* | formatDate(_messagetime, "``MM-dd-``yyyy`` HH:mm:ss") as myDate

However, in the case where you are using Min and Max to get the first and last values, you also need to convert the return value to a "Long" value type using the experimental toLong operator. This is because when you run the Min and Max operators, the return value gets reformatted as a "Double" value type that the formatDate operator can't read.

* | count, min(_messagetime) as mindate | formatDate(toLong(mindate))

For the given example, the following query gets the proper date/time values in the results:

_sourceCategory=sourceCategory
| parse "] [*][*][*].[*]" as (user, datasource, session, command)
| count, min(_messagetime) as mindate, max(_messagetime) as maxdate by session
| formatDate(toLong(mindate),"MM-dd-yyyy HH:mm:ss:SSS") as mindate
| formatDate(toLong(maxdate),"MM-dd-yyyy HH:mm:ss:SSS") as maxdate

Format a seconds (10 digits) epoch value

If your timestamp is a normal Unix timestamp it is in seconds since January 1, 1970 at 00:00:00 GMT. The formatDate operator requires your timestamp to be in milliseconds. Therefore, you need to convert by multiplying by 1,000 since there are 1,000 milliseconds in a second.

...
| toLong(eventTimeInEpochSeconds * 1000) as eventTimeInEpochMs
| formatDate(eventTimeInEpochMs, "MM-dd-yyyy") as eventTimeHuman