Sql: Subtracting 1 day from a timestamp date

Timestamp manipulation in various scenarios

CASE 1: Wave goodbye to the month

To get the last day of the current month, employ the date_trunc function:

-- When the last day of the month decides to play hide & seek!
SELECT (date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month;


CASE 2: Running an interval race

Create dynamic time intervals using the make_interval function:

-- Time travel made easy
SELECT CURRENT_TIMESTAMP - make_interval(days => 1);


CASE 3: Time-traveling to the past

Filter records from past days using a time window. For example, go back 130 days:

-- Bringing back the good old (130) days!
SELECT * FROM your_table WHERE your_timestamp_column >= now() - INTERVAL '130 days';


Tips & Tricks to enhance your SQL game

Embrace the Dates

If you desire the date part only, devoid of the complexities of time:

-- Time makes dates complicated, let's stick to just dates!
SELECT (your_timestamp_column - INTERVAL '1 day')::DATE FROM your_table;


Grouping for temporal trend insights

Group and aggregate data for analyzing trends over time with COUNT:

-- Time tourists counting their travels
SELECT 
  (your_timestamp_column::DATE - INTERVAL '1 day') AS date_only,
  COUNT(*) 
FROM your_table 
GROUP BY date_only;


Yesterday, all my timestamps seemed so far away...

In PostgreSQL, quickly get the date of 'yesterday' or 'tomorrow' with:

-- Getting a glimpse of past and future in a single sight
SELECT 'yesterday'::TIMESTAMP, 'tomorrow'::TIMESTAMP;


Syntax and data types make SQL world go 'round

Ensure proper syntax and data types for error-free execution:

-- Subtracting a day feels like stealing a cookie from your future self!
SELECT now()::DATE - 1;


The above line will present you with the date of yesterday.