convert seconds to hh:mm:ss in script

[#1]

I have a field from SQL that is the number of seconds worked. I would like to convert this to a format of hh:mm:ss and still be able to do aggregations of the data.

In SQL I would use code like:

//    RIGHT('0' + CAST("time_in_seconds" / 3600 AS VARCHAR),2) + ':' +

// RIGHT('0' + CAST(("time_in_seconds" / 60) % 60 AS VARCHAR),2)  + ':' +

// RIGHT('0' + CAST("time_in_seconds" % 60 AS VARCHAR),2)

QlikView errors when I put this code in the Select statement.

I've seen some ways to format seconds on the front end, which I may end up going with, but I would rather the data be formated appropriately to begin with, so I don't take any extra memory to accomplish this tranformation, especially, since I will need that memory to handle summing the number of seconds and minutes.

I would greatly appreciate any assistance!

Greg

[sunny_talwar · Accepted solution]

Try this:

Interval(Interval#(time_in_seconds, 'ss'), 'hh:mm:ss') as Time

[maxgro]

s:

load *, seconds / (24*60*60) as newfield;

load * inline [

seconds

3600

1800

7200

7800

];

then format newfield in number tab

[sunny_talwar]

Massimo Grossi‌ is there a performance benefit of using your method over this?

s:

load *, Interval(Interval#(seconds, 'ss'), 'hh:mm:ss') as Time;

load * inline [

seconds

3600

1800

7200

7800

];

Output is the same

Just out of curiosity

[#5]

Thank you both! The first example worked perfectly!

This is very similar to converting it in the front end. I'm still learning how to apply front end script versus back end script.

If there is a performance gain with either option, I would love to know.

Thanks again!

[maxgro]

I tried a load, this is the result

t: load rowno() as id AutoGenerate 10000000;

a11:

load Interval(Interval#(rowno(), 'ss'), 'hh:mm:ss') as Time

resident t;

store a11 into a11.qvd (qvd);

a12:

load id / (24*60*60)  as Time2

resident t;

store a12 into a12.qvd (qvd);

25/06/2015 21:56:17: 0013  t:

25/06/2015 21:56:17: 0014  load rowno() as id AutoGenerate 10000000

25/06/2015 21:56:17:       1 fields found: id, 10.000.000 lines fetched

25/06/2015 21:56:26: 0017  a11:

25/06/2015 21:56:26: 0018  load Interval(Interval#(rowno(), 'ss'), 'hh:mm:ss') as Time

25/06/2015 21:56:26: 0019  resident t

25/06/2015 21:56:26:       1 fields found: Time, 10.000.000 lines fetched

25/06/2015 21:57:35: 0020  store a11 into a11.qvd (qvd)

25/06/2015 21:57:38: 0023  a12:

25/06/2015 21:57:38: 0024  load id / (24*60*60)  as Time2

25/06/2015 21:57:38: 0025  resident t

25/06/2015 21:57:38:       1 fields found: Time2, 10.000.000 lines fetched

25/06/2015 21:57:44: 0026  store a12 into a12.qvd (qvd)

[sunny_talwar]

Thanks Massimo Grossi‌ for sharing the information, but there are few things I would like to share and get your feedback on:‌

1) I see the difference in size , but why is the formatting different?

May be the formatting is causing the size to be different?

2) And also I changed the script a little

t: load rowno() as id AutoGenerate 10000000;

a11:

load Interval(Interval#(rowno() id, 'ss'), 'hh:mm:ss') as Time

resident t;

store a11 into a11.qvd (qvd);

a12:

load id / (24*60*60)  as Time2

resident t;

store a12 into a12.qvd (qvd);

and saw big jump in performance:

Let me know what you think?

Best,

Sunny

P.S. Thanks for sharing a method to do performance testing.

[#8]

Sorry, remember I'm still fairly new to this.  Would you be able to explain how you are doing the performance testing?  I know we are a way off topic from the original question.

If I understand Massimo's post right, the interval option took from 21:56:26 to 21:57:35, where as the calculation went from 21:57:38 to 21:57:44, so basically the difference of 9 seconds to 6 seconds.

In sunindia's test the interval option too from 4:06:00 to 4:06:15 and the calculation took from 4:06:18 to 4:06:23, which would be the difference between 15 seconds and 5 seconds.

Am I understnading this correctly?

How did you create these files?

Thanks!

[sunny_talwar]

You are absolutely right. I actually did not do the calculations, but the numbers look fairly close time wise

[#10]

So how did you actually do this test?