How to Subtract a Day From a Timestamp Date in PostgreSQL

1. Use the INTERVAL Keyword to Subtract a Day From a Timestamp Date in PostgreSQL
2. Perform Date Cast to Subtract a Day From a Timestamp Date in PostgreSQL
3. Work With Time and Dates to Subtract a Day From a Timestamp Date in PostgreSQL

Before we start, let us define what timestamp is in SQL.

In the PostgreSQL documentation under the DATE/TIME heading, the timestamp is a data type that stores both date and time in the below format.

YYYY-MM-DD hh:mm:ss  (DATE | TIME)

The storage size of the timestamp is 8 bytes. It can be presented with or without the timezone.

The lowest value of a timezone is 4713 BC, and the highest value is 294276 AD.

So now, let us learn how to subtract a day from the timestamp.

Use the INTERVAL Keyword to Subtract a Day From a Timestamp Date in PostgreSQL

To view the date from a timestamp, you can do the following.

select timestamp '2021-01-01 08:08:01'

It will display the timestamp extracted from the string.

You cannot do the following to deduct a day from this timestamp. It will throw an error.

select timestamp '2021-01-01 08:08:01' - 1

Output:

ERROR:  operator does not exist: timestamp without time zone - integer
LINE 1: select timestamp '2021-01-01 08:08:01' - 1

To solve this, we can use the INTERVAL keyword. What is INTERVAL?

The INTERVAL is the count of the days from a timestamp or, in other words, its age. It won’t return the date; instead, the difference in the days between two specific dates.

Subtract Argument From Current Date (At Midnight)

Syntax:

age ( timestamp ) ? interval

Example:

age(timestamp '1957-06-13') ? 62 years 6 mons 10 days

Subtract Timestamps (Convert 24-Hour Intervals Into Days, Similar to justify_hours())

Syntax:

timestamp - timestamp ? interval

Example:

timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' ? 63 days 15:00:00

The syntaxes provided above were taken from the PostgreSQL documentation.

Using an INTERVAL value increments or decrements the day count from the timestamp provided. Hence, we can use the query given below:

select timestamp '2021-01-01 08:08:01' - INTERVAL '1 DAY'

Doing this will subtract a day from our timestamp.

You can also use the following.

select timestamp '2021-01-01 08:08:01' - INTERVAL '24 HOURS'

As 24 hours is the same as 1 day, you can use any syntaxes given above.

Perform Date Cast to Subtract a Day From a Timestamp Date in PostgreSQL

Syntax:

select timestamp '2021-01-01 08:08:01'::DATE - 1

This will deduct a day from the date. Because timestamp does not allow subtraction, we can cast this to a date and subtract as we desire.

The + and the - operators are used in PostgreSQL instead of the standard DATEADD and DATEDIFF.

Work With Time and Dates to Subtract a Day From a Timestamp Date in PostgreSQL

The DATE in PostgreSQL takes less storage space but should not be used in the case of calendar dates. It even considers leap years when making date calculations.

In the case of DATES, you can use the following code.

select DATE '2021-01-01' - INTERVAL '1 DAY'

Use the INTEGER Keyword to Make a New Date

Another notable keyword to use in PostgreSQL is the INTEGER keyword.

INTEGER is listed under the headings of DATE/TIME FUNCTIONS AND OPERATORS in the PostgreSQL documentation. Here is a table to define things in more detail:

To use the + keyword, go ahead and do something like this:

select DATE '2021-01-01' + INTEGER '7'

And to use it in the case of timestamp, do the CASTING and add INTEGER to it to produce the date needed.

You can not perform ADD, MULTIPLY, DIVISION, or any other operators with timestamps.

Remember that DATE/INTEGERS are calculated as the number of days but not in months, years, or other units of time.

Many people would tend to implement the DATEDIFF and DATEADD functions to PostgreSQL in its extensions. Here, you may even be able to use them if all else fails.

Because PostgreSQL7 does not allow standard SQL DIFF and ADD functions, we must use the keywords and clauses provided.