Fetch data whose date is less than 10hrs from the existing date!

Hi Team,

I’m trying to achieve a filter condition but haven’t found any luck so far. My data has a field as Last_Updated which has date stored in the format 2022-06-23 03:54:45

I want to consider only those records whose date is less than 10 hours than the existing date. How can I achieve this? Shall I use a mapper or a filter snap and I would really appreciate if the logic behind this can be shared.

If the format of the date that is stored in the Last_Updated needs to be transformed as well, please let me know.
Thanks!

Regards,
Darsh

You can do something like this:

Date.parse($Last_Updated) > Date.now().minusHours(10)

1 Like

Thank you @bojanvelevski, should it be > or ‘<’ ?

Now when I reviewed your original post, yes, it should be “<”. You want to consider dates that are less than 10 hours than the current date.

That’s correct and I can explain you in detail.

Let’s assume, I manually execute this pipeline, the current date and time is June 29, 10:40am EST, with the filter condition, we should only consider records whose Last_updated was before 48hrs than the current date and time.

Let’s assume again that 2 records have Last_Updated as June 27, 6pm EST and the difference is 48hrs (it’s easy to explain if we are considering values (i.e. 24, 48, 72) that are multiple of 24) and the other records have Last_Updated as June 25, 10:40am EST, we should ONLY get those 2 records as those fall into 48hr bracket.

Moreover, the Timezone of Last_Updated is UTC as I haven’t changed it to any other timezone, may be that is creating a hurdle?

CC: @dmiller @ptaylor @siwadon @robin @koryknick

Than I go back to my original statement. This expression will do the job:

Date.parse($Last_Updated) > Date.now().minusHours(48)

1 Like

Sure @bojanvelevski, I will test it out and keep you posted.
For now, I’m not seeing any data in my filter snap, may be there is no record that suffice the condition but I will manually change the data from the source system and test if this works.

As always, appreciate your help on this one too.

I wasn’t able to fetch any records with this criteria but that doesn’t mean this is not the solution. It is indeed and thank you @bojanvelevski for your help on this one.

Reason behind no records to be fetched was there were actually no records those were updated in the last 48 hours, I tweaked my criteria to 7 Days and found some for testing purposes and I can confirm that this worked.
Also, with Date.parse, the date format is consistent so not to worry about EST and UTC.

Something new I learned today was

Date.now().minusDays(7)

This above function helped me test the functionality for the last 7 days.

Thanks again @bojanvelevski , signing off from this thread now!

1 Like

@bojanvelevski: I do understand that we closed this thread as we got the desired solution however putting a comment here as the next thing I’m trying to achieve is related to this functionality itself.

We were considering all records whose Last_Updated was less than 48 hrs and then those records were set to update the Salesforce table.
After that, a scheduled job was enabled to run 4 times a day and as we are considering records updated within last 48 hrs, we are seeing duplicate records in each batch, I believe we will continue to see those as the criteria is less than 48 hours, is there a way we can filter out what was updated in the last batch and consider the brand new ones ONLY?

Thanking you in advance for your help :slight_smile:

Regards,
Darsh

In order to exclude duplicates, you’ll have to use a Diff mechanism. Practically, when get records that are updated in the last 48 hours, you need to compare them with Salesforce records added in the last 48 hours by using a Diff snap.

1 Like

Thank you @bojanvelevski, cheked Diff snap, wasn’t aware of this up until now. It suggests that the input streams should be sorted, I will have to use a sorter as my entire pipeline was unsorted.

Yes @darshthakkar, you’ll have to sort the streaming data on the same field you’ll differentiate on.

@bojanvelevski: From the documentation on Diff snap, it can be understood that the input documents should be flat files. For my use case, I will have to compare 2 different systems, one is snowflake and another is Salesforce plus after the comparison I need to update Salesforce.

Does Diff snap allow us to update records to Salesforce if we put a salesforce upsert/salesfroce update after Modified path of Diff?

Thanks!

@darshthakkar - I would recommend carefully considering if the Diff snap is really what you want to use. The Diff snap compares the incoming JSON documents as a whole for equivalence - if anything in the objects are different, it considers it as modified. This includes everything from a difference in the value of a given element, the order of the elements within the object, missing / added elements, element name case sensitivity, or even differences in datatypes.

I think what you probably want to consider instead is the use of a Join snap - probably using Left Outer join type - to check the existence of the target record and apply insert/update methodology based on that condition.

Also, rather than opening a new discussion on a closed topic, please open a new thread so the information shared is under a logical heading for other members to find more easily.

1 Like

I agree to your comments on opening a new thread. I will do the needful.

Regarding Diff snap, I haven’t used it so far, or to put it correctly, I wasn’t even aware of that until today.
So, I have used Joins, Inner join to be precise and then routing the error view to get the actual output.

The concerning part is duplicates so how would I eliminate those will require some serious brainstorming from my side.

Thank you for your suggestion on new thread.

Regards,
Darsh

Discussion continued on Eliminating duplicated records based on the Last Updated Date