Trying to extract delta data from the table using the Unix Epoch Time

[RamBoopalam]

In a table column in oracle it has Unix Epoch time, want to extract one day older data (delta data). How do I filter in Qlik Replicate in the filter condition having Epoch Time.  Please advise

We have a table in Source and it has column which stores the time in Unix Timestamp (Epoch) and I want to get the delta data from the table, basically 
 
Today = 1653652800000 - 5/27/2022

$LAST_REFRESH >= ( 1653566400000 - Epoch Time for 5/26/2022)
Ideally the above has to be in Filter condition for Source
$LAST_REFRESH >= (SYSDATE- 1 of Epoch Time value)

 How can we dynamically extract the data on a daily basis? Please let me know.

[Michael_Litz]

Hi RamBoopalam,

I see that you have opened a support case for this and I will take and work it with you.

Today = 1653652800000 - 5/27/2022

$LAST_REFRESH >= ( 1653566400000 - Epoch Time for 5/26/2022)
Ideally the above has to be in Filter condition for Source
$LAST_REFRESH >= (SYSDATE- 1 of Epoch Time value)

 How can we dynamically extract the data on a daily basis? Please let me know.

Thanks,
Michael

[Steve_Nguyen]

here is an article on timestamp filter that may help :

https://community.qlik.com/t5/Knowledge/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/18807...

[RamBoopalam]

Hi Steve,

Thanks a ton for the quick response, but in my case, the source table.columns which i am trying to extract has Epoch (Unix Time in Seconds) than date.

LAST_REFRESH  is the field in Source and it's datatype is Numeric (19,0)

E.g.

Today = 1653652800000 - 5/27/2022

$LAST_REFRESH >= ( 1653566400000 - Epoch Time for 5/26/2022)
Ideally the above has to be in Filter condition for Source
$LAST_REFRESH >= (SYSDATE- 1 of Epoch Time value)

[john_wang]

Hello @RamBoopalam , copy @Steve_Nguyen @Michael_Litz ,

There should be many different expressions to present 'yesterday' (only date part, means from 0 hour 0 min 0 sec) by Unix Epoch. Because your data contains milliseconds part, one sample for  (SYSDATE- 1 of Epoch Time value) is:

(strftime('%s', date('now','localtime')) - 86400)*1000

Additional comments: 1 day = 86400 seconds; Convert to milliseconds by multiplied by 1000.

The screen copy of the demo:

Hope this helps.

Regards,

John.

[RamBoopalam · Accepted solution]

Thanks a ton John, it worked. Really appreciate your help

.

[john_wang]

Glad to hear that @RamBoopalam , thanks for your feedback.