On the Phenomenon of “I’m not dead” Posts
September 28th, 2006
It apparently is a bother to report that there is no news - that a blog, or a column, or a subject simply doesn’t have anything to speak of at the time.
Personally, I’m trying to make a TFHC client in Flex 2 right now, for a class - but that doesn’t mean that there is anything to write about.
None the less…
Every wordpress post has a date ascribed to it automatically. Theoretically, there is an SQL query that would give you the average time between posts - I don’t want to work on that logic right now see logic below. Multiply that by a constant, say 3, and add the time of your last post, and you get ‘you usually would have made three posts by now’.
That’s when a psuedo post pops up.
This is a test of the automated lazyness system. You may assume I’m dead - or just lethargic. Whatever.
A SQL Query
SELECT
AVG( TIMEDIFF( p1.post_date, (
SELECT MAX( p2.post_date )
FROM `wp_posts` AS p2
WHERE p2.post_status = 'publish'
AND p2.post_date < p1.post_date )
)
) AS diff
FROM `wp_posts` AS p1
WHERE p1.post_status = 'publish'
ORDER BY p1.post_date ASC
The problem with this code is that it is not immediately obvious what it’s result means. For example, when I run it, I get “102.5663″. Days? NO. Hours? Sounds a little off.
So let’s take a step back.
SELECT p1.post_date, (
SELECT MAX( p3.post_date )
FROM `wp_posts` AS p3
WHERE p3.post_status = 'publish'
AND p3.post_date < p1.post_date
) AS prevDate,
TIMEDIFF( p1.post_date, (
SELECT MAX( p2.post_date )
FROM `wp_posts` AS p2
WHERE p2.post_status = 'publish'
AND p2.post_date < p1.post_date )
) AS diff
FROM `wp_posts` AS p1
WHERE p1.post_status = 'publish'
ORDER BY p1.post_date ASC
… and somthing is messed up - or is it?
| Sample Result - from PHPMyAdmin | ||
|---|---|---|
| post_date | prevDate | diff |
| 2005-12-07 15:54:52 | 2005-12-02 19:30:33 | 116:24:19 |
| 2005-12-11 23:42:54 | 2005-12-07 15:54:52 | 103:48:02 |
| 2005-12-14 03:32:14 | 2005-12-11 23:42:54 | 51:49:20 |
| 2005-12-16 12:22:04 | 2005-12-14 03:32:14 | 56:49:50 |
| 2005-12-16 16:18:25 | 2005-12-16 12:22:04 | 03:56:21 |
| 2005-12-19 02:19:30 | 2005-12-16 16:18:25 | 58:01:05 |
| 2005-12-20 12:00:12 | 2005-12-19 02:19:30 | 33:40:42 |
| 2005-12-22 12:27:02 | 2005-12-20 12:00:12 | 48:26:50 |
| 2005-12-22 12:27:13 | 2005-12-22 12:27:02 | 00:00:11 |
| 2005-12-28 00:04:31 | 2005-12-22 12:27:13 | 131:37:18 |
| 2005-12-29 00:31:14 | 2005-12-28 00:04:31 | 24:26:43 |
| 2006-01-04 00:23:28 | 2005-12-29 00:31:14 | 143:52:14 |
| 2006-01-05 01:14:29 | 2006-01-04 00:23:28 | 24:51:01 |
| 2006-01-18 17:59:12 | 2006-01-05 01:14:29 | 328:44:43 |
| 2006-01-18 18:05:35 | 2006-01-18 17:59:12 | 00:06:23 |
The difference between 3 August and 16 September - the longest break I’ve had - was 1053 hours. Would a median measure be more accurate? Perhaps. Or, pehaps, use p2.post_modified instead of p2.post_date? *shrugs*
-Sud.
Posted in Overanalytical |