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]

I missed single quotes and replaced DD <-> MM in timestamp format.

Also changed round function to floor. Difference is:

round: 16:00:45 -> 16:01

floor: 16:00:45 -> 16:00

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(floor(timestamp#([Start Time], 'DD/MM/YYYY hh:mm:ss'), '$(vMinuteNumConst)')) as 'Start time', 
	Timestamp(floor(timestamp#([End Time], 'DD/MM/YYYY hh:mm:ss'), '$(vMinuteNumConst)')) as 'End time'
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
];

[EliGohar]

@mrybalko  still not working.

Please check it with the following inline load example:

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(floor(timestamp#([Start Time], 'DD/MM/YYYY hh:mm:ss'), '$(vMinuteNumConst)')) as 'Start time', 
	Timestamp(floor(timestamp#([End Time], 'DD/MM/YYYY hh:mm:ss'), '$(vMinuteNumConst)')) as 'End time'
Inline [
SourceID, ObjectID, Start Time, End Time
1, 12345, 28/02/2019 16:02:50, 28/02/2019 16:03:01
2, 67890, 01/03/2019 16:12:45, 01/03/2019 16:16:20
];

The result:

I'm expecting to see another record for objectId 120489 with 16:03:00 time

and for objectid 122339 with 16:16:00 

Thanks,

Eli.

[EliGohar]

@mrybalko  Sorry for bothering but can you try to find a solution using the code you provided?

I'm sure it's a small issue that needs to be fixed.

I tried a few things but still can't solve it.

I'm sorry again to bother but I need to find a solution for an important customer.

Thanks, 

Eli.