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

PostgreSQLDatabaseData Storage

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 −

ts
2021-01-30 19:23:24.008087
2021-02-04 19:23:24.008087
2021-01-30 01:23:24.008087
2022-01-30 19:23:24.008087
2021-01-30 19:26:24.008087
2015-01-30 19:23:24.008087

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

hour
19
19
1
19
19
19

Similarly −

SELECT EXTRACT(MONTH from ts) as month from timestamp_test
month
1
2
1
1
1
1

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
centuryweek
214
215
214
214
214
215

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 

raja
Published on 02-Feb-2021 13:07:54
Advertisements