PostgreSQL DATEDIFF function
Unlike SQL Server, PostgreSQL does not have a built-in DATEDIFFfunction to calculate the difference between dates. However, you can use expressions that include other functions provided by PostgreSQL to obtain the difference between dates.
This article discusses methods for calculating the difference in days between dates in PostgreSQL.
Use the function in PostgreSQL EXTRACTto get the date difference
grammar:
EXTRACT(field, source)
fieldCan be year, month, day, etc. sourceType can be timestamp, time, or interval.
If the table column is of date type, you can convert its type to timestamp.
You can use EXTRACTthe function to calculate the difference in days, as shown in the following example.
SELECT EXTRACT(DAY FROM '2022-01-15 11:40:00'::timestamp - '2021-11-03 13:10:00'::timestamp)
Output:
extract
---------
72
Note that PostgreSQL will return the number of complete days between the two dates, taking into account the difference in hours and minutes. If there is no difference in hours, minutes, and seconds, a different result is returned, as shown in the following example.
SELECT EXTRACT(DAY FROM '2022-01-15 00:00:00'::timestamp - '2021-11-03 00:00:00'::timestamp)
Output:
extract
---------
73
The dates used in the examples above are the same as when converting a date type field to a timestamp.
DATE_PARTGet the difference of dates in PostgreSQL
using the function
grammar:
DATE_PART('field', source)
fieldCan be year, month, day, etc. It must be a string type.
You can also use DATE_PARTthe function to calculate the difference in days, as shown in the following command.
SELECT DATE_PART('day', '2022-01-15 11:40:00'::timestamp - '2021-11-03 13:10:00'::timestamp);
Output:
date_part
-----------
72
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
Terminate the PostgreSQL connection
Publish Date：2025/04/11 Views：199 Category：PostgreSQL
-
In this article, we will learn how to terminate a PostgreSQL session. Any open connections are run by background processes or tasks, PSQL which may no longer exist despite exiting the user interface or command line tool. Use ps -ef or grep
Single query to rename and change column type in PostgreSQL
Publish Date：2025/04/11 Views：166 Category：PostgreSQL
-
This article describes how to rename a column and change its type in PostgreSQL using only a single query. Renaming and changing column types in MySQL In MySQL , if you want to change the column type and rename it, you can use a simple stat
Joining columns using Select in PostgreSQL
Publish Date：2025/04/11 Views：176 Category：PostgreSQL
-
MySQL PostgreSQL is an object-relational database system, which means it can support more complex data types than its competitors . Today we will learn how to use SELECT the operator to join the columns of a table. Using operators to || joi
Using CASE in PostgreSQL
Publish Date：2025/04/11 Views：124 Category：PostgreSQL
-
This article shows how to use the statement in PostgreSQL CASE . CASE How to use the statement in PostgreSQL case Statements are similar to those in general-purpose programming languages if-else . But in SQL, if you want to write IF-ELSE ,
Using NOT IN with subqueries in PostgreSQL
Publish Date：2025/04/11 Views：93 Category：PostgreSQL
-
NOT IN The inverts the result of NOT simply using IN the operator. NOT IN The right side of the operator must have a subquery in which multiple columns are returned to check whether the expression matches the data. NOT IN Tends to return tr
Using variables in PostgreSQL
Publish Date：2025/04/11 Views：171 Category：PostgreSQL
-
This article will demonstrate how we can declare and assign values to variables in PostgreSQL. In PostgreSQL, DECLARE variables are declared using Often you will need variables in your PL/SQL scripts. In DECLARE the section called , y
Connect to PostgreSQL using a password
Publish Date：2025/04/11 Views：171 Category：PostgreSQL
-
This article shows various ways to connect to PostgreSQL using a password. It can be through the command line, pgpass a file, PGPASSWORD an environment variable or a connection string. Connecting to PostgreSQL with a password using the comm
Deleting a database in PostgreSQL via PSQL
Publish Date：2025/04/11 Views：166 Category：PostgreSQL
-
There are two ways to access PostgreSQL objects and databases on your system. One is through an interface, such as a graphical interface like PGADMIN, and the other is the basic command line tool psql. Today, we will look at DROP DATABASE t
Using the database in PostgreSQL
Publish Date：2025/04/11 Views：132 Category：PostgreSQL
-
This article demonstrates connecting to a database, creating a new database, and creating a table in PostgreSQL. Available databases in PostgreSQL You can run the following command after opening the Postgres command line to view all availab
