JIYIK CN >

Before we begin, let's define what a timestamp is in SQL.

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

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

The storage size of a timestamp is 8 bytes. It can be displayed with or without a time zone.

The minimum time zone value is 4713 BC and the maximum is 294276 AD.

So now, let's learn how to subtract one day from a timestamp.

INTERVALSubtract one day from a timestamp date using keyword in PostgreSQL

To check 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 can't do the following to subtract one day from that 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 problem, we can use INTERVALthe keyword. What is 间隔?

INTERVALis the number of days of the timestamp, in other words, its age. It does not return a date; rather, the difference in days between two specific dates.

grammar:

age ( timestamp ) ? interval

example:

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

grammar:

timestamp - timestamp ? interval

example:

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

The syntax provided above is taken from the PostgreSQL documentation.

Use INTERVALthe value to increase or decrease the number of days from the provided timestamp. Hence, we can use the query given below:

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

Doing this will subtract one day from our timestamp.

You can also use the following.

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

Since 24 hours is the same as 1 day, you can use any of the syntax given above.

Performing date conversion in PostgreSQL to subtract one day from a timestamp date

grammar:

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

This will subtract one day from the date. Because timestamps do not allow subtraction, we can convert it to a date and do the subtraction as needed.

+The and -operator is used in PostgreSQL instead of the standard DATEADDand DATEDIFF.

Subtract one day from a timestamp date in PostgreSQL using time and date

in PostgreSQL DATEtakes less storage space but should not be used in the case of calendar dates. It even takes leap years into account when doing date calculations.

In DATESthis case, you can use the following code.

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

INTEGERCreate a new date using the keyword

Another notable keyword used in PostgreSQL is INTEGERthe keyword.

INTEGERListed in the PostgreSQL documentation under the heading Date/Time Functions and Operators. Here is a table defining things in more detail:

To use +the keyword, proceed as follows:

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

To use it in case of timestamp, do CASTING and add in to it INTEGERto produce the desired date.

You cannot perform ADD, MULTIPLY, , DIVISIONor any other operator with timestamps.

Remember, DATE/INTEGERSthis is calculated in days, not months, years, or other units of time.

DATEDIFFMany people prefer to implement the and functions in PostgreSQL extensions DATEADD. If all else fails, you can even use them here.

Because PostgreSQL 7 does not allow the standard SQL DIFFand ADDfunctions, we must use the provided keywords and clauses.