Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Truncate seconds from Timestamp

Is there an equivalent QV function that truncates the seconds part from a timestamp ?

In Oracle, I'll do something like this - TRUNC(sysdate, 'MI') .

For instance, date today is 06/01/2010 04:58:45 PM, the function above returns as 06/01/2010 04:58:00 PM.

I tried the timestamp() function but this will only affect the column display not the value stored in QV. I need the seconds stored as 00 as I am using this for calculation.

1 Solution

Accepted Solutions
johnw
MVP
MVP

I don't believe there's a function to do so, but I'm guessing this would work:

timestamp(floor(YourTimestamp*1440)/1440)

View solution in original post

7 Replies
johnw
MVP
MVP

I don't believe there's a function to do so, but I'm guessing this would work:

timestamp(floor(YourTimestamp*1440)/1440)

View solution in original post

Not applicable
Author

Thanks John. Your solution works like a charm !

Clever_Anjos
Employee
Employee

I think that

time

(frac(YourTimestamp),'hh:mm:00)) // Note 0 at the end



would be easier to read or mantain

johnw
MVP
MVP

Yes, but it doesn't actually truncate to minutes. It only changes the display. That means that 15 seconds past the minute and 45 seconds past the minute will be maintained as two separate values, not the same value. Now in some cases, changing the display may be good enough. In others, such as in Anthony's case, as he stated explicitly, it won't be. See attached, where a list box for the display-only version shows the "same" value twice, which almost certainly isn't what you'd want.

Not applicable
Author

Hello John,

This might be a very basic question, however how would you truncate the date and time field to display just minutes in the application? I tried using timestamp and few other functions, but no luck .

Thanks in advance.

Amit

johnw
MVP
MVP

To display just minutes, don't include seconds or fractions of a second in the format:

time(MyTimeField,'hh:mm')
timestamp(MyTimestampField,'DD/MM/YY hh:mm')

To actually remove the seconds from the underlying data, there would be multiple approaches. Here's one:

time(floor(MyTimeField,1/(24*60)),'hh:mm')
timestamp(floor(MyTimestampField,1/(24*60)),'DD/MM/YY hh:mm')

Or did you really mean just minutes, and not hours or the date either?

time(MyTimeField,'mm')
timestamp(MyTimestampField,'mm')
time(floor(frac(MyTimeField*24)/24,1/(24*60)),'mm')
timestamp(floor(frac(MyTimestampField*24)/24,1/(24*60)),'mm')

Not applicable
Author

Thanks John. That helped.