Time Interval Help

[abhaysingh]

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

[SreeniJD]

Abhay - Can you please give us more clarity?

Sreeni

[abhaysingh]

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

[jonathandienst]

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

[jonathandienst]

[abhaysingh]

Hi Jonathan,

thanks for revert.. but not getting output..

[sasiparupudi1]

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;

[maxgro]

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;

[MarcoWedel]

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

[abhaysingh]

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