This is a request to pleroma/akkoma and postgresql gods.
Can you illuminate me on how I can get some statistics on myself?
My objective is to get a csv of when I posted stuff (just the date of posting) but I can't quite figure out how I can extract that data from the database.

@eragon

psql command:

\copy (select data->>'published' from objects where data->>'actor'='https://pl.eragon.re/users/eragon' and data->>'type' = 'Note') TO 'eragon.csv';

(doesn’t include boosts)

@eragon oh this doesn’t return it sorted

\copy (select data->>'published' from objects where data->>'actor'='https://pl.eragon.re/users/eragon' and data->>'type' = 'Note' order by data->>'published' asc) TO 'eragon.csv';

this returns it sorted from oldest to newest

@charlotte is that a Postgres specific operator? (the ->>)

I think I need some explanation on what this is. And how that query is constructed

@eragon ->> is an operator for postgres json and jsonb columns so presumably postgres only

pleroma and akkoma use postgres as a sort of document db where mainly just a simplified representation of activitypub (the protocol powering this part of fedi) is stored

->> indexes the json object stored (there is also -> but it works Differently and not as nicely in this case)

‘actor’ is the creator account id of the object, Notes are regular posts

@charlotte Oh! Nice. I didn't knew about that operator, now it makes sense on why you can directly store json in db and still exploit it from postgres.