Splunk Search

Trouble with date conversion

mistydennis
Communicator

I need to create a field (30days) with a date 30 days from the date in a given field (pubdate). I believe I have that part working, but can't seem to get the date to convert to the format I want.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=epochtime + 2592000
|convert ctime(30days)
|table pubdate, 30days


Which produces:

pubdate30days
2022-09-3010/30/2022 00:00:00.000000
2021-08-3109/30/2021 00:00:00.000000

 

All I want to do is to format the 30days date field the same was as pubdate - "%Y-%m-%d". Everything I'm trying is producing an error.

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The convert command has options to control the format of the time string, but I prefer to use strftime.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=relative_time(epochtime, "+30d")
|eval 30days=strftime('30days', "%Y-%m-%d")
|table pubdate, 30days

I also used relative_time() to compute the new timestamp.

I didn't do it in my example, but try to avoid field names beginning with digits as they can confuse the parser.  For instance, the strftime call failed until I used single quotes around the first argument.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The convert command has options to control the format of the time string, but I prefer to use strftime.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=relative_time(epochtime, "+30d")
|eval 30days=strftime('30days', "%Y-%m-%d")
|table pubdate, 30days

I also used relative_time() to compute the new timestamp.

I didn't do it in my example, but try to avoid field names beginning with digits as they can confuse the parser.  For instance, the strftime call failed until I used single quotes around the first argument.

---
If this reply helps you, Karma would be appreciated.
0 Karma

mistydennis
Communicator

Thank you, @richgalloway ! I see you have answered MANY questions regarding date and timestamps here - maybe it's time for you to just write a book and help us all out 🙂

0 Karma

isoutamo
SplunkTrust
SplunkTrust

In most cases I also prefer to use strftime, but in adhoc  queries and especially with mv fields, it’s easier way to convert all values.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

Hi

have you tried 

...
| convert timeformat="%Y-%m-%d" ctimes(30days)

r. Ismo 

0 Karma
Get Updates on the Splunk Community!

Index This | Forward, I’m heavy; backward, I’m not. What am I?

April 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

A Guide To Cloud Migration Success

As enterprises’ rapid expansion to the cloud continues, IT leaders are continuously looking for ways to focus ...

Join Us for Splunk University and Get Your Bootcamp Game On!

If you know, you know! Splunk University is the vibe this summer so register today for bootcamps galore ...