PostgreSQL – How to Extract Date from Timestamp
You can extract date from a timestamp using various functions depending on the specific part of the date you want to extract. Here are some commonly used functions for extracting different parts of a date from a timestamp:
Extracting Date Only (Year, Month, and Day):To extract only the date (year, month, and day) from a timestamp, you can use the DATE type cast:
SELECT CAST(your_timestamp AS DATE); SELECT DATE(your_timestamp); Replace your_timestamp with your actual timestamp value.
Extracting Year, Month, or Day Separately:You can use the EXTRACT function to extract specific parts of the date, such as year, month, or day. Here are some examples:
Extract Year:
SELECT EXTRACT(YEAR FROM your_timestamp);Extract Month:
SELECT EXTRACT(MONTH FROM your_timestamp);Extract Day:
SELECT EXTRACT(DAY FROM your_timestamp);Extracting Other Date Components (Hour, Minute, Second, etc.):You can also extract other date components like hours, minutes, seconds, etc., using the EXTRACT function with appropriate keywords. For example:
Extract Hour:
SELECT EXTRACT(HOUR FROM your_timestamp);Extract Minute:
SELECT EXTRACT(MINUTE FROM your_timestamp);Extract Second:
SELECT EXTRACT(SECOND FROM your_timestamp);These functions allow you to extract specific parts of a date or time from a timestamp as needed in your SQL queries.