PostgreSQL – DATE Data Type
The date data type is used to store dates without any associated time information. It represents a specific day in the calendar, including the year, month, and day. Here are some key points about the date data type:
- Format: Dates are stored in the format ‘YYYY-MM-DD’, where ‘YYYY’ represents the year, ‘MM’ represents the month (with leading zero if necessary), and ‘DD’ represents the day of the month (also with leading zero if necessary).
- Range: The
datedata type in PostgreSQL can represent dates ranging from January 1, 4713 BC (negative infinity) to December 31, 5874897 AD (positive infinity). - Date Functions: PostgreSQL provides a variety of functions for manipulating and working with
datevalues, such as calculating differences between dates, extracting components (year, month, day), and formatting dates for display.
Here’s an example of using the date data type:
CREATE TABLE tasks(task_id serial PRIMARY KEY,task_name varchar(100),due_date date );
INSERT INTO tasks (task_name, due_date)VALUES ('Finish Report', '2023-08-31');
SELECT * FROM tasksWHERE due_date <= '2023-08-31';
In this example, a due_date column of type date is used to store due dates for tasks. The date data type is appropriate for scenarios where you need to store and work with specific calendar dates without any associated time information.