DEV Community

loading...
Cover image for Sort by timestamps (month and year) in PostgreSQL

Sort by timestamps (month and year) in PostgreSQL

sambhav2612 profile image Sambhav Jain ・1 min read

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');
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

will be sorted as:

August 2019, March 2020, January 2021
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

Forem Open with the Forem app