EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL DATE_PART()

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL DATE_PART()

PostgreSQL DATE_PART()

Introduction to PostgreSQL DATE_PART()

PostgreSQL DATE_PART() function is mainly used to return the part of the date and time; the date_part function in PostgreSQL will subtract the subfield from the date and time value. PostgreSQL date_part function will allow retrieving subfields, e.g. week, month and year, from the date and time value. Basically, there are two parameters we have a pass with date_part function, i.e. field and source; field determines which field we have extracting from the source.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Given below is the syntax of the PostgreSQL date_part function:

date_part (text, timestamp)

date_part (text, interval)

date_part (unit, date)

date_part (field, source)

Given below is the parameter description of the above syntax: 

  • Date part: PostgreSQL date_part function is used to extract the date part values from the expression. We can provide an argument with the date_part function as text and timestamp in PostgreSQL.
  • Text: Text is the first parameter which we have used with the date_part function in PostgreSQL.
  • Timestamp: This is the second parameter of the date_part function in PostgreSQL. We have used to define the timestamp value with date_part function in PostgreSQL.
  • Interval: Interval is used with the same as timestamp value in PostgreSQL. We can use an interval instead of a timestamp value in PostgreSQL.
  • Unit: Unit type in date_part function is interval such as minute, hour and month. Unit is an essential parameter in the date_part function.
  • Date: This is defined as date, timestamp, time or interval value which is used to be partly executed.
  • Field: This is a parameter of the date_part function in PostgreSQL used to which field to extract from the source.
  • Source: Source is defined in the date_part function as the second parameter. This is the same as the interval parameter in the date_part function in PostgreSQL.

How PostgreSQL DATE_PART() Function works?

If we want to return only date and time value in PostgreSQL same time, we are using the date_part function. Using the date_part function in PostgreSQL, we can extract the century from the time stamp. For extracting year from the date timestamp, we need to pass the field argument in the date_part function.

In PostgreSQL, the unit type has the following form used in the date_part function. The unit type is nothing but a day, month, minute and hour.

  • Century: It uses the Gregorian calendar in which the first century will start from the ‘0001-01-01 00:00:00 AD’.
  • Day: This is defined as the day of the month from 1 to 31.
  • Decade: Decade is defined as the year divided by 10 in PostgreSQL date_part function.
  • Day of week: This is defined as a day of the week from Sunday to Saturday.
  • Day of year: This is defined as a day of the year from January to December.
  • Epoch: This is defined as several interval values in the date_part function.
  • Hour: This is defined as an hour of the day from 0 to 23.
  • Millennium: This is defined as the millennium value in the date_part function.
  • Milliseconds: This is defined as a second multiply with 1000.
  • Microseconds: This is defined as a second multiply with 100000.
  • Minute: This is defined as a minute of the hour from 0 to 59.
  • Quarter: Quarter is defined in date_part function is 1 to 4.
  • Second: This is fractional second in the date_part function.
  • Year: This is defined as a year in four-digit value.

To extract the second, minute and hour from timestamp by using the date_part function, we need to pass the corresponding value that is second, minute and hour.

Examples of PostgreSQL DATE_PART()

Given below are the examples mentioned:

Example #1

Extract century from a timestamp using the date_part function.

In the below example, we have used the year as 2020 and 1920 to extract a century from the timestamp.

Code:

SELECT date_part('century', TIMESTAMP '2020-05-19');

SELECT date_part('century', TIMESTAMP '1920-05-19');

Output:

PostgreSQL DATE_PART() 1

In the above example, using the year like 1920, it will display a century as 20 and using the year like 2020, it will display the century as 21.

Example #2

Extract year from a timestamp using the date_part function.

In the below example, we have used the year as 2020 to extract the year from the timestamp.

Code:

SELECT date_part('year', TIMESTAMP '2020-05-19');

Output:

Extract year

Example #3

Extract quarter from a timestamp using date_part function.

In the below example, we have used the year as 2020 to extract a quarter from the timestamp.

Code:

SELECT date_part('quarter', TIMESTAMP '2020-05-19');

Output:

Extract quarter from timestamp

Example #4

Extract month from a timestamp using date_part function.

In the below example, we have used a month as May to extract the month from the timestamp.

Code:

SELECT date_part('month', TIMESTAMP '2010-05-19');

Output:

Extract month from timestamp

Example #5

Extract week from a timestamp using date_part function.

In the below example, we have used the year as 2010 and the month as May to extract the week from the timestamp.

Code:

SELECT date_part(week, TIMESTAMP '2010-05-19');

Output:

Extract week from timestamp

Example #6

Extract day from a timestamp using the date_part function.

In the below example, we have used the year as 2020 and the month as May to extract the day from the timestamp.

Code:

SELECT date_part('day',TIMESTAMP '2020-05-19 00:00:00');

Output:

PostgreSQL DATE_PART() 6JPG

Example #7

Extract hour, minute and second from a timestamp using the date_part function.

The below example shows that Extract hour, minute and second from a timestamp using the date_part function.

Code:

SELECT date_part('hour', TIMESTAMP '2020-05-19 12:30:40') h,  date_part('minute', TIMESTAMP '2020-05-19 12:30:40') m, date_part('second',TIMESTAMP '2020-05-19 12:30:40') s;

Output:

PostgreSQL DATE_PART() 7 JPG

Recommended Articles

This is a guide to PostgreSQL DATE_PART(). Here we discuss the introduction to PostgreSQL DATE_PART() with the working and examples, respectively. You may also have a look at the following articles to learn more –

  1. PostgreSQL GRANT
  2. PostgreSQL Trunc()
  3. PostgreSQL Auto Increment
  4. PostgreSQL Primary Key

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More