Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bibopipo
Creator II
Creator II

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
bill_markham
Champion III
Champion III

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
Partner

Hi Bibopipo,

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

fred_s
Partner
Partner

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

bill_markham
Champion III
Champion III

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

fred_s
Partner
Partner

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

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

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.