Manipulating Timestamps

[#1]

Hi all,

I'm having a bit of trouble calculating a "period" field based on two timestamps: a reference time stamp for the end of a period and the time stamp of an event.

Left Join(AttComp_Imp)

LOAD *

  ,if([Elapsed Time]<[Period 1 End],1

  ,if([Elapsed Time]<[Period 2 End],2

  ,if([Elapsed Time]<[Period 3 End],3

  ,if([Elapsed Time]<[Period 4 End],4

  ,if([Elapsed Time]<[Period 5 End],5

  ,if([Elapsed Time]<[Period 6 End],6

  ,if([Elapsed Time]<[Period 7 End],7

  ,8)

  )

  )

  )

  )

  )

  ) As [Match Period]

Resident AttComp_Imp;

The resulting values are all null - and have been when I have tried with Time(), Time#(), Timestamp() and Timestamp#() around the individual elements and the calculation as a whole.

In a chart table, the calculations work. I need to do the calculation in the script.

Thanks

CS

[whiteline]

Hi.

It seems that you Time and Period fields are just text and should be converted to dates with # function.

*#() functions convert text into date while others just change the text format of date.

Post the source data.

[#3]

Source data cannot be provided but it comes from an excel which is timestamp format.

[#4]

As an example:

Table 1:

Event, Time

1, 00:10:02

2, 00:57:13

Table 2:

Period, End Time

1, 00:15:00

2, 00:45:00

3, 01:00:00

[#5]

Try using Interval()

[#6]

I have tried

Interval(Time([Elapsed Time])-Time([Period 1 End]))

And

Interval([Elapsed Time]-[Period 1 End])

and both return null values when used in the script (they both work in charts).

I have declared the fields as Time() on import as well.

[#7]

easiest way to deal with it would be to convert them to numbers to achieve what you are after Callum

[whiteline]

Hi.

So the Time values are just strings.

Use something like Time#([End Time], 'hh:mm:ss') to convert the strings into timestamps to be able to compare them with <>.

Again, Time() changes the text format of dual value (both number and text),  while Time#() converts the string into timestamps (dual).

[d_pranskus · Accepted solution]

Hi

I built a sample application. Please have a look.

Cheers

Darius

[#10]

Thanks all for your comments and help!

Darius, the solution used in the example has worked perfectly. Thanks very much!