How to convert a timestamp to the nearest minute?

[josephinetedesc]

I have a problem:

I have a series of events that occur within a number of milliseconds of each other.

How do I show all the timestamps that occur within the same MINUTE   - Perhaps I need to do it in SQL before it gets to QlikView?

Jo

[maxgro]

maybe with a new field rounded at the minute

Z:

load

  timestamp(today() + rand()) as ts

AutoGenerate 10;

Left Join (Z)

load

  ts,

  timestamp(round(ts, 1/(24*60))) as tsmin

Resident Z;

[Frank_Hartmann]

this might be helpful:

Milliseconds in timestamps

[sunny_talwar]

You might this link to be helpful as well:

Re: Remove Seconds from a Time Field

[josephinetedesc]

Hi All

sorry it has taken so long to respond:

Date(Floor(CourseStartDateTime), 'dd/MM/yyyy  h:mm:ss TT') as noSeconds,

Date(CourseStartDateTime,'dd/MM/yyyy h:mm') AS CourseStartDateTimeA,

CourseStartDateTime

this will give me the day as a selection

so in order to sort by date and then by ID but without taking into account the milliseconds I can use Date(Floor(CourseStartDateTime), 'dd/MM/yyyy  h:mm') as noSeconds

BUT ... what if i actually want to sort within minutes - because has this just taken away ALL the hours and minutes? 

[sunny_talwar]

Try this:

Date(Floor(CourseStartDateTime, (1/1440)), 'DD/MM/YYYY') as noSeconds

[jonathandienst]

I would create separate date and time values. This will reduce the number of distinct values which will save space and improve performance:

Date(Floor(CourseStartDateTime)) as Date,

Time(Round(Frac(CourseStartDateTime), 1/(24*60))) as Time

Then use the Time field to sort in minutes.

The Importance Of Being Distinct

[josephinetedesc]

Hi Jonathan and Sunny

I don't think I am explaining the problem well.

I think I can explain it better by:

if I use dayName(CourseStartDateTime) - this groups all the timestamps which occur on the one day together.  I want to know if there is some other function or formula that I can use that will group all timestamps that happened within the same hour together.  Or that happened within the same second together.

Although thinking further this will not help those instances when a group of activities will take place say from 10:59 followed by 11:00.

Jo

but maybe I should try to do the conversion in sql???

[#9]

Lets say you have a timestamp like 2016-09-02 17:01:25.123

Then you can use functions such as

Hour(timestamp) -> 17

Minute(timestamp) -> 1

Second(timestamp) -> 25

And also

Date(timestamp) -> Dual('2016-09-02', Num(timestamp))

Time(timestamp) -> Dual('17:01', Num(timestamp))

For the Date() and Time() you may note that the field still contains the full numeric representation of the timestamp, but added a string representation to represent the timestamp in charts and list boxes. This object is however different to another timestamp from the same Date with the same representation, which means that it may appear as if a list box contains the same date several times.

This is why you get a lot of recommendations to truncate the timestamps. Qlikview is the most efficient when you have few unique values, and timestamps stored at a millisecond level are rarely unique.

Say for instance you want to use the Time() representation, but round the timestamp to minute level. In that case you may use the Floor() function to round down.

Time(Floor(timestamp, 1/(24*60))) -> Dual('17:01:', rounded timestamp)

or for better readability as

Time(Floor(timestamp, Interval#('1', 'm'))) -> Dual('17:01:', rounded timestamp)

Which is what I tend to prefer as I don't have to do any calculations to see that it is floored to a minute level.

[MarcoWedel]

Timestamp#(Timestamp(yourtimestamp,'MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm')