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.
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"
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