Having a little fun with Gitlab's join date

Before writing this post, if you went to my personal Gitlab instance and looked at when it says I joined, it specifies August 19th, 2016. Which is correct, but as of writing this, doesn't feel accurate. I've been using Gitlab for far longer, in other instances, and in previous editions of this same instance. (That I forgot to back up. Bad past me!) So I decided this evening to change that to an earlier date.

However, I always like a good Easter egg. So instead of just choosing a random time from the past, let's see if I can't think of something.

Thinking... 🤔

Oh, I know! How about the day the global surveillance apparatus was disclosed? It Is far enough into the past to satisfy my intent to show I've been using Gitlab for a long time. It is also quite relevant to my interests. I'm running the instance specifically because of my distrust in corporate clouds and the global passive surveillance imposed on everyone. If anyone does look up the date (or this blog post) they will get the reference, an easy in-joke.

Affecting the change

Since this isn't exactly a normal thing to do to a user in Gitlab, it's not readily documented. Thankfully it's not complicated - just updating the users.created_at column. Doing a quick search through the code base (ripgrep makes that super quick) to see where it's used shows that it's only for presentational components, like the profile page. So I'm pretty sure what I'm about to do is fairly safe.

The complete SQL statement is more complex than it needs to be. Since I'm being very particular about the date, a simpler statement like the following would work:

UPDATE users SET created_at = date '2013-06-05' WHERE username = 'evaryont';

However, that has one drawback. It sets the hours, minutes, and seconds all to 0! This is normal and expected, but it just looks so artificial. I realize that I'm artificially changing the date because of silly reasons, but if I'm being silly, then let's go for maximum silliness.

PostgreSQL does not have a convenient function to override specific components of a timestamp with arbitrary values. Instead, we'll have to rebuild a full timestamp piece by piece. That's not particularly complicated, but it does make the final statement more verbose:

UPDATE users SET created_at = (date '2013-06-05' + EXTRACT(HOUR FROM localtimestamp) * INTERVAL '1 HOUR' + EXTRACT(MINUTE FROM localtimestamp) * INTERVAL '1 MINUTE' + EXTRACT(SECOND FROM localtimestamp) * INTERVAL '1 SECOND') WHERE username = 'evaryont';

I already know the date, so specify that directly. The hour, minute and second (which pulls in milliseconds too) components are the verbose parts. Since I want a natural-looking time, what is more natural than the current moment? Extract the hour, minute, and second components each individually from localtimestamp and append them to the date, which has been typecasted to a 'timestamp without time zone' value. (This matches the current schema in the table. If it ever changes to including the time zone, use current_timestamp instead.)

Save that to a file on your database server. If you're like me and use the omnibus, that's probably the same server as everything else. Then run the gitlab-psql command to execute it:

/tmp $ sudo /opt/gitlab/bin/gitlab-psql -d gitlabhq_production -f update_joined_at.sql
UPDATE 1

Ta-da!