How to Subtract a Day From a Timestamp in PostgreSQL

To subtract one day from a PostgreSQL timestamp and keep its time of day, subtract an INTERVAL value: SELECT TIMESTAMP '2021-01-01 08:08:01' - INTERVAL '1 DAY';. This produces 2020-12-31 08:08:01. Use a cast to DATE first if you want to discard the time and work with the date only. These are PostgreSQL SQL examples; running them requires a PostgreSQL server, so they are not presented here as runtime-tested output.

Subtract a Day From a PostgreSQL Timestamp With an Interval

Use INTERVAL '1 DAY' when the result should remain a timestamp. For example, SELECT TIMESTAMP '2021-01-01 08:08:01' - INTERVAL '1 DAY'; subtracts one day from the supplied timestamp. The date changes to the preceding calendar date, while the time component remains 08:08:01 in this example.

An interval represents a span of time that PostgreSQL can add to or subtract from date and timestamp values. In this query, the interval is one day. The expression is tied to the input timestamp, so it does not depend on the current date or time. Replace the timestamp literal with a timestamp column or parameter to use the same operation on stored data; for example, created_at - INTERVAL '1 DAY' computes a timestamp one day earlier for each row.

Do not subtract an integer directly from a timestamp. An expression such as TIMESTAMP '2021-01-01 08:08:01' - 1 has no matching timestamp-minus-integer operation. Use an interval when the result must retain a time of day. If you only need the date, cast the timestamp to DATE before subtracting an integer, as shown in the next section.

Subtract a Day From the Date Portion of a Timestamp

Casting to DATE removes the time of day before subtraction. For example, SELECT TIMESTAMP '2021-01-01 08:08:01'::DATE - 1; returns 2020-12-31. The result is a date, not a timestamp, so the original 08:08:01 is not retained.

This is useful when a timestamp column records an event time but the calculation is meant to produce a date label or reporting day. Apply the cast only when dropping the time is intentional. If downstream code needs the original clock time, subtract an interval from the timestamp instead. The cast and subtraction can also be applied to a column, such as event_time::DATE - 1.

Subtract a Day From a PostgreSQL Date

For a value that is already a PostgreSQL DATE, subtract an integer day count: SELECT DATE '2021-01-01' - 1; returns 2020-12-31. The integer here is a number of days, so - 7 moves the date back seven days. The result remains a DATE.

Choose the operation based on the type and result you need: use timestamp - INTERVAL '1 DAY' to keep timestamp data, timestamp::DATE - 1 to get a date from a timestamp, or date - 1 when you already have a date. PostgreSQL date arithmetic accounts for the calendar when computing the resulting date; you do not need to calculate month lengths yourself. The examples use fixed literals to make the input and result clear. In a query against a table, substitute the relevant column and keep the interval or integer operation appropriate to its type.