topic Re: How to covert numbers into date in Splunk Search

How to covert numbers into date

Muthu_Vinith — Fri, 29 Mar 2024 10:21:05 GMT

Hi Experts,

I have a list of dates in the field called my_date like below:

45123
45127
45130

How can I convert this?

Thank you!

Re: How to covert numbers into date

ITWhisperer — Fri, 29 Mar 2024 14:44:25 GMT

It depends on what this number is supposed to represent

Re: How to covert numbers into date

Muthu_Vinith — Sun, 31 Mar 2024 21:30:56 GMT

I tried to convert it but i couldn't get the exact results. Are there any other ways to convert it @ITWhisperer ?

Re: How to covert numbers into date

PickleRick — Sun, 31 Mar 2024 21:46:46 GMT

Again - what dates are these numbers supposed to be?

Re: How to covert numbers into date

Muthu_Vinith — Sun, 31 Mar 2024 22:19:10 GMT

It’s supposed to be based on the data @PickleRick

Re: How to covert numbers into date

ITWhisperer — Mon, 01 Apr 2024 06:51:03 GMT

Can you give an example of what 45123 is supposed to be as a date? I can make a guess but it might be wrong which would waste everyone's time.

Re: How to covert numbers into date

Muthu_Vinith — Mon, 01 Apr 2024 07:17:03 GMT

Format of the date you want based on that number? @ITWhisperer

Re: How to covert numbers into date

ITWhisperer — Mon, 01 Apr 2024 07:20:48 GMT

How about YYYY/mm/dd?

Re: How to covert numbers into date

Muthu_Vinith — Mon, 01 Apr 2024 07:24:30 GMT

Like this @ITWhisperer

YYYY-mm-dd

Re: How to covert numbers into date

ITWhisperer — Mon, 01 Apr 2024 07:26:42 GMT

OK what date in YYYY-mm-dd format would you expect 45123 to be shown as?

Re: How to covert numbers into date

Muthu_Vinith — Mon, 01 Apr 2024 07:33:22 GMT

I’m not sure. Give me an example so that I can try that @ITWhisperer

Re: How to covert numbers into date

ITWhisperer — Mon, 01 Apr 2024 07:34:57 GMT

If I give you a conversion, how will you know whether it is correct or not?

Re: How to covert numbers into date

Muthu_Vinith — Mon, 01 Apr 2024 08:07:08 GMT

I tried this query but it's showing something like this. But when i checked with an excel for this number 45123 - it's showing as 07/16/23. @ITWhisperer

Re: How to covert numbers into date

ITWhisperer — Mon, 01 Apr 2024 08:30:31 GMT

Finally, the key piece of information! You are expecting this to be an Excel date value.

| makeresults | eval date=45123 | eval _time=(date-25567-2)*24*60*60

Excel uses dates based on the start of the 20th Century 1900-01-01, counting in days, whereas, Splunk uses unix-style times based on seconds since 1970-01-01, so, you need to subtract the number of days between these two baseline points, and multiply by the number of seconds in a day. Note that Excel may not be calculating the date correctly since it indexes the first day as 1 (instead of 0) and incorrectly assumes that 1900 was a leap year (which it wasn't), hence the extra -2 days in the calculation.

Having said that, you will have to decide whether the _time value returned is correct based on the source of your data i.e. it could be a couple of days out.

Re: How to covert numbers into date

Muthu_Vinith — Mon, 01 Apr 2024 10:39:57 GMT

Ok Thanks @ITWhisperer