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

[ssaenger]

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...

any advice greatly welcomed.

[richgalloway · Accepted solution]

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")

[ssaenger]

Hi Rich,

Brilliant!, thank you thank worked.

[ssaenger]

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.

| 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")
| eval humanTime1=strftime(time, "%Y-%m-%d %H:%M:%S.%1N")
| table _time StartDateTime JD time humanTime humanTime1

this gives,

2020-06-25 00:00:00.0

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

[richgalloway]

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")