Badge based on 'read time'

data & reporting
1

@PJH do you implemented a badge on “read time” yet? :smiley:

2

Nope, not yet. May look into it sometime…

3

It could be really cool! Looking forward to your query :smiley:

4

There is actually a time_read field in user_stats - should be fairly simple to use that, but bear in mind I think it’s fudged somewhat (leaving a tab open for 24 hrs won’t, for example, add 24 hrs to your time reading.)

I did mess around with it else-forum back in October, though not sure if I got it quite right…

[postgres@sofa ~]$ sql_tdwtf time_read 

SELECT u.username, (time_read /86400 || ' days') || ' ' || TO_CHAR((time_read % 86400 || ' second')::interval, 'HH24:MI:SS') AS duration
FROM user_stats us
JOIN users u ON u.id=us.user_id
ORDER BY time_read DESC
LIMIT 25

    username     |     duration     
-----------------+------------------
 HardwareGeek    | 28 days 04:51:56
 ChaosTheEternal | 27 days 07:02:10
 PJH             | 23 days 09:40:24
 boomzilla       | 20 days 15:46:17
 aliceif         | 19 days 17:45:37
 ben_lubar       | 16 days 22:27:15
 Luhmann         | 16 days 21:06:04
 Onyx            | 16 days 07:52:28
 Arantor         | 16 days 06:16:00
 abarker         | 16 days 05:37:31
 faoileag        | 14 days 17:40:01
 Maciejasjmj     | 14 days 11:29:28
 cartman82       | 14 days 09:34:25
 Keith           | 14 days 05:16:52
 dkf             | 14 days 03:28:55
 blakeyrat       | 13 days 18:54:00
 darkmatter      | 13 days 15:24:54
 accalia         | 13 days 02:27:41
 Zecc            | 13 days 01:04:15
 antiquarian     | 12 days 01:04:01
 Matches         | 11 days 19:21:03
 VinDuv          | 11 days 07:47:35
 loopback0       | 10 days 12:45:37
 locallunatic    | 10 days 12:38:35
 reverendryan    | 10 days 08:41:02
(25 rows)

Edit - for comparison, that query returns the following as of 0400UTC this morning:

    username     |      duration
-----------------+--------------------
 HardwareGeek    | 56 day(s) 19:12:15
 ChaosTheEternal | 49 day(s) 22:41:29
 accalia         | 46 day(s) 13:42:15
 boomzilla       | 44 day(s) 17:11:23
 aliceif         | 42 day(s) 04:06:41
 PJH             | 41 day(s) 05:50:33
 reverendryan    | 37 day(s) 07:46:37
 Onyx            | 34 day(s) 20:06:48
 dkf             | 34 day(s) 06:35:03
 abarker         | 32 day(s) 19:26:00
 Luhmann         | 31 day(s) 22:07:55
 ben_lubar       | 31 day(s) 15:37:57
 loopback0       | 31 day(s) 00:54:37
 blakeyrat       | 30 day(s) 13:40:02
 Maciejasjmj     | 27 day(s) 00:17:55
 Jaloopa         | 26 day(s) 17:59:05
 Polygeekery     | 25 day(s) 09:11:23
 antiquarian     | 25 day(s) 02:50:19
 Zecc            | 24 day(s) 07:41:55
 VinDuv          | 22 day(s) 10:42:21
 locallunatic    | 22 day(s) 09:30:10
 cartman82       | 22 day(s) 01:41:44
 tar             | 21 day(s) 22:43:10
 PleegWat        | 21 day(s) 10:25:17
 Buddy           | 21 day(s) 05:23:12
(25 rows)

Elapsed: 0.031s
1 Like