Skip to main content
Sumo Logic

formatDate

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, timestamps, etc. 

Syntax

formatDate(date [, format [, timeZone]]) as alias

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

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.

FormatDate

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:

EuropeanDateFormat

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:

DateTimestamp

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:

Incorrect Timestamp

How old are your 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:

Message age

Compare Log 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 an epoch value (milliseconds since 1970) into a human readable date

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 a couple experimental functions that help with this case, fromMillis and toLong.

  • fromMillis formats the epoch time string value to a data value that can be read by the formatDate function.
  • toLong casts the data into a Long data type as milliseconds.

To convert the epoch time into a date formatted string you can use fromMillis, like this:

* | formatDate(fromMillis(_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 function. This is because when you run the Min and Max functions, the return value gets reformatted as a "Double" value type that the fromMillis function cannot read.

 * | count, min(_messagetime) as mindate | formatDate(fromMillis(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(fromMillis(toLong(mindate)),"MM-dd-yyyy HH:mm:ss:SSS") as mindate
  | formatDate(fromMillis(toLong(maxdate)),"MM-dd-yyyy HH:mm:ss:SSS") as maxdate