Date Extraction help.

[lalitkgehlot89]

Hi,

I have a table and Data is coming after every 10 second, due to large data application is getting slow.

I want to put the condition while extracting the data should be every 2 minutes only rather than every second?

data set record would be like this

01.01.01 12:00:00

01.01.01 12:00:10

01.01.01 12:00:20

01.01.01 12:00:30

01.01.01 12:00:40

01.01.01 12:00:50

01.01.01 12:01:00

01.01.01 12:01:10

01.01.01 12:01:20

01.01.01 12:01:30

01.01.01 12:01:40

01.01.01 12:01:50

01.01.01 12:02:00

01.01.01 12:02:10

01.01.01 12:02:20

01.01.01 12:02:30

01.01.01 12:02:40

01.01.01 12:02:50

01.01.01 12:03:00

01.01.01 12:03:10

01.01.01 12:03:20

01.01.01 12:03:30

01.01.01 12:03:40

01.01.01 12:03:50

01.01.01 12:04:00

01.01.01 12:04:10

01.01.01 12:04:20

01.01.01 12:04:30

01.01.01 12:04:40

01.01.01 12:04:50

01.01.01 12:05:00

01.01.01 12:05:10

01.01.01 12:05:20

01.01.01 12:05:30

01.01.01 12:05:40

01.01.01 12:05:50

01.01.01 12:06:00

01.01.01 12:06:10

01.01.01 12:06:20

01.01.01 12:06:30

01.01.01 12:06:40

01.01.01 12:06:50

01.01.01 12:07:00

01.01.01 12:07:10

01.01.01 12:07:20

01.01.01 12:07:30

01.01.01 12:07:40

01.01.01 12:07:50

output require like records would be

01.01.01 12:00:00

01.01.01 12:02:00

01.01.01 12:04:00

01.01.01 12:06:00

and so on.

Pls suggest

[sunny_talwar]

May be this:

Table:

LOAD DISTINCT Timestamp(Floor(TimeStamp, 2/(24*60))) as TimeStamp;

LOAD * Inline [

TimeStamp

01.01.01 12:00:00

01.01.01 12:00:10

01.01.01 12:00:20

01.01.01 12:00:30

01.01.01 12:00:40

01.01.01 12:00:50

01.01.01 12:01:00

01.01.01 12:01:10

01.01.01 12:01:20

01.01.01 12:01:30

01.01.01 12:01:40

01.01.01 12:01:50

01.01.01 12:02:00

01.01.01 12:02:10

01.01.01 12:02:20

01.01.01 12:02:30

01.01.01 12:02:40

01.01.01 12:02:50

01.01.01 12:03:00

01.01.01 12:03:10

01.01.01 12:03:20

01.01.01 12:03:30

01.01.01 12:03:40

01.01.01 12:03:50

01.01.01 12:04:00

01.01.01 12:04:10

01.01.01 12:04:20

01.01.01 12:04:30

01.01.01 12:04:40

01.01.01 12:04:50

01.01.01 12:05:00

01.01.01 12:05:10

01.01.01 12:05:20

01.01.01 12:05:30

01.01.01 12:05:40

01.01.01 12:05:50

01.01.01 12:06:00

01.01.01 12:06:10

01.01.01 12:06:20

01.01.01 12:06:30

01.01.01 12:06:40

01.01.01 12:06:50

01.01.01 12:07:00

01.01.01 12:07:10

01.01.01 12:07:20

01.01.01 12:07:30

01.01.01 12:07:40

01.01.01 12:07:50

];

[Gysbert_Wassenaar]

Split the timestamp into a date and a time:

LOAD

     Date(Floor([My TimeStamp]),'DD.MM.YY') as [My Date],

     Time(Floor(Frac([My TimeStamp]),1/720),'hh:mm') as [My Time],

     ...other fields...

FROM

     ....source...

     ;

[Gysbert_Wassenaar]

psst... every two minutes

[sunny_talwar]

Gysbert‌ what font size is that? I can hardly read that

[Gysbert_Wassenaar]

2 pt

4 pt

6 pt  ◄ THIS ONE

8 pt

10 pt

[lalitkgehlot89]

Thanks a lot Sunny & Gysbert,

But I am fetching data from SQL server.

Is there any option to write this with SQL where clause.

Regards,

Lalit Kmar

[sunny_talwar]

So you are deliberately making it difficult for me to read, aren't you?

[sujeetsingh]

Agree with Gysbert just brake the dates and time so that they will reduce the storage size.

[sunny_talwar]

I am sure SQL must have a way to Round()/Floor()/Ceil() things, but for that you would have to ask an SQL expert