Sort by timestamps (month and year) in PostgreSQL

Let's understand how we can sort by a non-timezone bound timestamp value, here we will call it timestamp_x in table dev_to;

select to_char("timestamp_x", 'Month YYYY') as "month", to_char("timestamp_x", 'YYYYMM') as "monthN" from dev_to
order by to_char("timestamp_x", 'YYYYMM');

Let's break the query is smaller chunks and understand whats happening:

• to_char("timestamp_x", 'Month YYYY') will convert timestamp_x into its Month and Year form, for example: January 2021.
• to_char("timestamp_x", 'YYYYMM') will again convert timestamp_x into its Month and Year form, but this time it will be numeric, for example: 202101.
• order by to_char("timestamp_x", 'YYYYMM') will order the data by year and month (in that order as well), for example:

January 2021, August 2019, March 2020

will be sorted as:

August 2019, March 2020, January 2021

Hence, the final output will be sorted by both month and year as desired.