EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Datetime
 

PostgreSQL Datetime

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 17, 2023

PostgreSQL Datetime

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Postgresql Datetime output 1

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);

Postgresql Datetime output 2

With the use of the SHOW TIMEZONE command, let’s examine the current timezone.

SHOW TIMEZONE;

Postgresql Datetime output 3

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();

Postgresql Datetime output 4

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;

Postgresql Datetime output 5

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;

output 6

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;

output 7

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

output 8

SELECT CURRENT_TIME;

that gives output –

output 9

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 –

output 10

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');

output 11

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 –

output 12

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 –

output 13

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 –

output 14

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

output 15

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 –

output 16

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.

  1. PostgreSQL JSON
  2. Timestamp PostgreSQL
  3. PostgreSQL LIMIT
  4. PostgreSQL IN Operator

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW