Truncate seconds from Timestamp

[#1]

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.

[johnw · Accepted solution]

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

timestamp(floor(YourTimestamp*1440)/1440)

[#3]

Thanks John. Your solution works like a charm !

[Clever_Anjos]

I think that

time

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

would be easier to read or mantain

[johnw]

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.

[#6]

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]

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

[#8]

Thanks John. That helped.

[PhilipMertin]

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.