formatDate Search Operator

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

note

If you're looking to convert a date to a timestamp, use parseDate.

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 do not 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 do not 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 cannot 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