topic Adding or substacting hours from timestamp in QlikView

Adding or substacting hours from timestamp

sridhar_sigired — Mon, 11 Jul 2016 09:27:23 GMT

Hi,

I have a date with timestamp, where i need to show that -3 hours from actual date.

I have tried with different date functions but we can able to do.

Ex: 07/10/2016 01:00:00 000

Output should be 07/09/2016 22:00:00 000;

Can you please give any suggestions?

Re: Adding or substacting hours from timestamp

Chanty4u — Mon, 11 Jul 2016 09:32:10 GMT

try this

= date ( date#(' 07/10/2016 01:00:00 000' , 'DD/MM/YYYY hh:mm:ss') , 'DD/MM/YYYY hh:mm:ss')

& chr(10) &

date ( date ( date#(' 07/10/2016 01:00:00 000' , 'DD/MM/YYYY hh:mm:ss') , 'DD/MM/YYYY hh:mm:ss') +(-180/24/60) , 'DD/MM/YYYY hh:mm:ss')

edited

Re: Adding or substacting hours from timestamp

Chanty4u — Mon, 11 Jul 2016 09:36:01 GMT

or else try below

Timestamp#(YourFieldName, 'YYYY-MM-DD hh:mm:ss') AS OldTime,

Timestamp(YourFieldName+Time#(-3,'hh')) as NewTime

Re: Adding or substacting hours from timestamp

fneumann — Mon, 11 Jul 2016 09:36:08 GMT

Hi!

This seems to work:

Timestamp([Date_Time]-(3/24)) as Timestamp_substracted

Re: Adding or substacting hours from timestamp

sridhar_sigired — Mon, 11 Jul 2016 09:41:58 GMT

Hi,

I am receiving blank information when i copied this in text box.

Re: Adding or substacting hours from timestamp

sridhar_sigired — Mon, 11 Jul 2016 09:44:27 GMT

Still not getting result

Re: Adding or substacting hours from timestamp

effinty2112 — Mon, 11 Jul 2016 09:49:28 GMT

Hi Sridar,

Try:

=Timestamp(Timestamp#('07/10/2016 01:00:00 000','MM/DD/YYYY hh:mm:ss fff') - 1/8,'MM/DD/YYYY hh:mm:ss fff')

Cheers

Andrew

Re: Adding or substacting hours from timestamp

deepakqlikview_123 — Mon, 11 Jul 2016 09:58:32 GMT

=date ( date#('07/10/2016 01:00:00' , 'MM/DD/YYYY hh:mm:ss') , 'MM/DD/YYYY hh:mm:ss')

& chr(10) &

date ( date ( date#('07/10/2016 01:00:00' , 'MM/DD/YYYY hh:mm:ss') , 'MM/DD/YYYY hh:mm:ss') + (-180/24/60) , 'MM/DD/YYYY hh:mm:ss')

Re: Adding or substacting hours from timestamp

sunny_talwar — Mon, 11 Jul 2016 10:50:37 GMT

I think first thing to check here is if your timestamps are read correctly by QlikView. If it has doing as simple as this could work:

TimeStamp(TimeStampField + MakeTime(3)) as NewField

or this in front end

=TimeStamp(TimeStampField + MakeTime(3))

But if timestamps are not interpreted correctly, try to fix that issue before you do this manipulation using TimeStamp#() or Date#() function

Get the Dates Right

Why don’t my dates work?

Re: Adding or substacting hours from timestamp

sridhar_sigired — Mon, 11 Jul 2016 14:25:12 GMT

Thanks Sunny.