Postgres timestamp without time zone query example

Discourse Discourse System Administration
1

Currently, a. number of discourse forum categories are auto-bumping very old posts. So, we need a query in postgres to close these very topics in the DB so they will (presumably) not be auto-bumped.

Here are some sample queries to SELECT:

SELECT count(id)  from topics where closed = true and created_at < '2018-01-01 00:30:00'::timestamp without time zone; 

SELECT id, created_at, closed from topics where closed = false and created_at < '2010-01-01 00:30:00'::timestamp without time zone order by created_at desc limit 10;

SELECT count(id) from topics where created_at < '2010-01-01 00:30:00'::timestamp without time zone;

You get the idea.

So, let's close all the topics older than '2018-01-01 00:00:00', first we SELECT to test:

discourse=> SELECT count(id) from topics where created_at < '2018-01-01 00:00:00'::timestamp without time zone;
 count  
--------
 231992
(1 row)

and let's redo this to only count the open topics:

discourse=> SELECT count(id) from topics where closed = false and created_at < '2018-01-01 00:00:00'::timestamp without time zone;
 count 
-------
144
(1 row)

So, let's take a look more closely to check again:

discourse=> SELECT id, closed, created_at from topics where closed = false and created_at < '2018-01-01 00:00:00'::timestamp without time zone order by created_at desc limit 10;
   id   | closed |     created_at      
--------+--------+---------------------
 367097 | f      | 2017-06-18 11:06:10
 361531 | f      | 2016-07-20 18:38:35
 356555 | f      | 2015-11-05 02:21:11
 354801 | f      | 2015-08-21 11:40:01
 353421 | f      | 2015-06-17 15:13:34
 348487 | f      | 2014-11-28 13:40:57
 346031 | f      | 2014-09-05 11:13:59
 345723 | f      | 2014-08-27 08:31:35
 344577 | f      | 2014-07-17 18:38:24
 343391 | f      | 2014-06-05 04:16:07
(10 rows)

Looks good, so let's run it:

discourse=> UPDATE topics set closed = true where closed = false and created_at < '2018-01-01 00:00:00'::timestamp without time zone;
UPDATE 144
discourse=>

So, I will run this query against the Community postgres DB and see if auto-bumping of topics older than this date will stop :slight_smile:

1 Like
2

Production postgres DB actions on live, production Discourse DB:

# cd /var/discourse
/var/discourse# ./launcher enter data;
# pwd
/
# su discourse -c 'psql discourse'
psql (10.12 (Debian 10.12-2.pgdg100+1))
Type "help" for help.
discourse=> SELECT count(id) from topics where closed = false and created_at < '2018-01-01 00:00:00'::timestamp without time zone;
 count 
-------
144
(1 row)
discourse=> UPDATE topics set closed = true where closed = false and created_at < '2018-01-01 00:00:00'::timestamp without time zone;
UPDATE 144
discourse=> SELECT count(id) from topics where closed = false and created_at < '2018-01-01 00:00:00'::timestamp without time zone;
 count 
-------
0
(1 row)
discourse=>

Done.

Let's wait and see if auto-bumping changes and all the very old topics are not auto bumped anymore.