Research - My abandoned blog is nearly 2 decades old

Before I archived the blog, I did some research. I wanted to know how many articles I wrote, how many posts I published, and when I published them.

History

The last Weblog post is from 2011-04-03 and links the HTML 5 Editor draft.

The last Article post is from 2009 and offers two free HTML templates for personal sites. My personal site was running on the second one (Helvetica) until 2020.

The installation of the current running blog was in 2008 (moved from VPS A to VPS B).

$ find . -type f -printf '%T+ %p\n' | sort | head -n 1
2008-06-18+11:08:07.0000000000 ./sbin/config/db_settings.conf.php

Last config/code adjustment was 2014-10-21 (PHP Version upgrade).

$ find . -type f ! \( -name "*.cache" -o -name "*.sqlite" \) -printf '%T+ %p\n' | sort -r | head -n 1
2014-10-21+07:32:55.0000000000 ./sbin/includes/functions.inc.php

Database

Get updates by date:

cat content.sqlite | grep -a -o -E '[0-9]{10}' | sort | uniq |awk '{ print strftime("%Y-%m-%d", $1)}' | sort | uniq
2004-12-19
2005-01-08
2005-01-12
2006-11-29
2006-12-17
2007-01-08
2008-06-09
2008-06-10
2008-06-29
2008-08-11
2008-08-17
2008-09-02
2008-09-03
2008-09-04
2008-09-05
2008-09-07
2008-09-17
2008-09-18
2008-09-19
2008-09-22
2008-09-23
2008-09-26
2008-09-27
2008-09-29
2008-10-03
2008-10-04
2008-10-05
2008-10-06
2008-10-07
2008-10-10
2008-10-11
2008-10-13
2008-10-14
2008-10-15
2008-10-16
2008-10-17
2008-10-18
2008-10-19
2008-10-21
2008-10-24
2008-10-25
2008-10-26
2008-10-30
2008-11-02
2008-11-05
2008-11-07
2008-11-08
2008-11-09
2008-11-11
2008-11-15
2008-11-18
2008-11-22
2008-11-26
2008-12-17
2009-02-20
2009-02-23
2009-02-28
2009-03-01
2009-03-23
2009-03-29
2009-03-31
2009-04-07
2009-04-11
2009-04-28
2009-06-08
2009-06-12
2009-06-16
2009-06-17
2009-06-19
2009-06-20
2009-06-21
2009-06-22
2009-06-24
2009-06-25
2009-07-03
2009-07-11
2009-07-12
2009-07-15
2009-07-16
2009-07-19
2009-07-20
2009-08-02
2009-08-03
2009-08-12
2009-08-16
2009-08-21
2009-09-22
2009-10-13
2009-10-23
2009-10-25
2009-10-26
2009-11-01
2009-11-04
2009-11-05
2009-11-27
2010-01-27
2010-02-07
2010-02-22
2010-03-12
2010-03-13
2010-03-17
2010-03-21
2010-03-25
2010-05-22
2010-05-23
2010-05-25
2010-05-29
2010-07-08
2010-07-14
2010-07-28
2010-11-24
2010-12-07
2011-01-18
2011-01-31
2011-04-03
2011-12-13

Let's break down the command step by step:

cat content.sqlite: This reads the contents of the file named content.sqlite and sends it to the standard output stream.
grep -a -o -E '[0-9]{10}': The grep command searches for patterns ([0-9]{10} matches sequences of 10 digits) in the input text (-a treats the file as text, -o outputs only the matched parts, -E enables extended regular expressions).
sort: Sorts the output. In this case, it sorts the Unix timestamps in ascending order since no additional flags are specified.
uniq: Removes adjacent duplicate lines. It ensures that each timestamp appears only once in the sorted output.
awk '{ print strftime("%Y-%m-%d", $1)}': Uses awk to convert each Unix timestamp into a date in the format %Y-%m-%d (year-month-day).
Another sort: Sorts the dates again.
Another uniq: Removes adjacent duplicate dates, ensuring that each unique date appears only once in the final output.
This command sequence extracts Unix timestamps from the content.sqlite file, converts them into human-readable dates in the format %Y-%m-%d, sorts them, removes duplicate dates, and finally presents a list of unique dates sorted in ascending order. Shows all posts from 2004 to 2012. Because i did not find a proper tool to view SQLite2 databases, i had to parse the binary.

