Is there any way to set up an automation to detect the type of format the stamp is, and then convert to epoch?

[samwatson45]

Hi,

I am trying to create a timechart with data coming from multiple sources. There are two different formats of dates which are coming into the dataset. The two formats are:

Feb 14 2018 4:59PM
2018-01-16 09:08:50

Is there any way to set up an automation to detect the type of format the stamp is, and then convert to epoch?
Or will I have to write a bunch of if statements which convert them manually, and if so, any hints/ideas?

Many thanks.

[bangalorep]

You can try this .
You can add the regex and extract the fields Settings » Fields » Field extractions » Add new

| rex field=A "(?\w+\s\w+\s\w+\s.+)"
| rex field=A "(?\w+-\w+-\w+\s.{7,8})"
| eval A1 = strptime(a1,"%b %d %Y %I:%M %p")
| eval A2 = strptime(a2, "%Y-%m-%d %H:%M:%S")
| eval Total = mvappend(A1,A2)

[samwatson45]

I have tried manipulating your query however I keep getting the same error:

Error in 'rex' command: Encountered the following error while compiling the regex '(?\w+\s\w+\s\w+\s.+)': Regex: unrecognized character after (? or (?-

(I am unable to upload an image apparently).

Does this only work in the field extraction or should it be fine just as a search?

[bangalorep]

Try this

| rex field=A "(?<a1>\w+\s\w+\s\w+\s.+)" 
| rex field=A "(?<a2>\w+-\w+-\w+\s.{7,8})" 
| eval A1 = strptime(a1,"%b %d %Y %I:%M %p") 
| eval A2 = strptime(a2, "%Y-%m-%d %H:%M:%S") 
| eval Total = mvappend(A1,A2)

[bangalorep]

If the time formats are from different sources you can create, the calculated field based on the source like this

let me know if this works!

[samwatson45]

Hiya,

Yup, that's what I tried however they are from the same source (as in source field).

[bangalorep]

This creates a new field a with the epoch time of A. You'll have to use a in all your search queries for the epoch time.

Let me know, if this solves the issue.

[samwatson45]

It created one of the new field, B_time, and didn't for A. When I try to count by B_time it tells me there is one value for each time. It basically isn't giving me the results I know it should be giving.

[samwatson45]

Maybe because I am using essentially the same query for both A and B? That is:
eval A_time=strptime(SentToBank, "%b %d %Y %I:%M %p")
eval B_time=strptime(SentToBank,"%Y-%m-%d %H:%M:%S")

[SentToBank being the time variable I am interested in.]

[bangalorep]

Are the two different time formats coming from two different sources?

[bangalorep]

| makeresults 
| eval A="Feb 14 2018 4:59 PM",B="2018-01-16 09:08:50" 
| table A B 
| eval a=strptime(A, "%b %d %Y %I:%M %p") 
| eval b=strptime(B,"%Y-%m-%d %H:%M:%S")

Try this run anywhere search and you can use the evaluation of a and b.
A way forward to automate it would be to use calculated fields where you can use the same evaluations. In order to use calculated fields go to Settings » Fields » Calculated fields » Add new
and put the eval expression strptime(A, "%b %d %Y %I:%M %p") for A and repeat the same for B

[samwatson45]

Your search string works fine for editing individual dates however when I try to add this to the calculated fields it doesn't change the outcome of any of my other searches.

[bangalorep]

The calculated fields create new fields with the epoch time. Do the new fields not work?

[samwatson45]

I'm not sure if I have done it correctly but they are essentially not changing anything within the search results.
Also both the fields (A and B in this case) come in as the same field initially, so do I need a statement to filter them out?

[bangalorep]

Can you please send what the "_time" is for each of these timestamp formats?

[samwatson45]

_time comes out in the normal format

2018-02-08

The issue is the timestamp is the time at which we collected the data from our database, and the time which we want to plot is a variable within the dataset, relating to the recorded time of the events.