how to add/subtract time to a timestamp field

[Anonymous]

HI,

I've a field like this:   08 /11/ 2013 16:00:00 and I need to add /subtract a time value , for examples 90 minutes.

How can I do it ?

Help please

Thanks in advance

[fred_s]

Hi Bibopipo,

Maybe there are better answers, but this one does his job (I guess 🙂

[fred_s]

Sorry, this one is a bit better.. forgot to format the new time (03:05 was 3:5)

[Anonymous · Accepted solution]

Bobipipo

Qlikview [like many things] holds dates as numbers and the decimal part is the fraction of a day, as in 90 minutes is (90/24/60).  And you can then simply add / subtract (90/24/60) from the numeric data.

Paste the below into a text and you see it in action.

= date ( date#('08/11/2013 16:00:00' , 'DD/MM/YYYY hh:mm:ss') , 'DD/MM/YYYY hh:mm:ss')

& chr(10) &

date ( date ( date#('08/11/2013 16:00:00' , 'DD/MM/YYYY hh:mm:ss') , 'DD/MM/YYYY hh:mm:ss') + (90/24/60) , 'DD/MM/YYYY hh:mm:ss')

Best Regards, Bill Markham

[fred_s]

Thanks Bill.

Didn't know about the decimal part.

[qlikviewforum]

Hi Bill,

Can you please help me out too in the below post? It is little urgent!

http://community.qlik.com/thread/128183

[#7]

Hi,

Try this one to add some hours with your time stamp

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

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

[qlikviewforum]

Hi Irfan,

Can you please help me out in below post too?

[#9]

Hi,

Use following code to add time stamp with your time

ID,

     timestamp#(YourField, 'YYYY-MM-DD hh:mm:ss') AS OldTime,

         Timestamp(YourField+Time#(5,'hh')) as NewTimeHours,

          Timestamp(YourField+Time#(90,'mm')) as NewTimeMin

          Timestamp(YourField+Time#(30,'ss')) as NewTimeSec

[qlikviewforum]

Hi Irfan,

As I mentioned mine is not hard coded. In our case we are calculate the time by the dashboard to refresh using the interval. We would like to add this output the current refresh start time so that we can calculate the approximate refresh time. Hope my requirement is understandable now.