Back to top

Dates in Drupal

So, I'm converting a site from a current static HTML version to a drupalized version and in the process we are taking comments that were created over email from a "contact me mailto:" email on the old site and putting them into forum discussions on the new site. Most of the discussions were "receive email, send response" variety, so it's just a node and a comment.

In order to give a feel of the time of the original email and the response, we used the node administration facility of setting the time when creating the nodes. That works great, but when you enter comments you don't have the same control over the time. So, we calculated the average response time in the emails and decided to set comments to be that far in the future after their node. The first curious thing to me was figuring out what dates in Drupal are based on - turns out it's the Unix Timestamp which is fairly common.

So, armed with the knowledge that the average comment lag was 183807 seconds, I ran the following sets of queries to get the desired offset:

mysql> create table gjk_junk as SELECT n.nid, created +183600 as new_time FROM node n inner join comments c on n.nid = c.nid;
Query OK, 19 rows affected (0.96 sec)
Records: 19 Duplicates: 0 Warnings: 0

mysql> create table comment_bak as select * from comments;
Query OK, 19 rows affected (1.61 sec)
Records: 19 Duplicates: 0 Warnings: 0

mysql> update comments c, gjk_junk g set timestamp = new_time where c.nid = g.nid;
Query OK, 19 rows affected (0.20 sec)
Rows matched: 19 Changed: 19 Warnings: 0

mysql> select c.cid, c.timestamp, b.cid, b.timestamp from comments c inner join comment_bak b on c.cid = b.cid;
+-----+------------+-----+------------+
| cid | timestamp | cid | timestamp |
+-----+------------+-----+------------+
| 2 | 1140696532 | 2 | 1141291314 |
| 3 | 1110370045 | 3 | 1141291509 |
| 4 | 1140091558 | 4 | 1141291624 |
...........you get the idea.........
+-----+------------+-----+------------+
19 rows in set (0.19 sec)

There should be a way to do this all in one step with subqueries (I'm using mysql5), but making a temp table is easy and helped me keep my steps straight.

Now the process is preserved for others (and for when I have to run it again - somehow I think Dave is going to make me do that... :)

People Involved: 

Comments

One interesting note,

One interesting note, however, is that the dates in the forum's main views don't change as a result. While the new comment date may now be listed as several years ago on the page itself, in the listing of all nodes it shows as having been updated just a few hours ago. Also, this updates all replies, so if you have a string of replies to a single thread, they all look like they came in at the same time. None of this particularly confounds me; it's just interesting to note.

definite weaknesses

There are definitely problems with this script like the ones you mentioned.

For performance reasons a lot of things in Drupal are denormalized (e.g. count of comments on a node, time of last comment on a node) and doing "fixes" like this don't take those into account. It's also possible for those values to get corrupted when you delete a user. I contributed a patch to 4.7 that fixes the "forum topic disappears when a user who makes a comment is deleted" which "fixes" the problem but doesn't solve the fact that there is still a comment recorded on the node with a false time.

For the "multiple comments on a node all get the same time" that wasn't a problem here because of the relatively small number of nodes with multiple comments (e.g. 1 in my case) but if that were a problem you wanted to solve there are ways around that either using multiple queries or a php script to set the time at successively longer intervals for subsequent comments.

Pingback

[...] on from the project I just mentioned…I wanted to remove the list of pages (which is analogous to a table of contents) from the [...]