Archive

Datetime to Date

luislema
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)
0 Karma
1 Solution

sbbadri
Motivator

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

View solution in original post

sbbadri
Motivator

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

View solution in original post

luislema
Path Finder
0 Karma

luislema
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

0 Karma

hettervi
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

0 Karma

luislema
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

0 Karma

sbbadri
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

0 Karma

luislema
Path Finder

Date is my field so I need to use it.

0 Karma

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

0 Karma

luislema
Path Finder

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

0 Karma

sbbadri
Motivator

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

0 Karma

luislema
Path Finder

my search | table Date Revenue

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

0 Karma

sbbadri
Motivator

try this

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

0 Karma

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

0 Karma

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

0 Karma