How to Extract Date From a TimeStamp in PostgreSQL

PostgreSQL is a powerful open-source relational database management system (RDBMS). PostgreSQL is well-known for its feature-rich capabilities, standardization, and adaptability. It supports a variety of data types, complex SQL queries, and ACID properties. PostgreSQL offers scalability and durability, making it a popular option for both small projects and massive enterprise databases. It may be used for applications of all sizes and complexity.

What is Timestamp

The timestamp data type in PostgreSQL is used to hold date and time values. In PostgreSQL, we may work with timestamps using several related data types and methods. A timestamp is a sort of data that is used to indicate a specific point in time in databases, programming languages, and other systems. Usually, it contains details regarding the time and date. A timestamp is used to store date and time information in databases such as PostgreSQL, MySQL, and Oracle with extreme precision.

Representation: The format used to indicate a timestamp is `YYYY-MM-DD HH:MI: SS`, where `HH:MI: SS`stands for the hours, minutes, and seconds, and `MM` for the month, `DD` for the day, and “ for the year.

Example:

2022-01-27 15:30:45

Methods for Extracting Date from Timestamp in PostgreSQL

To understand How to Extract date from a timestamp in PostgreSQL we need a table on which we will perform various operations. So here we will create a my_table. Let’s see below.

Query:

CREATE TABLE my_table  
(     id serial PRIMARY KEY,
     emp_name VARCHAR,
     joining_time TIMESTAMP
);

Let’s insert some data into the my_table for better understanding.

Query:

INSERT INTO my_table (emp_name,joining_time) VALUES (‘Sanket’,’2024-01-27 12:00:00′), (‘Aniket’,’2024-01-28 14:30:00′), (‘Samuel’,’2024-01-29 09:45:00′);

Method to Extract Date From a Timestamp

Method 1 : Using the DATE() Function

The DATE function in PostgreSQL is used to extract the date component from a timestamp or a timestamp with a time zone. Additionally, a date can be created using distinct values for the year, month, and day.

Syntax:

SELECT DATE(timestamp_expression);

Example: Extracting Date from TIMESTAMP using DATE() Function

Query:

SELECT emp_name,DATE(joining_time) from my_table;

Output:

Output for DATE() Function

Explanation: This query retrieves employee names and their joining dates from the table my_table. It utilizes the DATE() function to extract the date part from the joining_time timestamp column

Method 2 : Using the CAST Operator

we can convert a value from one data type to another in PostgreSQL by using the CAST operator for explicit type conversion. It’s frequently used when we need to do operations involving multiple data types or when we want to make sure that a specific data type is used for a given expression.

Syntax:

CAST (expression AS data_type)

Explanation:

• expression: The expression that has to be converted.
• data_type: The data type to which we wish to convert the expression.

Example: Extracting Date from TIMESTAMP using CAST Operator:

SELECT emp_name,CAST(joining_time AS DATE) from my_table;

Output:

Output for CAST Operator

Explanation: Thiis query retrieves employee names along with the joining dates from the table my_table. It utilizes the CAST() operator to explicitly convert the joining_time timestamp column to the DATE data type for date extraction.

Method 3: Using the Scope Resolution(::) Operator

Explicit type casting in PostgreSQL is frequently accomplished using the “::” syntax. It enables us to cast an expression or value to a certain kind of data.

Syntax:

expression::data_type

Explanation:

• expression: The intended value or statement to be cast.
• data_type: The type of data to which the expression should be cast.

Example: Extracting Date from TIMESTAMP using Scope Resolution Operator:

SELECT emp_name,joining_time::DATE from my_table;

Output:

Output for Scope Resolution Operator

Explanation: This query extracts employee names along with the corresponding joining dates from the my_table table. It uses the scope resolution operator :: to cast the joining_time timestamp column to the DATE data type for date extraction.

Conclusion

In conclusion, you can use the CAST operator or the DATE function in PostgreSQL to extract the date from a timestamp. While the CAST operator enables explicit type casting of the timestamp to a date, the DATE function is very helpful for extracting the date portion directly. In order to retrieve a date from a timestamp, the Scope Resolution operator `::` additionally uses explicit casting.