Date/Time Processing and Querying
Learn about data types for date and time in PostgreSQL.
Once the application’s data, or rather the user data, is properly stored as a timestamp with time zone, PostgreSQL allows us to implement all the processing we need to.
Using git repository
As an example dataset, we’re playing with git history this time. The PostgreSQL and the pgloader project history have been loaded into the commitlog table thanks to the git log command, with a custom format and some post-processing—properly splitting up the commit’s subjects and escaping its content. Here’s, for example, the most recent commit registered in our local commitlog table:
select project, hash, author, ats, committer, cts, subjectfrom commitlogwhere project = 'postgres'order by ats desclimit 1;
The column names ats and cts, respectively. Where, ats stands for author commit timestamp and cts stands for committer commit timestamp, and the subject is the first line of the commit message, as per the git log format (%).
To get the most recent entry from a table, we order by dates in descending order, then limit the result set to a single entry, and we get a single line of output:
─[ RECORD 1 ]────────────────────────────────────────────────────────────────
project │ postgresql
hash │ b1c2d76a2fcef812af0be3343082414d401909c8
author │ Tom Lane
ats │ 2017-08-19 19:39:37+02
committer │ Tom Lane
cts │ 2017-08-19 19:39:51+02
subject │ Fix possible core dump in parallel restore when using a TOC list.
Time-based reporting
With timestamps, we can compute time-based reporting, such as how many commits each project received each year in its whole history:
select extract(year from ats) as year,count(*) filter(where project = 'postgres') as postgresql,count(*) filter(where project = 'pgloader') as pgloaderfrom commitloggroup by yearorder by year;
As we have only loaded two projects in our commitlog table, the output is better with a pivot query. We can see more than 20 years of sustained ...