Updated May 17, 2023
Introduction to PostgreSQL Datetime
In every database, some data types help to store and manipulate values related to date and time or both togetherly. In PostgreSQL, too, we have 6 different data types that are present to store and manipulate dates and time in the database. Many functions help us retrieve and manage them properly and efficiently per use-case requirements and purposes. This article aims to provide an understanding of the datatypes, syntaxes, and examples of commonly used date and time-related functions in PostgreSQL. It will also delve into the concepts of timestamps and time zones. In this topic, we are going to learn about PostgreSQL Datetime.
Datatypes for storing date and time
| Data Type | Size of storage | Description |
| timestamp | 8 bytes | Used to store the date and time togetherly |
| timestamptz | 8 bytes | Used to store date and time along with the timezone consideration |
| interval | 12 bytes | Used to store the time intervalin database |
| date | 4 bytes | Only stores date value in the datatbase |
| time | 8 bytes | It is used to store only time value in datatbase |
| timetz | 12 bytes | Time can be stored along with zone consideration in the database |
Timestamp allows us to store the date as well as the time. In the case of timestamptz data typed values, the time zone is also considered, and the value is stored in the UTC format. Whenever we try to store any value in the timestamptz datatype value, then it is automatically converted into its corresponding UTC value, and then its UTC value is stored in the database table. The PostgreSQL database server retrieves the UTC value from the table when attempting to fetch a specific value. The server subsequently converts this value to the appropriate time zone, typically determined by the time zone setting either in the database server or set by the user, depending on the time zone of the current database connection. The same is the case with the storage of time and timetz.
Functions
There are many functions available for the date and time manipulation.
| Function Name | Description |
| Age(timestamp[,timestamp]) | To get the difference between a particular timestamp and a current timestamp or the difference between two timestamps in the form of years, months, and days. |
| CURRENT_DATE | It is used to get the current date according to the database’s timezone and settings. |
| CURRENT_TIME | It is used to get the current date according to the database’s timezone and settings. |
| DATE_PART(field,source) | It is used to get a particular value from the date, for example, a year or just a month or whichever field value from the source timestamp or interval. |
| EXTRACT(field FROM source) | It is similar to date_part and is used to retrieve the value of a particular field from the given timestamp or interval, whichever source is specified. |
| ISFINITE(date|timestamp|interval) | It is used to test whether the supplied value, that can be either timestamp, interval, or date, is finite or infinite. |
| JUSTIFY(interval) | It is used to retrieve data in years, months, days, and time formats and adjust the interval. |
Examples of PostgreSQL Datetime
To understand working more clearly, let us consider one example. We will prepare one table with two columns, one with the timestamp and another with timestamptz. Check the current timezone and insert one record in the above table and check its value. And then, change the time zone of the database server and check the outputs. Let us first list out all available databases and then switch o a database named educational_platform by using \list and then \c educational_platform.
\list
Let us check all the tables in it with the help of \dt command and then create one table named establish_timestamp in the following way.
CREATE TABLE establish_timestamp (timestampCol TIMESTAMP, timestamptzC0l TIMESTAMPTZ);
With the use of the SHOW TIMEZONE command, let’s examine the current timezone.
SHOW TIMEZONE;
As can be seen, the current timezone is Asia/Kolkata. To get the current timestamp, we can use the command SELECT NOW(), which gives the output as follows.
SELECT NOW();
Now let us insert a record in our table establish_timestamp with the same values in both columns using
INSERT INTO establish_timestamp (timestampCol, timestamptzCol) VALUES('2020-03-27 18:10:57','2020-03-27 18:10:57');and check whether the value is inserted with the help of the command
SELECT * FROM establish_timestamp;
Let us change our time zone to America/New_York using the command
SET timezone = 'America/New_York';and verify the current timezone by the command
SHOW TIMEZONE;
As can be seen from the output, our timezone is changed successfully. Now its time to retrieve the values from our table establish_timestamp and see what both the columns show using the command
SELECT * FROM establish_timestamp;
The values displayed in both columns differ noticeably. The timestamptzcol column shows the modified value aligned with America’s timezone, while the timestampcol column shows the unchanged value representing Kolkata’s timezone as it was originally stored. Don’t forget to reset the timezone;)!
Date and Time-related Functions
We will first try to retrieve the current date and time using the CURRENT_DATE and CURRENT_TIME functions using the following query statements –
SELECT CURRENT_DATE;that results in
SELECT CURRENT_TIME;that gives output –
Consider a particular date, for example, “1996-01-26” is the birth date of someone. We have to find out how old is that person. Then we will use the following query statement to find the same.
SELECT AGE('1996-01-26');whose output is as follows –
So that person is 24 years, 2 months, and 12 days old.
Let us find out how old was that person on the 1st of June 2019, i.e, ‘2019-06-01’. Then we can find out the result using the following query statement –
SELECT AGE('2019-06-01','1996-01-26');
So he/she was 23 years, 4 months, and 6 days old on the 1st of June 2019 if the birth date is 26th of January 1996. Note that the first parameter should always be the greater value that is a recent date, and the second one should be an older and smaller value. If not mentioned so, then the result will be negative. Let us confirm the case by just swiping the first and second parameters –
SELECT AGE('1996-01-26', '2019-06-01');whose output is as follows –
Let us find the month in the timestamp using the date_part() function using the following query statement –
SELECT date_part('month', TIMESTAMP '2020-04-10 20:38:40');that results in the following output –
Let us retrieve the century of the same timestamp –
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2020-04-10 20:38:40');whose output is as follows –
So the timestamp belongs to the 21st century.
Let us test whether the timestamp used above is finite or not using isfinite() function –
SELECT isfinite(date '2020-04-10 20:38:40');that results in
t stands for true. Hence the timestamp holds a finite value.
Let us justify 85 hours in years, months, days and time format.
SELECT justify_hours(interval '85 hours');that results in –
Hence, 85 hours are equivalent to 3 days and 13 hours.
Conclusion
In PostgreSQL, you can leverage excellent temporal datatypes and functions to streamline your work with date, time, and timestamps in databases, reducing your efforts effectively. We should try using them frequently.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Datetime” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
