If you want to round these epochs to any interval (in your case 10 minutes = 60 * 10 seconds), you can make an integer division of this epoch by 600 (so, you get rid of the decimals) and multipy back by this 600. PostgreSQL (and I think, SQL in general) uses "EXTRACT (EPOCH FROM ts)" to get this value. The " epoch" of a timestamp represents the number of seconds elapsed since a certain time and date (1st Jan 1970, 00:00:00). You do that by representing timestamps as "number of seconds since a certain date and time". You can do the same with times, as sooun as you convert timestamps to integers (or bigints). This is a standard way to "rounding down" numbers to bin them, given a certain bin size (12, in this case). The trick lies in the fact that your division is an integer division, and the result is an integer number with no fractional part. If instead of rounding time intervals you would like to round integer numbers, let's say to "the nearest dozen", you would do the follwing operation: number::integer / 12 * 12Īlthough dividing and multiplying by 12 seems to "just do nothing", it actually gets rid of the "fraction of a dozen" in your number.
, lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp)
SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp If "groups" are defined by gaps of 10 minutes or more between rows of the same hero: SELECT hero Groups defined by gaps of 10 or more minutes It's a reserved word in standard SQL and base data type in Postgres. I advise not to use the "timestamp" as identifier. The CASE expression only adds an end_time if more than one rows fall in the same 10-minute interval. After the cast to integer ( ::int), integer division ( / 10) effectively rounds to 10-minute intervals ( 0 - 5). Refer to the chapter Date/Time Functions and Operators in the manual.ĮXTRACT(minute FROM timestamp) extracts the minute part of the time The expression. , EXTRACT(MINUTE FROM timestamp)::int / 10 , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time I suggest to group by a combination of "hour" and 10-minute interval: SELECT hero
0 kommentar(er)
