Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to add/subtract time to a timestamp field

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

9 Replies
fred_s
Partner - Creator III
Partner - Creator III

Hi Bibopipo,

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

fred_s
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

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
Partner - Creator III
Partner - Creator III

Thanks Bill.

Didn't know about the decimal part.

qlikviewforum
Creator II
Creator II

Hi Bill,

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

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

Not applicable
Author

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
Creator II
Creator II

Hi Irfan,

Can you please help me out in below post too?

Not applicable
Author

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
Creator II
Creator II

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.