4 Replies Latest reply: Feb 12, 2018 2:53 AM by alessandro trinca

# How to create Timestamps between two Date/Hours

Hi,

could anyone help me in this?

I've to gather data from an Excel file in which there's just some values, by date and hour.

Loading that data I've to fill all the rows between two date/hours, automatically creating ALL the inter-current date/hours; after do this I've to divide the next value by the inter-current rows count, but this is simple.

The main problem is the first: how to fill all rows creating all the inter-current date/hours.

This is an example:

Alessandro

• ###### Re: How to create Timestamps between two Date/Hours

Check here on how to create all dates between two dates using while loop...

Loops in the Script

Same logic will go for DateTimes

• ###### Re: How to create Timestamps between two Date/Hours

maybe something like this to create hours between 2 date

// test data

S:

Date,

Floor(rand()*100) as Value1,

Floor(rand()*100) as Value2

inline [

Date

18/01/2018 03:00:00

17/01/2018 03:00:00

16/01/2018 16:00:00

16/01/2018 08:00:00

15/01/2018 22:00:00

31/12/2017 23:00:00

];

S1:

RowNo() as Id, Value1, Value2,

Date, Alt(Peek(Date), Date) as DateTo

Resident S

Order By Date desc;

DROP Table S;

F:

*,

Timestamp(Date + (IterNo()-1) / (24)) as DateTime

Resident S1

While (Date + (IterNo()-1) / (24)) <= (DateTo);

DROP Table S1;

• ###### Re: How to create Timestamps between two Date/Hours

Hi Massimo,

thanks for the suggestion.

Just one last question: how can I split values dividing them by the number of Days/Hours Between two dates, using your solution?

Example:

Date

05/02/2018  Value 1=1000  Value2=1500

Previous Date:

28/01/2018 Value 1:500 Value 2= 800

I've to divide the last Values (1000 and 1500) splitting them for all Days and Hours between 29/01/2018 (the first missed date after 28/01/2018) and 05/02/2018.

Alessandro

• ###### Re: How to create Timestamps between two Date/Hours

Hi Alessandro,

Step 1

I would add a data hour table with ALL dates and hours over the report measurement period.

Table name = T_DateHour

Field Name = DateHour

Field Example = 01 jan 2017 14:00,01 jan 2017 15:00

Step 2

Create same field in your current data

Field Name = DateHour

Field Example = 01 jan 2017 14:00,01 jan 2017 15:00

The new table should then key into your old data using the DateHour field and your report will have the date hour field available to select.

Hope this helps

Darrell