Hi,
I am currently attempting to split the Date and Time from one field into 2 or more fields. I have read some of the questions and answers here, but to no avail.
I am working with Starbucks.csv, which shows the Date, Volume and Closing stock price of Starbucks. The Date format is in YYYY-MM-DD. My intention is to split the Date to Year, Month and Day Fields respectively.
I have seen some of the community answers and many proposed a simple method such as |eval YearNo=(Date, "%Y) for the Year field. However, I tried and the search simply did not return any new field, Below is a snippet of the attempt. I put Date and YearNo in the same table to show how YearNo was not extracted.
My next thought was that maybe splunk did not register the Date field as a date but merely as a string. I went ahead and plotted the Date vs Volume Chart on the visualization option and it does seem that Splunk registered the Date as date, and hence the plot was crafted nicely. The snippet is shown below.
I would greatly appreciate if someone could enlighten me on this situation and how can I extract the date to their individual fields.
Cheers,
Lucas
Hi @lucasle ,
I'm not sure to completely understood your need.
are you speaking of the timestamp or of one other field?
if you're speking of the TimeStamp, _time is stored in epochtime so to search you have to transform using strftime and strptime function in eval command.
If instead you have a different fiel (called e.g. date), it depends on the format and you can use substr or a regex,
so, if you have a field called e.g. Date with format YYYY-MM-DD, you can extract year, month and day in this way:
index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
In this way you can insert the search or where commands to filter your events, e.g. if you want only the events of May, you could try something like this:
index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| table .....
To plot a graphic, it's easier if you have a date, but also with a string you could plot a graphic by month creating something like this:
index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| stats count BY Year Month
Ciao.
Giuseppe
Hi @lucasle ,
I'm not sure to completely understood your need.
are you speaking of the timestamp or of one other field?
if you're speking of the TimeStamp, _time is stored in epochtime so to search you have to transform using strftime and strptime function in eval command.
If instead you have a different fiel (called e.g. date), it depends on the format and you can use substr or a regex,
so, if you have a field called e.g. Date with format YYYY-MM-DD, you can extract year, month and day in this way:
index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
In this way you can insert the search or where commands to filter your events, e.g. if you want only the events of May, you could try something like this:
index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| table .....
To plot a graphic, it's easier if you have a date, but also with a string you could plot a graphic by month creating something like this:
index=my_index
| eval Year=substr(Date,1,4),Month=substr(Date,5,2),Day=substr(Date,7,2)
| search Month="05"
| stats count BY Year Month
Ciao.
Giuseppe
Hi @gcusello ,
Thanks for your swift reply, I very much appreciate it.
So to clarify again, the initial method that I mentioned - |eval YearNo = strftime(Date, "%Y") is used when dealing with real time data?
Also, thanks for your solution. I was just wondering if Splunk has a more intelligent way of reading the date string and recognising it as Year, Month or Day, instead of using substr to isolate the numericals and group them together. Either way, this works fine and I really appreciate it!
Best regards,
Lucas
Hi @lucasle ,
As I said it depends on the format of date (epochtime or human readable), because the date/time functions work wiith epochtime, so when you manage timestamps, they are in epochtime, so you can use the date/time managing functions; the realtime isn't relevant.
You could use these functions also with other dates, but I think that's easier to use
| eval Year= substr(Date,1,4)
instead of
| eval Year=strftime(strptime(Date,"%Y-%m-%d"),"Y")
because you need to transorm the date two times (date->epochtime->year).
Ciao.
Giuseppe
Hi @gcusello ,
Just to show you how you can work with " the date string" as a date:
| eval Date = strptime(Date, "%Y-%m-%d")
| eval Year = strftime(Date, "%Y")
| eval Month = strftime(Date, "%m")
| eval Day = strftime(Date, "%d")
With the strptime() you convert your yyyy-mm-dd to Unix time and afterwards you can derive Year, Month and Day with strftime from it.
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/DateandTimeFunctions
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables