PostgreSQL: Date Filtering and Transformations

Let’s use the following Projects table as an example:

Column Name	Data Type
id	integer
title	character varying(255)
start_date	date
end_date	date

 id |          title           | start_date |  end_date  
----+--------------------------+------------+------------
  1 | Build a cool site        | 2011-10-28 | 2012-01-26
  2 | Update TPS Reports       | 2011-07-20 | 2011-10-28
  3 | Design 3 New Silly Walks | 2009-05-11 | 2009-08-19

• Filter by a specific date

SELECT
  *
FROM
  start_date = '2011-10-28';

• Filter by a range

Please notice that the between is inclusive

SELECT
  *
FROM
  start_date BETWEEN '2011-10-28' AND '2011-10-30';

For non-inclusive results, you can use

SELECT
  *
FROM
  start_date > '2011-10-28' 
  AND start_date < '2011-10-30'; 

-- This will return records with start_date = 2011-10-29 

• Filter by HOUR, DAY, MONTH, YEAR

SELECT  
  *
FROM  
  projects
WHERE
  EXTRACT( DAY FROM start_date ) = 20
  AND EXTRACT( MONTH FROM start_date ) = 07
  AND EXTRACT( YEAR FROM start_date ) = 2011;

You can use ::DATE::TIMESTAMP to cast a date into timestamp

SELECT  
  *,
  current_date::DATE::TIMESTAMP
FROM  
  projects
WHERE
  EXTRACT( HOUR FROM current_date::DATE::TIMESTAMP) = 0;

• Filter by Relative Dates

SELECT  
  *
FROM  
  projects
WHERE
  start_date >= current_date - INTERVAL '7 days';

• Filter by Timestamps

To filter timestamps values using a date value

SELECT 
  * 
FROM 
  projects 
WHERE 
  start_date::date = '2023-09-01';

-- This converts the start_date(timestamp to date)

Transformations

• String to Date

Function: TO_DATE(string, format)
Output: date

SELECT  
  TO_DATE(new_date, 'YYYYMMDD') AS start_date
FROM  
  projects;

• String to Timestamp

Function: TO_TIMESTAMP(string, format)
Output: timestamp

SELECT 
  TO_TIMESTAMP(new_date, 'YYYY-MM-DD HH24:MI:SS')
FROM
  projects;

• Date to Timestamp

SELECT  
  start_date::TIMESTAMP
FROM  
  projects;

• Timestamp to Date

SELECT  
  start_date::DATE
FROM  
  projects;

• Date to String

Function: TO_CHAR(date, format) – The format that you want
Output: String

SELECT  
  TO_CHAR(start_date, 'YYYYMMDD')
FROM  
  projects;

• Timestamp to String

Function: TO_CHAR(date, format) – The format that you want
Output: String

SELECT  
  TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS')
FROM  
  projects;