PostgreSQL Date/Time Types (With Examples)

PostgreSQL is one of the best databases that provide a wide number of functionalities to deal with date/time values. It provides more features for the date/time type than the SQL standard requires. In this tutorial, we will check all the aspects of the date/time type in PostgreSQL with examples. So, let’s get started!

Also Read: PostgreSQL Integer Types: SMALLINT, INT, BIGINT

Introduction to Date/Time in PostgreSQL

PostgreSQL supports a complete set of date and time types that includes multiple possibilities of input, time zone and special values. The dates calculated in PostgreSQL are as per the Gregorian calendar and it supports dates of years when the calendar was not even introduced.

If you are interested in the logic and history behind the calculation of the dates in PostgreSQL, you can refer to this article

PostgreSQL provides a combined type for date-time values, as well as an individual type for the time values. Look at the tables below for the date/time types, their description and some more information about them.

Here, p is the precision value that specifies how many fractional digits should be there in the seconds’ field. By default, it is none, but you can have a 0 to 6 range of precision.

The interval type accepts an additional parameter for restricting the set of stored fields by using one of the following phrases:

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEAR TO MONTH, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, MINUTE TO SECOND.

The interval type is so vast that we can have an entire tutorial on it. Therefore, we will discuss it in some another tutorial.

Now glance through the table below for the lowest and highest possible values of each type.

Now that you have seen the date/time types in PostgreSQL, you would be interested in what format of date/time values does PostgreSQL accept, right?

Postgresql has given us a wide number of options to specify the date and time values.

• PostgreSQL possible Date input formats and examples
• PostgreSQL possible Time input formats and examples
• PostgreSQL possible Time Zone input formats and examples

You can check that out. Now let’s see some examples of date-time formats.

Note that, we will not cover date/time functions in this tutorial, we will be focusing only on the functionality of the standard date/time type.

PostgreSQL Date/Time Type Examples

Let’s see some date/time examples in the psql shell without creating a table. Here, we will just try different formats of dates in the date() function to check if the inputs we are passing are valid formats or not.

SELECT DATE('2 January 2022');
SELECT DATE('2-1-2022');
SELECT DATE('2022 January 2');
SELECT DATE('2022/1/2');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s now create a table and test each date/time type with different values.

CREATE TABLE registrations(
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dateRegistered DATE NOT NULL,
timeRegistered TIME NOT NULL,
entryTime TIMESTAMP(5) DEFAULT CURRENT_TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created three different types of columns: Date, Time and timestamp. Let’s now insert some values into the table to create records.

INSERT INTO registrations (name, dateRegistered, timeRegistered)
VALUES
  ('John Doe', '2022-01-01', '09:30:00'),
  ('Jane Smith', '2022-02-15', '13:45:00');

INSERT INTO registrations (name, dateRegistered, timeRegistered)
VALUES
  ('Bob Johnson', '2022-02-20', '17:15:00'),
  ('Alice Brown', '2022-03-05', '10:00:00');

INSERT INTO registrations (name, dateRegistered, timeRegistered)
VALUES
  ('Sarah Lee', '2022-03-20', '19:30:00');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created a table of three different types of columns. Each one of those contains the date, time and whole date-time values respectively. The timestamp type contains precision up to 5 digits as specified and it will contain the default value which will be the record creation date-time value.

Here we have fired the insert query at different times, therefore the entry time will be different in the table.

Now let’s see the output of the table.

SELECT * FROM registrations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the table consists of the proper date, time and date-time values. As you can see, there is precision present in the ‘entrytime’ (timestamp) column as well.

Now let’s try to fetch the records from the table using the where condition by different formats of the input date and time.

SELECT * FROM registrations
WHERE dateRegistered BETWEEN '1 january 2022' AND '2022 feb 28' ;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have used two different formats of dates in the same query. Let’s see if it works.

As you can see, PostgreSQL has processed the query and given us the output. That means you can use multiple formats of date and time and the same query as well.

What is the use of Precision?

This is a very interesting question that revolves around new learners’ minds. Here I will tell one scenario where precision is a crucial part of the database.

Let’s say you have an application that is getting thousands of hits per second. However, many of them can be bots and some person with bad intentions is trying to break your server. In this case, you can use precision and limit the request and responses from and to the user and server.

Another use case can be a sport. Often the milliseconds are used to conclude the final result of the races/games.

Conclusion

In this tutorial, we have learned about the date/time types in PostgreSQL. Postgresql has given us so many options in the date/time format that one can get easily get confused. However, do not try to use different formats of date/time, try to stick with the standard format every time you specify a value so that the next time you look at the code, you won’t get confused. We strongly recommend going through the PostgreSQL documentation and reading about the ISO standards which will be beneficial irrespective of the particular database.

Reference

PostgreSQL Official Documentation