Extract day, hour, minute, etc. from a datetime column in PostgreSQL?

Let us create a new table containing a single timestamp column −

CREATE TABLE timestamp_test(
   ts timestamp
);

Now let us populate it with some data −

INSERT INTO timestamp_test(ts)
VALUES(current_timestamp),
(current_timestamp+interval '5 days'),
(current_timestamp-interval '18 hours'),
(current_timestamp+interval '1 year'),
(current_timestamp+interval '3 minutes'),
(current_timestamp-interval '6 years');

If you query the table (SELECT * from timestamp_test), you will see the following output −

Now, in order to extract hour, minute, etc. from the timestamp column, we use the EXTRACT function. Some examples are shown below −

SELECT EXTRACT(HOUR from ts) as hour from timestamp_test

Output −

Similarly −

SELECT EXTRACT(MONTH from ts) as month from timestamp_test

You can also extract not-so-obvious values like the ISO week, or the century −

SELECT EXTRACT(CENTURY from ts) as century, EXTRACT(WEEK from ts) as week from timestamp_test

To get a complete list of values you can extract from a timestamp column, see https://www.postgresql.org/docs/9.1/functions-datetime.html 

Yash Sanghvi

e

Updated on: 02-Feb-2021

945 Views

Kickstart Your Career

Get certified by completing the course

Get Started