topic Truncate seconds from Timestamp in QlikView

Truncate seconds from Timestamp

Wed, 02 Jun 2010 01:02:31 GMT

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.

Truncate seconds from Timestamp

johnw — Wed, 02 Jun 2010 01:43:29 GMT

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

timestamp(floor(YourTimestamp*1440)/1440)

Truncate seconds from Timestamp

Wed, 02 Jun 2010 22:07:55 GMT

Thanks John. Your solution works like a charm !

Truncate seconds from Timestamp

Clever_Anjos — Fri, 11 Jun 2010 20:11:05 GMT

I think that

time

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

would be easier to read or mantain

Truncate seconds from Timestamp

johnw — Fri, 11 Jun 2010 20:38:24 GMT

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.

Truncate seconds from Timestamp

Thu, 14 Oct 2010 12:14:36 GMT

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

Truncate seconds from Timestamp

johnw — Thu, 14 Oct 2010 16:57:34 GMT

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')

Truncate seconds from Timestamp

Mon, 18 Oct 2010 15:38:03 GMT

Thanks John. That helped.

Re: Truncate seconds from Timestamp

PhilipMertin — Tue, 30 Aug 2022 10:27:58 GMT

I've managed to trim the timestamp to date and hour with the following:

Timestamp(Timestamp#(Text(Timestamp(Now(),'YYYYMMDD_hh')),'YYYYMMDD_hh')).

First it formats it to what you want, then convert that into text to keep only what you see, then convert it back to a timestamp and then format it to a timestamp again.