@PJH do you implemented a badge on “read time” yet?
Nope, not yet. May look into it sometime…
It could be really cool! Looking forward to your query
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