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 to retrieve and manage them properly and efficiently as per use-case requirements and purposes. In this article, we will learn about the datatypes and syntaxes and examples of some of the most frequently used data and time-related functions that are used in PostgreSQL and discuss the 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 database |
Timestamp allows us to store the date as well as 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. Whenever this value is tried to fetch the PostgreSQL database server retrieves the UTC value from the table and then that value is converted into an appropriate time zone set which is generally set in the database server or by the user whichever is 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 current timestamp or 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 particular value from the date, for example, a year or just 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 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);
let us check out the current timezone with the help of SHOW TIMEZONE command.
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 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 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;
It can be easily seen that both columns show different values. The column timestamptzcol shows the changed value according to America’s timezone while timestampcol shows Kolkata’s timezone value which is unchanged as it was stored. Don’t forget to reset the timezone;)!
Date and Time-related Functions
We will first try to retrieve 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 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 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 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 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
PostgreSQL provides us with great temporal datatypes and functions which can be used easily and effectively to reduce our efforts while working with date, time and timestamps in databases. We should try using them frequently.
Recommended Articles
This is a guide to PostgreSQL Datetime. Here we discuss the examples of PostgreSQL Datetime along with the Date and Time-related Functions. You may also have a look at the following articles to learn more –
