- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Extract day, hour, minute, etc. from a datetime column in PostgreSQL?
23 Lectures 1.5 hours
126 Lectures 10.5 hours
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
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
- MySQL convert timediff output to day, hour, minute, second format?
- How to extract only the month and day from a datetime object in Python?
- Fetch month, day, year, etc. from ISODate in MongoDB?
- How can I extract minute from time in BigQuery in MySQL?
- Display just hour and minute using Formatter in Java
- How to extract from datetime column in MySQL by comparing only date and ignoring whitespace?
- Extract the Day / Month / Year from a Timestamp in PHP MySQL?
- Python Pandas - Extract the minute from DateTimeIndex with specific time series frequency
- Java Program to display hour and minute in AM or PM
- MySQL select distinct dates from datetime column in a table?
- Program to find angle between hour and minute hands of a clock in C++?
- Adding a day to a DATETIME format value in MySQL?
- What are the ways to extract features from a DateTime variable using pandas?
- Python Pandas - Get the minute of the hour component of the Period
- MySQL query to remove string from a column with values EMP1, EMP2, EMP3, etc.