Splunk Search

## Datetime to Date

Path Finder

I have a field called Date like this 2017-07-26 22:34:09.383 and I need to strip out the time and keep just the date (2017-07-26). After that I need to group by Date. How do I do that? I have tried this but it's not working:

``````source=mysource
| eval newtime=strftime(Date, "%Y-%m-%d")
| chart sum(Revenue) by newtime
``````

Thanks.

Tags (1)
1 Solution
Motivator

your search | rex field=Date "(?P<newField>\d+-\d+-\d+)\s\d+:\d+:\d+" | chart sum(Revenue) by newField

Motivator

your search | rex field=Date "(?P<newField>\d+-\d+-\d+)\s\d+:\d+:\d+" | chart sum(Revenue) by newField

Path Finder
Path Finder

Never mind. I figured it out:
my search
| rex field=Date "(?P\d+-\d+-\d+)\s\d+:\d+:\d+"
| eval n=relative_time(now(), "-7d@d")
| eval newtime=strptime(Date, "%Y-%m-%d")
| where newtime >= n
| chart sum(Revenue) AS Revenue by Day

Builder

Hi,

If you just need a new time field with dates, you can try this:

``````your search | eval newtime=strftime(_time, "%Y-%m-%d")
``````

This will create a new field called "newtime" that's a time field with only the date (year (Y), month (m), day of month (d)).

See more information on the time functions here:

http://docs.splunk.com/Documentation/SplunkCloud/6.6.0/SearchReference/DateandTimeFunctions

And see more information on date and time format variables (the %Y, %m, etc.) here:

https://docs.splunk.com/Documentation/SplunkCloud/6.6.0/SearchReference/Commontimeformatvariables

Path Finder

Hi, I am trying to group by the newtime, and it's not working:

my search| eval newtime=strftime(Date, "%Y-%m-%d")
| chart sum(Revenue) by newtime

Motivator

try _time instead of DATE. if DATE field is not existed.

below is the sample query

index=_internal group=per_source_thruput | eval newtime=strftime(_time, "%Y-%m-%d") | chart sum(kb) by newtime

Path Finder

Date is my field so I need to use it.

Motivator

please check Revenue and Date field is exist. Query is fine.

To confirm, please execute the query below, It should output some values. If not fields are not extracted properly.

index=xx sourcetype=xxxx | table Date Revenue.

Path Finder

Yes, when I run this it outputs the Date and Revenue.

Motivator

Ah okay. can you paste your search query and one sample event.

Path Finder

my search | table Date Revenue

Date Revenue
2017-07-27 18:14:37.65 19.0000

Motivator

try this

your search | rex field=_raw "(?P<extractedDate>\d+\-\d+\-\d+)\s+\d+:\d+:\d+.\d+"

Builder

If I'm not mistaken this would create a new field (extractedDate) that's a string, not a time field, meaning that one cannot do time operations with it unless converting it with "strptime" first.

Motivator

@hettervi

As per my understanding luislema have timestamp in the events itself and wanted to extract only date portion from timestamp. So in my search query i m not converting anything, so there is no chance to convert from epoch time to date stamp.

Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...