This command sequence extracts Unix timestamps from the content.sqlite file, converts them into human-readable dates in the format %Y-%m-%d, sorts them, removes duplicate dates, and finally presents a list of unique dates sorted in ascending order.

Get Posts create/edit by date:

$ cat content.sqlite | grep -a -o -E '([0-9]{10})(.*)' | awk '!seen[$1]++ { print strftime("%Y-%m-%d", $1) " " substr($0, 11) }' | sed -E 's/<[^>]+>//g; s/[0-9]{10}//g' | sort
2004-12-19 ArtikelArtikel//gedit.NET - Artikel - Tutorials, Howtos und Artikel zu Themen wie HTML>
2004-12-19 ArtikelArtikelTutorials, Howtos und Artikel zu Themen wie HTML, CSS, XML, SVG, SEO, JaT
2004-12-19 Artikel - Advance CSS Server Side parsed mit PHPA
2004-12-19 Artikel - CSS Schriftgröße und ZeilenabstandArtiC
2004-12-19 Artikel - Druckvorschau - Cascading Style Sheet zum
2004-12-19 Artikel - Dynamische Webseiten mit SSI Templates
2004-12-19 Artikel - Elemente anzeigen und verbergen
2004-12-19 Artikel - Free HTML/CSS Templates viCard und
2004-12-19 Artikel - Javascript - externe Links in einem n
2004-12-19 Artikel - Javascript - externe Links in einem ndP
2004-12-19 Artikel - KVIrc Windows exec aliasArtikel - KVIrc Windows exec g
2004-12-19 Artikel - phpSQLiteCMS HTML Sitemap Smarty Temp
2004-12-19 Error - FehlerseiteError - FehlerseiteFehlerseite für Fehler wie 401, 403, 404 oder 500ge
2004-12-19 Galerie - Simon - ScreenshotsGalerie - Simon - Screen
2004-12-19 Kontaktieren Sie mich doch bei Frage, Anregungen und Kri
2004-12-19 Services//gedit.NET - Services und Tools für den remote Usergedit, Servicep
2004-12-19 Sitemap - InhaltsverzeichnisSitemap - Inhaltsverzeichnis//gedit.NET - Inhaltsverze
2004-12-19 Sitemap - InhaltsverzeichnisSitemap - InhaltsverzeichnisInhaltsverzeichnis und Sits
2004-12-19 Tools &amp; ServicesTools &amp; ServicesReferenzen und Tools wie HTML Farbräume, Fon
2004-12-19 Tutorials Webentwicklung mit standardisierten WebtechnikenTutorials
2004-12-19 WeblogWeblogEine Liste interessanter, lesenswerter und informativer Websites zu The5[
2005-01-08 Impressum - HaftungsausschlussImpressum - HaftungsausschlussImpressum und Haftungsaus
2005-01-08 Impressum und Haftungsausschluss - DatenschutzImpressum und Haftungsaussch
2005-01-08 Impressum und Haftungsausschluss - Verweise und LinksImpressum und
2005-01-08 SeitensucheSeitensucheHier finden Sie was Sie suchen auf gedit.gedit, Suche, Seitensuche,F
2005-01-08 Seitensuche - GoogleSeitensuche - GoogleBenutzen Sie Google um zu finden was Sie a
2005-01-08 Seitensuche - GoogleSeitensuche - Google//gedit.NET - Seitensuche - Benutzen Sie GH
2005-01-08 Seitensuche - Yahoo!Seitensuche - Yahoo!//gedit.NET - Seitensuche - Benutzen Sie Ya
2005-01-08 Simon Gattner - KontaktSimon Gattner - Kontakt//gedit.NET - Simon Gattner\'s pers`
2005-01-08 Simon Gattner - LesezeichenSimon Gattner - Lesezeichen//gedit.NET - Simon Gat
2005-01-08 &Uuml;ber gedit.NET&Uuml;ber gedit.NET//gedit.NET - Über gedit.NET - Konzepte, Umsetzung
2005-01-08 Who Am I - Remote User, Browser and Script InformationWho Am I - Remote User, Bro
2005-01-12 Creative Commons LicenseCreative Commons LicenseCreative Commons License 3.0 - Namensnennung
2005-01-12 GPLGNU GENERAL PUBLIC LICENSEGNU GENERAL PUBLIC LICENSE - Deutsche Übersetzung der Version
2005-01-12 VERSION 0.4VERSION 0.4//gedit.NET - VERSION HISTORY - &Auml;nderungen, Technik und=
2005-01-12 VERSION 0.5VERSION 0.5//gedit.NET - VERSION HISTORY -  &Auml;nderungen, Technik un
2005-01-12 VERSION HISTORYVERSION HISTORYVersion Historie von geditgedit, VERSION, HISTORY, HISTO
2006-11-29 gedit.NETgedit.NETgedit.NET - Really Simple Syndication - RSS 2.0 Feed
2006-11-29 XML Sitemap
2006-12-17 robots.txt
2008-08-17 blog.fefe.de
2008-09-03 dotfiles.org
2008-09-04 Advanced Apache und mod_rewrite Guide
2008-09-04 Fit f&uuml;r Usability
2008-09-05 www.iconlook.com
2008-09-07 BILDblog.
2008-09-17 I love typography
2008-09-18 Google - The Anatomy of a Large-Scale Hypertextual Web Search Engine
2008-09-19 blog.internet-briefing.ch
2008-09-22 gedit.NET Webloggedit.NET Webloggedit.NET - Weblog - Really Simple Syndicatic
2008-09-22 dev.opera.com
2008-09-23 Who Am I, Remote User Informations
2008-09-27 Sitepoint
2008-09-29 Der Vorleser
2008-10-04 Matt Cutts: Gadgets, Google, and SEO
2008-10-05 Keinen Namen - keinen Titel
2008-10-05 KVIrc exec Alias unter Windows
2008-10-05 Mit Javascript externe Links in einem neuen Fenster &ouml;ffnen
2008-10-05 phpSQLiteCMS HTML Sitemap Smarty Template
2008-10-06 Weblog
2008-10-07 Print Style Sheet mit Druckvorschau
2008-10-10 Projekt Small Corporation
2008-10-11 456 Berea Street
2008-10-11 Project Euler
2008-10-13 Web Robots
2008-10-15 freiesMagazin
2008-10-15 MaxDesign
2008-10-18 Incutio CSS discuss Wiki
2008-10-19 Advance CSS Server Side parsed mit PHP
2008-10-19 phpSQLiteCMS HTML Sitemap Smarty Template mit Bl&auml;tter Funktion
2008-10-21 blog.html.it layoutgala
2008-10-24 A List Apart
2008-10-25 The Web Standarts Project
2008-10-30 Jakob Nielsen\'s Alert Box
2008-11-02 Dynamische Webseiten mit SSI Templates
2008-11-05 mezzoblue
2008-11-05 The Zen Garden
2008-11-08 Elemente anzeigen und verbergen mit Javascript
2008-11-11 Einfach f&uuml;r alle
2008-11-18 XWolf CGI/Perl und SSI
2008-11-22 QuirksMode
2008-11-26 meyerweb.com - Erik Meyer\'s Homepage
2008-12-17 belauscht.de
2008-12-17 M&uuml;nchner Kreis
2009-02-20 Liste von Open Source Content Management Systemen
2009-02-20 Todessto&szlig; f&uuml;r  IE6
2009-02-20 Was bietet CSS 3?
2009-02-20 Yahoo! - Best Practices for Speeding Up Your Web Site
2009-02-23 &lt;abbr&gt; vs &lt;acronym&gt;
2009-02-28 CSS float
2009-03-01 Opera Web Standards Curriculum
2009-03-23 fwpf-webdesign.de
2009-03-23 webtypography.net
2009-03-23 www.bitvtest.de
2009-03-29 Variables CSS-Layout mit verschiedenen Spaltenfarben
2009-03-31 Bundesministerium der Justiz - Leitfaden zur Impressumspflicht
2009-04-11 CSS Schriftgröße und Zeilenabstand
2009-04-11 HTML Farb- und Zeichenreferenzen
2009-04-28 15 Jahre Opera
2009-06-08 CSS Font Stacks
2009-06-08 DZone Code Snippets
2009-06-12 IBM Drupal/OpenSource Web design, develop and deploy Howto
2009-06-12 Top Ten Javavascript Snippets
2009-06-16 Internet Explorer CSS Bugs
2009-06-16 On having layout / Über hasLayout
2009-06-17 SOME  NOTES  ON  BOOKS  AND  PRINTING
2009-06-19 Conditional Comments that Work
2009-06-20 Professionelle Webentwicklung und Wartbarkeit
2009-06-22 Webseiten auf Mobiltelefonen Windows Mobile testen
2009-06-24 Eine (Wieder)Einführung in Javascript
2009-07-03 Language support in Apache through negotiation
2009-07-03 XHTML2 ist tot, es lebe HTML5
2009-07-11 Free HTML/CSS Templates viCard und Helvetica
2009-07-12 Arial gleich Helvetica?
2009-07-15 Law Podcasting - Anwalt-Audio-Blog
2009-07-19 Typographie für Webautoren
2009-07-19 Webfonts Demo und Testseiten
2009-07-20 Guidelines on alt texts in img elements
2009-08-02 CSS Schriftgrößenabgleich
2009-08-02 font-face embedding anhand der OpenType Schriftart Kaffeesatz
2009-08-03 Bitkom Umfrage
2009-08-16 Beispiel für kreative Webgestalltung
2009-08-16 Dropdown low down
2009-08-16 E-Lerningplattform Digital Media for Artists der Kunstuniversität Linz
2009-08-21 W3C Toolbox
2009-09-22 Google und  Keywords Metatags
2009-10-13 00000000mehr...main_menudefault.tpl111111002
009-10-23 W3C Website in neuem Design
2009-10-26 10 Jahre lyrikline.org
2009-11-01 Matthew James Taylor
2009-11-04 Explaining UX Research to Clients
2009-11-04 Richtlinien für barrierefreie Webinhalte (WCAG) 2.0
2010-01-27 Dive Into HTML5: Video on the Web
2010-02-07 Ten principles that contribute to a Googley user experience
2010-03-12 The Social Media Landscape
2010-03-13 Adrian Roselli Browser Archive
2010-03-17 @font-face Font KitsAlle font kits bestehen aus
2010-03-21 Skipfish - Web-Security-Scanner
2010-03-25 Datenschutz bei Onlinenetzwerken
2010-05-22 Browser-Support für HTML5 und CSS3
2010-05-22 Interessante Ausschreibungen für Kreative
2010-05-22 KommDesign Informationsarchitektur
2010-05-23 CKAN - Comprehensive Knowledge Archive Network
2010-05-25 See the Bigger Picture Album<a href=\"http://www.flickr.com/photos/michaelpaukner/sets/062367
2010-05-29 Jarlsberg: Web Application Exploits and Defenses
2010-07-08 Creative Commons und Public Domain Tools
2010-07-14 Microsoft's TrueType core fonts for the web
2010-07-28 W3C vereinheitlicht seine Validierungsdienste
2010-11-24 HTML Geo Tags
2010-12-07 Artikel 5 Grundgesetz
2011-01-18 Offizieles HTML5-Logo des W3C
2011-01-31 Microsoft veröffentlich Internet Explorer auf Virtual PC VHDs
2011-04-03 WHATWG HTML5 specification for web developers

Let's break down the command step by step:

cat content.sqlite: This reads the content of the file named content.sqlite.
grep -a -o -E '([0-9]{10})(.*)': Using grep with options:
- -a: Treat binary files as text.
- -o: Only output the matching part of the line.
- -E: Interpret the pattern as an extended regular expression.
- '([0-9]{10})(.*)': This regular expression searches for a sequence of exactly 10 digits (representing UNIX timestamps) followed by any characters. It captures the timestamp and the rest of the line as separate parts.
awk '!seen[$1]++ { print strftime("%Y-%m-%d", $1) " " substr($0, 11) }':
- awk: A powerful text processing tool.
- '!seen[$1]++ { print strftime("%Y-%m-%d", $1) " " substr($0, 11) }': The awk command does the following:
- !seen[$1]++: Uses an associative array seen indexed by the first captured group from grep (which is the UNIX timestamp). It ensures that only the first occurrence of each timestamp is considered.
- strftime("%Y-%m-%d", $1): Converts the UNIX timestamp $1 to a human-readable date format (%Y-%m-%d).
- substr($0, 11): Extracts the substring from the 11th character to the end of the line (excluding the timestamp). This represents the content after the timestamp.
sed -E 's/<[^>]+>//g; s/[0-9]{10}//g': sed command with two replacement expressions:
- s/<[^>]+>//g: Removes HTML tags using a regular expression that matches any characters between < and >.
- s/[0-9]{10}//g: Removes sequences of exactly 10 digits, i.e., UNIX timestamps.
sort: Sorts the output in ascending order.

Overall, this command chain extracts UNIX timestamps and content from the file, formats the timestamps to human-readable dates, removes HTML tags, removes the timestamps themselves, and finally sorts the output.