Getting Data In

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

Path Finder

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.

0 Karma

Communicator

alt text
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)

0 Karma

Path Finder

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?

0 Karma

Communicator

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)
0 Karma

Communicator

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

let me know if this works!

0 Karma

Path Finder

Hiya,

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

0 Karma

Communicator

alt text

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.

0 Karma

Path Finder

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

0 Karma

Path Finder

Maybe because I am using essentially the same query for both A and B? That is:
eval Atime=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.]

0 Karma

Communicator

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

0 Karma

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

0 Karma

Path Finder

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.

0 Karma

Communicator

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

0 Karma

Path Finder

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?

0 Karma

Communicator

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

0 Karma

Path Finder

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

0 Karma