This article covers important date manipulation functions in PostgreSQL. It explains how to work with current date and timestamp, date arithmetic using intervals, getting the beginning date or time using date_trunc, extracting information using to_char function, and dealing with Unix timestamps.
Key Concepts Explanation
Getting Current Date and Timestamp
The current_date function returns today’s date, and current_timestamp returns the current timestamp. These functions do not require () and have specific formats:
- Date format:
yyyy-MM-dd - Timestamp format:
yyyy-MM-dd HH:mm:ss.SSS
SELECT current_date AS current_date;
SELECT current_timestamp AS current_timestamp;
Date Arithmetic
Perform arithmetic operations using intervals by adding or subtracting days, months, or years from the current date.
SELECT current_date + INTERVAL '32 DAYS' AS result;
SELECT current_date - INTERVAL '730 DAYS' AS result;
Beginning Date or Time - date_trunc
Use date_trunc to get the beginning dates or times based on specified units like year, month, week, day, or hour.
SELECT date_trunc('MONTH', current_date) AS month_beginning;
SELECT date_trunc('HOUR', current_timestamp) AS hour_beginning;
Extracting Information using to_char
Retrieve specific elements such as year, month, day, day of the week from dates or timestamps using to_char.
SELECT current_timestamp, to_char(current_timestamp, 'yyyy') AS year;
SELECT current_timestamp, to_char(current_timestamp, 'DD') AS day_of_month;
Extracting Information using extract
Use extract to retrieve various date or timestamp components like century, decade, year, month, etc.
SELECT extract(month FROM current_date) AS month;
SELECT extract(hour FROM current_timestamp) AS hour;
Dealing with Unix Timestamp
Convert dates or timestamps to Unix epoch format or Unix timestamps.
SELECT extract(epoch FROM current_date) AS date_epoch;
SELECT extract(epoch FROM '2019-04-30 18:18:51'::timestamp) AS unixtime;
Hands-On Tasks
- Use
date_truncto get the beginning date of the week. - Convert a timestamp to Unix epoch format and back to a timestamp.
Conclusion
In this guide, we explored essential date manipulation functions in PostgreSQL. Practice these examples and engage with the community for further learning opportunities.
Video Tutorial
Watch the video tutorial on YouTube
Remember to follow along with the tasks mentioned in the article for practical experience. Join the community to interact and share your learnings with others.