Splunk Search

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

ssaenger
Communicator

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.

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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")
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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")
---
If this reply helps you, Karma would be appreciated.

ssaenger
Communicator

Hi Rich,

 

Brilliant!, thank you thank worked.

 

ssaenger
Communicator

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?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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")
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...