topic Re: Datetime to Date in Splunk Search

Datetime to Date

luislema — Wed, 26 Jul 2017 22:43:46 GMT

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.

Re: Datetime to Date

sbbadri — Thu, 27 Jul 2017 02:51:18 GMT

try this

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

Re: Datetime to Date

hettervik — Thu, 27 Jul 2017 11:29:37 GMT

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

Re: Datetime to Date

hettervik — Thu, 27 Jul 2017 11:32:07 GMT

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.

Re: Datetime to Date

sbbadri — Thu, 27 Jul 2017 13:41:57 GMT

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

Re: Datetime to Date

luislema — Thu, 27 Jul 2017 15:47:26 GMT

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

Re: Datetime to Date

sbbadri — Tue, 29 Sep 2020 15:05:42 GMT

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

Re: Datetime to Date

luislema — Thu, 27 Jul 2017 17:14:31 GMT

Date is my field so I need to use it.

Re: Datetime to Date

sbbadri — Thu, 27 Jul 2017 18:12:05 GMT

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.

Re: Datetime to Date

luislema — Thu, 27 Jul 2017 18:14:37 GMT

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

Re: Datetime to Date

sbbadri — Thu, 27 Jul 2017 18:16:15 GMT

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

Re: Datetime to Date

luislema — Thu, 27 Jul 2017 18:19:11 GMT

my search | table Date Revenue

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

Re: Datetime to Date

sbbadri — Thu, 27 Jul 2017 18:28:40 GMT

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

Re: Datetime to Date

luislema — Fri, 28 Jul 2017 03:44:03 GMT

If you have time please answer my new question:

https://answers.splunk.com/answers/558724/last-7-days-based-on-field.html?minQuestionBodyLength=80

Thanks.

Re: Datetime to Date

luislema — Fri, 28 Jul 2017 04:12:57 GMT