topic Re: convert to datetime datatype and derive day,week,month,year from it... in Splunk Enterprise

convert to datetime datatype and derive day,week,month,year from it...

Raj_Splunk_Ing — Thu, 10 Oct 2024 14:27:55 GMT

Hi, Before asking i did try to find but not able to locate the thread that has this kind of datetime values..so i had to come up with this new thread

I have the datetime values in string format like Thu 10 Oct 2024 08:48:12:574 EDT sometimes there may be a null in it - thats how it is 😀 what is that i have to do with this is get/derive into separate columns

day name like Thursday

day of month like 10

month like Oct
year 2024
week - weeknumber like 2 or 3

Time part into separate column like 08:48:12:57 - not worried about EDT

separate the time components into again
08 as Hour
48 as Min
12 as Sec
not worried about ms

still looking for threads with this kind of but...again sorry this is a basic one 🙂 just needs more searching

Re: convert to datetime datatype and derive day,week,month,year from it...

sainag_splunk — Thu, 10 Oct 2024 14:43:39 GMT

Based on what I can understand, you can try using something like this and tweak it as needed.

| makeresults | eval datetime_str="Thu 10 Oct 2024 08:48:12:574 EDT" | eval datetime=strptime(datetime_str, "%a %d %b %Y %H:%M:%S:%3N %Z") | eval day_name=strftime(datetime, "%A"), day_of_month=strftime(datetime, "%d"), month=strftime(datetime, "%b"), year=strftime(datetime, "%Y"), week_number=strftime(datetime, "%U"), time_part=strftime(datetime, "%H:%M:%S") | fields datetime_str, datetime, day_name, day_of_month, month, year, week_number, time_part | eval hour=substr(time_part, 1, 2), minute=substr(time_part, 4, 2), second=substr(time_part, 7, 2)

Re: convert to datetime datatype and derive day,week,month,year from it...

Raj_Splunk_Ing — Thu, 10 Oct 2024 15:11:35 GMT

Hi sainag, Thank you so much for your quick response.
I was able to use your example and get it as follow - 2 things i noticed are
1 is the week number as 40 this should have been the october month week number
2 is the time part - i have 08.48.12 which is EST - but in my results i see it as 07.48.12

ToDateTime1=strptime(TempDate1, "%a %d %b %Y %H:%M:%S:%3N %Z"),
Get_Day_Name=strftime(ToDateTime1, "%A"),
Get_Month_Num=strftime(ToDateTime1, "%d"),
Get_Month_Name=strftime(ToDateTime1, "%b"),
Get_Year=strftime(ToDateTime1, "%Y"),
Get_Week_Number=strftime(ToDateTime1, "%U"),
Get_Time_Part=strftime(ToDateTime1, "%H:%M:%S")

Thanks a lot

Re: convert to datetime datatype and derive day,week,month,year from it...

Raj_Splunk_Ing — Thu, 10 Oct 2024 15:29:31 GMT

Hi, figured out to get the week number based on the day number

Get_Week_Number=floor(tonumber(strftime(ToDateTime1, "%d"))/7)+1,

also adjusted my preferences to the datetime to show eastern

Re: convert to datetime datatype and derive day,week,month,year from it...

PickleRick — Thu, 10 Oct 2024 20:48:42 GMT

Ok. In order to reliably split a "variable format" time string you must have some strong assumptions you can make about it. For example, the order of the fields must be constant, the time specifier must be in a relatively well-defined format and so on. Otherwise you wouldn't be able to tell whether "10 23" means 10:23 AM or 23rd of October. Or maybe 10 minutes past some hour in 23rd day of some month. You must have something to anchor your extraction to.