Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi Guys,
I have one scenario in which i getting data every second a day
i.e. 25-Sep-15 12:00:01 AM
25-Sep-15 12:00:02 AM
25-Sep-15 12:00:03 AM
25-Sep-15 12:00:04 AM
25-Sep-15 12:00:05 AM
25-Sep-15 12:00:06 AM
so on...
25-Sep-15 01:00:01 AM
25-Sep-15 01:00:02 AM
25-Sep-15 01:00:03 AM
25-Sep-15 01:00:04 AM
I want i to extract intervals between then like below
25-Sep-15 02:00:01 AM
25-Sep-15 01:00:01 AM
25-Sep-15 03:00:01 AM
25-Sep-15 04:00:01 AM
25-Sep-15 05:00:01 AM
so on
Any Suggestion??
Abhay - Can you please give us more clarity?
Sreeni
this is the output i want from above data
25-Sep-15 02:00:01 AM
25-Sep-15 01:00:01 AM
25-Sep-15 03:00:01 AM
25-Sep-15 04:00:01 AM
25-Sep-15 05:00:01 AM
regards
Abhay
You could use something like
Load TimeStamp,
...
FROM ...
Where Mod(Frac(TimeStamp) * 24*60*60, 3600) = 1
OR
Load TimeStamp,
...
FROM ...
Where Round(Mod(Frac(TimeStamp) * 24*60*60, 3600)) = 1
Hi Jonathan,
thanks for revert.. but not getting output..
Try like this
Data:
LOAD ts,Timestamp#(ts,'DD-MMM-YYYY hh:mm:ss TT') as ts1,
time(Timestamp#(ts,'DD-MMM-YYYY hh:mm:ss TT'),'s') as tstimeinSec
INLINE [
ts
25-Sep-15 12:00:01 AM
25-Sep-15 12:00:02 AM
25-Sep-15 12:00:03 AM
25-Sep-15 12:00:04 AM
25-Sep-15 12:00:05 AM
25-Sep-15 12:00:06 AM
25-Sep-15 01:00:01 AM
25-Sep-15 01:00:02 AM
25-Sep-15 01:00:03 AM
25-Sep-15 01:00:04 AM
25-Sep-15 02:00:01 AM
25-Sep-15 02:00:02 AM
25-Sep-15 03:00:01 AM
25-Sep-15 03:00:02 AM
];
NoConcatenate
Final:
load * Resident Data
where num(trim(tstimeinSec))=1;
drop Table Data;
Data:
LOAD
ts,
Timestamp#(ts,'DD-MMM-YYYY hh:mm:ss TT') as ts1
INLINE [
ts
25-Sep-15 12:00:01 AM
25-Sep-15 12:00:02 AM
25-Sep-15 12:00:03 AM
25-Sep-15 12:00:04 AM
25-Sep-15 12:00:05 AM
25-Sep-15 12:00:06 AM
25-Sep-15 01:00:01 AM
25-Sep-15 01:00:02 AM
25-Sep-15 01:00:03 AM
25-Sep-15 01:00:04 AM
25-Sep-15 02:00:01 AM
25-Sep-15 02:00:02 AM
25-Sep-15 03:00:01 AM
25-Sep-15 03:00:02 AM
];
NoConcatenate
Final:
load
*,
floor(ts1, 1/24)
Resident Data
Where floor(ts1, 1/24) <> floor(peek(ts1), 1/24)
order by ts1;
drop Table Data;
Hi,
one solution could be:
LOAD *,
Timestamp(Floor(YourTimeStamp,'01:00:00','00:00:01'),'DD-MMM-YY hh:mm:ss TT') as YourIntervalStart;
LOAD Timestamp(Today()-2+IterNo()/86400,'DD-MMM-YY hh:mm:ss TT') as YourTimeStamp,
Ceil(Rand()*1000) as fact
AutoGenerate 1
While Today()-2+IterNo()/86400<=Now();
hope this helps
regards
Marco
Hi Marco,
Need Script help for this,,
i have scenario like below..
Load
Field1,
DateRecorded,
field2
From Source...
Where to write Above script;
Thanks
Abhay