topic Re: Is there any way to set up an automation to detect the type of format the stamp is, and then convert to epoch? in 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?

samwatson45 — Tue, 20 Feb 2018 09:57:22 GMT

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.

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

bangalorep — Tue, 20 Feb 2018 11:31:01 GMT

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

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

samwatson45 — Tue, 20 Feb 2018 11:46:22 GMT

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

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

bangalorep — Tue, 20 Feb 2018 13:02:49 GMT

| 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

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

samwatson45 — Tue, 20 Feb 2018 16:04:30 GMT

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.

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

bangalorep — Tue, 20 Feb 2018 16:08:47 GMT

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

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

samwatson45 — Tue, 20 Feb 2018 16:49:59 GMT

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?

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

bangalorep — Wed, 21 Feb 2018 04:21:22 GMT

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.

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

samwatson45 — Tue, 29 Sep 2020 18:09:20 GMT

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.

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

samwatson45 — Tue, 29 Sep 2020 18:10:51 GMT

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

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

bangalorep — Fri, 23 Feb 2018 04:45:29 GMT

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

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

bangalorep — Fri, 23 Feb 2018 05:02:58 GMT

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!

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

samwatson45 — Fri, 23 Feb 2018 16:22:48 GMT

Hiya,

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

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

bangalorep — Mon, 26 Feb 2018 09:01:17 GMT

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)

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

samwatson45 — Mon, 26 Feb 2018 11:35:18 GMT

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?

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

bangalorep — Mon, 26 Feb 2018 11:58:13 GMT

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)