Understanding DATE Data Type in PostgreSQL

The DATE data type takes 4 bytes of storage to store the date value in Postgres. The upper limit of the date value is 5874897 AD and the lower limit is 4713 BC, the DATE data type stores the value in between this range. The standard format of the DATE type is “yyyy-mm-dd”.

The basic syntax to declare a variable of the DATE data type is given as

Var_name DATE;

We will have to specify the variable’s or column’s name before the DATE keyword, to declare it of DATE data type.

We will implement the DATE data type on different use cases in the following examples.

Example 1: Creating a Column of DATE Data Type in Postgres Table

We can create a table having a column of DATE data type. This column will store DATE values in it. Let’s create a table named “quiz” by executing the following query:

CREATE TABLE Quiz(
quiz_id serial PRIMARY KEY,
topic_name VARCHAR (255) NOT NULL,
submission_date DATE NOT NULL);

The query will successfully create the “quiz” table with “quiz_id”, “topic_name”, and “submission_date” columns having SERIAL, VARCHAR, and DATE types, respectively.

Now, we will be inserting some values into the table for the “topic_name” column and “submission_date” column having the DATE data type. The following query can be used in this regard:

INSERT INTO quiz(topic_name, submission_date)
VALUES('Introduction to Algorithms', '2022-02-11'),
  ('Basics of PostgreSQL', '2023-04-21'),
  ('PostgreSQL JSON Functions', '2023-07-28');

This query will insert the provided data into the “quiz” table. To see the table and its data, we will execute the SELECT statement, which will retrieve the whole “quiz” table which is given as

In the above table, we can notice that we have created the “submission_date” column having DATE data type. So we have stored the date values into the table. The data type of the selected column can be seen as “date”. This way, users can create a table column with the DATE type.

Example 2: Adding a DATE Type Column to an Existing Table

An additional column can also be added to an existing table, in Postgres, by using the ALTER TABLE statement. Let’s add a column with DATE data type into an existing table named “messages”, which looks like the following:

Now if we want to add a column with DATE data type, we will write the following query.

ALTER TABLE messages
ADD COLUMN message_date DATE;

This query will alter the table for the addition of a new column named “message_date” having DATE data type.

To see if the column “messages_date” with the Date data type has been added or not, we will execute the SELECT statement like this:

SELECT * FROM messages;

We can see that the “message_date” column has been added which has the date data type. The values of the column are null because we have not yet inserted any values into it.

This is how we can add a new DATE column to an existing Postgres table.

Example 3: Getting the Current DATE Using NOW() Function

We can get the current date using the now() function. The now() function retrieves the current/today’s date and time with time zone information. If we want to get only the date from the current date and time, returned by the now() function, we will have to typecast it into the DATE data type. This will give us the current date in the DATE data type. The query for this purpose can be written as

SELECT now():: DATE;

The above query will return the following output:

The returned type of the now() function is “timestamp with time zone”, after typecasting it into the DATE data type, we got the current date in the DATE data type.

Example 4: Getting the Current DATE Using CURRENT_DATE

We can also get the current date using the CURRENT_DATE. The following query can be written/executed for this purpose:

SELECT CURRENT_DATE;

The CURRENT_DATE retrieves the date at the current moment, as depicted in the below screenshot:

This is another simple way to get the current date.

Example 5: Getting the DATE in Specific Format

As stated earlier, the default format of the Date data type to store the date value is “yyyy-mm-dd”. We can also get the date in the custom format by executing the TO_CHAR() function. The TO_CHAR() function converts the timestamp, integer, any numeric value, interval, etc. into the specified format. We will write the query as follows:

SELECT TO_CHAR('2023-08-22'::DATE, 'dd/mm/yyyy');

The TO_CHAR() function returns the value as string/TEXT. The output for the stated function will be:

We can see that the query has returned the provided date after transforming it into the specified format. In a similar way, we can get the current date in any custom format like this:

SELECT TO_CHAR(now():: DATE, 'Mon dd, yyyy') AS "current date";

The query will return the current date in the specified format i.e. Mon dd, yyyy :

This is how we can get any date in a customized format.

Example 6: Getting the Interval Between Two Dates

We can get the interval between the two dates by making use of the “-” operator in PostgreSQL. Let’s get the interval between the “submission_date” and the current timestamp by executing the following query:

SELECT *, now()-submission_date AS "interval difference"
FROM quiz;

We have used the now() function to get the current timestamp and used the “-” operator between the now() function and the “submission_date” to get the interval between both dates. The query retrieves the following output:

In the above-described way, we can get the interval between two dates.

Example 7: Extracting the Year, Month, Week, and Day From DATE Value

The user can get the year, month, week, and day from any provided DATE value, by using the EXTRACT() function. The EXTRACT() function is used with the field to be extracted and the FROM keyword to get the required value from the specified date value. For example, in the following query, we have extracted the year, month, and day from the “submission_date” respectively.

SELECT topic_name,
EXTRACT (YEAR FROM submission_date) AS "submission year",
EXTRACT (MONTH FROM submission_date) AS "submission month",
EXTRACT (DAY FROM submission_date) AS "submission day"
FROM quiz;

On execution of the above query, the following output is returned:

This is how we can get the year, month, week, and day from the particular date value using the EXTRACT() function.

Example 8: Getting INTERVAL Between Given Dates Using AGE() Function

The AGE() function retrieves the interval between any two date values/entries. The AGE() function can take one or two date values. In the case of one date value, the function returns the interval between the specified date and the current date. But if we provide two date values to the AGE() function, we will get the interval between the two date values. Let’s execute the following query to understand the working of the AGE() function:

SELECT topic_name,submission_date,
AGE(submission_date)
FROM quiz;

In the above query, we have provided the “submission_date” column to the AGE() function. The query will return the interval between the “submission_date” and the current date like this:

In the above output, we got the interval between the current date and the “submission_date” in the INTERVAL data type. So the AGE() function can be used for this specific purpose.

Conclusion

To create a DATE type column, specify the column’s name followed by the DATE keyword. The default format in which the DATE data type stores values is “yyyy-mm-dd”. It consumes four bytes of storage. There are several functions and operations that we can operate on the DATE data type. These functions can be used to get the current date, get the date in a specific format, or get the interval between the date values, etc., these functions are elaborated extensively in this article with examples and proper explanation.

Saryia

TUTORIALS ON LINUX, PROGRAMMING & TECHNOLOGY

[email protected]