topic Re: How to convert Modified Julian Date (MJD) to human readable in Splunk Search

How to convert Modified Julian Date (MJD) to human readable

ssaenger — Tue, 19 Sep 2023 16:42:55 GMT

Hi All,

i have read similar posts but none that will get me to an answer.

My log entry is this;

2023-09-19 16:17:01,306 <OnAirSchedule Service="9008" Status="ON" StartDateTime="59025.5249306"/>

The StartDateTime is in MJD and i would like to get into human readable format.

Below is my search, some regex to start with then the conversion.

| rex "<OnAirSchedule\sService=\"(?<SERVICE>[0-9]+)\"\sStatus\=\"(?<STATUS>.+)\"\sStartDateTime\=\"(?<START_DATE>.+)\"\/\>" | eval jdate=START_DATE,epoch_date=strptime(jdate,"%y%j"),date=strftime(epoch_date,"%Y-%m-%d %H:%M:%S.%1N") | table _time SI_SERVICE_KEY STATUS START_DATE epoch_date date

which was a solution in another question, however i get the date time

2059-01-25 00:00:00.0

I have tried variances of the %y%j to %y.%j and %Y.%j, however these just seem to deal with the date as Julian Date, rather than using the values after the decimal point.

This page seems to point to something i am after but it doesnt deal with the full MJD.

https://community.splunk.com/t5/Getting-Data-In/Splunk-recognizing-Julian-Date-and-Elapsed-Seconds/m-p/72709

any advice greatly welcomed.

Re: How to convert Modified Julian Date (MJD) to human readable

richgalloway — Tue, 19 Sep 2023 17:16:15 GMT

This run-anywhere example shows how to convert MJD into a text date.

| makeresults | eval StartDateTime="59025.5249306" ``` Extract the day number (prior to .) ``` | rex field=StartDateTime "(?<JD>\d+)" ``` Shift the day number to base epoch day and convert to seconds ``` | eval time=(JD-40587) * 86400 ``` Convert to text ``` | eval humanTime=strftime(time, "%c")

Re: How to convert Modified Julian Date (MJD) to human readable

ssaenger — Tue, 19 Sep 2023 17:31:02 GMT

Hi Rich,
Thank you for your reply.
I have run the command and added some extra as I was also after the time, however i think you chop the seconds off.

this gives,

2020-06-25 00:00:00.0

do we have to extract the seconds and add them back to epoch?

Re: How to convert Modified Julian Date (MJD) to human readable

richgalloway — Tue, 19 Sep 2023 19:42:04 GMT

To include the time component, we don't need to extract anything. Just treat StartDateTime as a floating point number.

| makeresults | eval StartDateTime="59025.5249306" | eval time=(StartTime-40587) * 86400 | eval humanTime=strftime(time, "%c")

Re: How to convert Modified Julian Date (MJD) to human readable

ssaenger — Tue, 19 Sep 2023 20:21:40 GMT

Hi Rich,

Brilliant!, thank you thank worked.