Fill dates and hours between datetime fields to create sequence

[EliGohar]

Hi all,
I have a date time range - Start date and End date.
What I want to Create is a sequence between the start and end date time, like auto-generated rows between the start and end date and hours.

For example, what I have now is:

SourceID, ObjectID, Start time, End time

1, 12345, 28/02/2019 16:00, 28/02/2019 16:05

2, 67890, 01/03/2019 23:59, 02/03/2019 0:02

What I would like to see is:

ObjectID, Date Time

1, 12345, 28/02/2019 16:00

1, 12345, 28/02/2019 16:01

1, 12345, 28/02/2019 16:02

1, 12345, 28/02/2019 16:03

1, 12345, 28/02/2019 16:04

1, 12345, 28/02/2019 16:05

2, 67890, 01/03/2019 23:59

2, 67890, 02/03/2019 0:00

2, 67890, 02/03/2019 0:01

2, 67890, 02/03/2019 0:02

Can you help me how to make this on the script?

Thanks,
Eli.

[mrybalko]

Hello @EliGohar 

LET vMinuteNumConst = 1 / 24 / 60; // 1 day / 24 hours / 60 minutes or MakeTime(0, 1);

tmp:
LOAD
	*,
	Timestamp([Start time] + (IterNo() - 1) * '$(vMinuteNumConst)') as 'Date Time'
While [Start time] + (IterNo() - 1) * '$(vMinuteNumConst)' <= [End time];
LOAD
	SourceID,
	ObjectID,
	Timestamp#([Start time], 'DD/MM/YYYY hh:mm') as 'Start time',
	Timestamp#([End time], 'DD/MM/YYYY hh:mm') as 'End time'
Inline [
SourceID, ObjectID, Start time, End time
1, 12345, 28/02/2019 16:00, 28/02/2019 16:05
2, 67890, 01/03/2019 23:59, 02/03/2019 0:02
];

[jonathandienst]

Code like this:

LOAD SourceID, 
	ObjectID, 
	Date(Floor([Start time], 1/1440) + IterNo() / 1440, 'dd/MM/yyyy hh:mm') as [Date Time]
While (Floor([Start time], 1/1440) + IterNo() / 1440) < [End Time]
;
LOAD SourceID, 
	ObjectID, 
	Date#([Start time], 'dd/MM/yyyy hh:mm') as [Start time],
	Date#([End time], 'dd/MM/yyyy hh:mm') as [End time]
* INLINE
[
	SourceID, ObjectID, Start time, End time
	1, 12345, 28/02/2019 16:00, 28/02/2019 16:05
	2, 67890, 01/03/2019 23:59, 02/03/2019 0:02
];

Not tested, so it may need tweaking. To convert this test code to real system, replace the inline portion with the actual data source.

[EliGohar]

Thanks @mrybalko 

your snippet is exactly what i wanted.

Tested it on my real data and it does the job.

[EliGohar]

Hello Again @mrybalko 

I have an issue with this topic.

Here is the snippet I'm using:

//Variable for time resolution
LET vMinuteNumConst = MakeTime(0,1);

