PostgreSQL Type Casting

PostgreSQL allows casting of types in a variety of ways. Let’s explore some examples that are useful from time to time.

Timestamp to date¶

A blog posts table contains a created_at column which records the date and time when a post was originally saved. But maybe we either need or want to only display the date disregarding the time part of the timestamp.

created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL

Then, we might run a query like this to retrieve the created_at and cast it to DATE (YYYY-MM-DD) ignoring the time part of the timestamp:

SELECT
    C.name AS category
  , P.title
  , P.created_at::date
FROM categories AS C
  INNER JOIN posts AS P
  	ON C.category = P.category;