PostgreSQL Date/Time Types (With Examples)

Date Time Types In Postgresql

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.

timestamp [(p)] [without time zone]8 BytesDate and time without time zone
timestamp [ (p) ] with time zone8 BytesDate and time with time zone
date4 BytesOnly date
time [ (p) ] [ without time zone ]8 BytesOnly time of day
time [ (p) ] with time zone12 BytesOnly time of day with time zone
interval [ fields ] [ (p) ]16 BytesTime interval

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:


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.

TypeLow ValueHigh ValueResolution
timestamp [(p)] [without time zone]4713 BC294276 AD1 microsecond
timestamp [ (p) ] with time zone4713 BC294276 AD1 microsecond
date4713 BC294276 AD1 day
time [ (p) ] [ without time zone ]00:00:0024:00:001 microsecond
time [ (p) ] with time zone00:00:00+155924:00:00-15591 microsecond
interval [ fields ] [ (p) ]-178000000 years178000000 years1 microsecond

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.

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)
PostgreSQL Date/Time Type Example 1
Example 1

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

CREATE TABLE registrations(
dateRegistered DATE NOT NULL,
timeRegistered TIME NOT NULL,
);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)
  ('John Doe', '2022-01-01', '09:30:00'),
  ('Jane Smith', '2022-02-15', '13:45:00');

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

INSERT INTO registrations (name, dateRegistered, timeRegistered)
  ('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)
Registrations Table Data
Registrations Table Data

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.

PostgreSQL Date/Time Type Example 2
Example 2

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.


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.


PostgreSQL Official Documentation