// Creating rows per each object duration 
// Using while loop that adds rows per each minute
DataForOneMinute:
NoConcatenate
 LOAD
 	*,
 	Timestamp(timestamp#( timestamp( [Start Time Test], 'MM.DD.YYYY hh:mm'), 'MM.DD.YYYY hh:mm')   + (IterNo() - 1) * '$(vMinuteNumConst)') as 'Date Time Test'
 While [Start Time Test]  + (IterNo() - 1) * '$(vMinuteNumConst)' <= [End Time Test];
 
Load Distinct	
   ObjectID1 as 'ObjectID Test' , 
   SourceID1 as 'SourceID Test', 
   Source1 as 'Source Test',
   RuleID1 as 'RuleID Test',
   [Path & Area1] as 'Path & Area Test',     
   Timestamp(timestamp#( timestamp( [Start Time1], 'MM.DD.YYYY hh:mm:ss'), 'MM.DD.YYYY hh:mm:ss')) as 'Start Time Test', 
   Timestamp(timestamp#( timestamp( [End Time1], 'MM.DD.YYYY hh:mm:ss'), 'MM.DD.YYYY hh:mm:ss')) as 'End Time Test',
   Duration1 as 'Duration Test'
Resident OneMinuteTable
// Where Clause with specific SourceID1 and/or RuleID
WHERE (SourceID1 = '29') And (RuleID1 = '32') And ((ObjectID1 = 120430) Or (ObjectID1 = 120489)) ;
Drop Table OneMinuteTable;

Please look at the screenshot:

I'm expecting to ignore the seconds in the scenario and get 4 rows per this 2 objects.

ObjectID 120430 should have 2 rows:

01/07/2018 16:02 and 01/07/2018 16:03.

I understand that the seconds are disruptive.

How can I ignore it and generate rows only for a date, hour and minute level?

Thanks!

[mrybalko]

@EliGohar 

If you want to crop seconds modify code to:

Timestamp(round(timestamp#([Start Time1], 'MM.DD.YYYY hh:mm:ss'),  $(vMinuteNumConst))) as 'Start Time Test', 
Timestamp(round(timestamp#([End Time1], 'MM.DD.YYYY hh:mm:ss'), $(vMinuteNumConst))) as 'End Time Test',

or you can crop data strings before converting to timestamp

Timestamp(timestamp#(left([Start Time1], 16), 'MM.DD.YYYY hh:mm')) as 'Start Time Test', 

[EliGohar]

Hi @mrybalko ,

Thanks for your efforts to help again.

I Tried the 2 options you shared but can't get the cropped timestamp:

Do you know why?

I defined the vMinuteNumConst like you said:

LET vMinuteNumConst = 1 / 24 / 60;

EDIT: I also tried to take your original code and use the cropped that you suggested, in the following way:

LET vMinuteNumConst = 1 / 24 / 60; // 1 day / 24 hours / 60 minutes or MakeTime(0, 1);

tmp:

LOAD
	SourceID,
	ObjectID,
Timestamp(round(timestamp#([Start Time], 'MM.DD.YYYY hh:mm:ss'),  $(vMinuteNumConst))) as 'Start Time Test', 
Timestamp(round(timestamp#([End Time], 'MM.DD.YYYY hh:mm:ss'), $(vMinuteNumConst))) as 'End Time Test'
Inline [
SourceID, ObjectID, Start Time, End Time
1, 12345, 28/02/2019 16:00:50, 28/02/2019 16:05:00
2, 67890, 01/03/2019 23:59:45, 02/03/2019 0:02:20
];

and still, no luck,  got no result on both Start and End times.

Thanks.

[EliGohar]

@mrybalko hi again,

Unfortunately, the solution you provided in your first message doesn't work after I checked it with this data:

LET vMinuteNumConst = 1 / 24 / 60; // 1 day / 24 hours / 60 minutes or MakeTime(0, 1);

tmp:
LOAD
	*,
	Timestamp([Start time] + (IterNo() - 1) * '$(vMinuteNumConst)') as 'Date Time'
While [Start time] + (IterNo() - 1) * '$(vMinuteNumConst)' <= [End time];
LOAD
	SourceID,
	ObjectID,
	Timestamp#([Start time], 'DD/MM/YYYY hh:mm') as 'Start time',
	Timestamp#([End time], 'DD/MM/YYYY hh:mm') as 'End time'
Inline [
SourceID, ObjectID, Start time, End time
1, 12345, 28/02/2019 16:02, 28/02/2019 16:03
2, 67890, 01/03/2019 16:02, 01/03/2019 16:03
];

I got this result:

instead of getting 2 records per each ObjectID (for 16:02 and 16:03), can you tell me why please?

Many thanks,

Eli.

[mrybalko]

@EliGohar 

Could you please copy values for variables:

DateFormat

TimestampFormat

Find strings in the script beginning. Something like:

SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]'; 

[EliGohar]

Hi, yes sure:

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';