Timestamp calculation

[#1]

Hi guys,

please I have this timestamp format in my database timestamp= 2016-11-01 00:00:00.0000000 +00:00 and I convert it to this format Date = 2016-11-01  by using this expression :    Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD')

I want whenever I choose a date for example 2016-11-01, I want to get the value of this timestamp 2016-11-01 05:59:57.1810000 +00:00.

Please help me.

[MindaugasBacius]

The expression have to be in a script not in front end:

Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD') as Date

[#3]

Yes Mindaugas, the expression is in the script and not in front end.

[MindaugasBacius]

Is the field that contains 2016-11-01 05:59:57.1810000 +00:00 data are also in the script and in the same table?

[#5]

yes they in the same table

[MindaugasBacius]

So simple make Table box containing [Date] and [Full Date] fields.

You will see how they are connected.

[rubenmarin]

Hi Lou, you want to sum that exact time part to the date? weird, but it's what I understand from the post... if that's the case you can create a variable wich contains the time part to sum to the dates:

LET vTimeToSum = Num(Time#('05:59:57.5810000', 'hh:mm:ss.fffffff'));

And create the field with the timestamp:

Timestamp(Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD') + $(vTimeToSum), 'YYYY-MM-DD hh:mm:ss.fffffff +00:00')

If it's a different field of your date field you can use this field in expressions and leave the date field as selectable field.

[#8]

Hi Ruben,

I tried your expression: Timestamp(Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD') + $(vTimeToSum), 'YYYY-MM-DD hh:mm:ss.fffffff +00:00') as DD

But when I choose a value for example 2016-11-01 05:59:57.5810000, it gives me the sum of all the values of the date 2016-11-01 and not only the value of this timestamp 2016-11-01 05:59:57.5810000

[rubenmarin]

Hi Lou, this was intended to be in the script (I read above it was on script), creating the fields while loading the data so there will be a selectable field with only the date and a related field with the timestamp.

I don't see why sum all the values, is there a group by or something than loads many dates in one record?

Is there a possibility to upload a sample to make some tests?

[#10]

Hi Ruben,

I attached a data sample.

You will find and the field I want to calculate is : VTagResolvedVirtual.ValueFloat

So when I choose a value frim the field 'da', for example 2016-11-01, it should give me the value of the field 'VTagResolvedVirtual.ValueFloat' that corresponds to this timestamp '2016-11-01 05:59:57.1810000 +00:00', but what I get is the sum of value of the field 'VTagResolvedVirtual.ValueFloat' of all the timestamps of this date 2016-11-01.