PostgreSQL Convert DateTime to Date: Practical Examples

DateTime variables store important information, such as the date, time, and timezone for when a transaction occurred or when a new entry was made in your PostgreSQL tables. Date and time features are helpful not only for finance and e-commerce but also for data science projects where you are training models or performing feature engineering. 

But many times, these date and time features are merged into DateTime variables. For PostgreSQL users, working with DateTime variables raises several issues, like “How do I extract the date fields from DateTime?” or “What if I simply need to know the time the transaction occurred?”

In this tutorial, we discuss several PostgreSQL convert DateTime to Date values or given timestamps in your PostgreSQL tables to dates easily. We will share practical examples to help you better understand the methods for Postgres to convert timestamp to date.

What Is PostgreSQL?

PostgreSQL is an excellent open-source, fully-featured Relational Database Management System (RDBMS) developed by the PostgreSQL Global Development Group. It has been in use for over 20 years and is a multi-paradigm database to support relational, object-relational, JSON, XML, and graph data types.

PostgreSQL has had a reputation for being a reliable, feature-rich, and performance-rich utility from its beginnings. Every release is very carefully controlled, and PostgreSQL beta releases are subject to at least a month’s testing. A number of businesses rely on PostgreSQL as their primary data storage/data warehouse for online, mobile, geospatial, and analytics applications. 

PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. 

Learn more about creating PostgreSQL tables in this guide – Creating PostgreSQL Tables: A Comprehensive Guide 101. To know about the best reporting tools available in PostgreSQL, read 5 Best PostgreSQL Reporting Tools.

Key Features of PostgreSQL

• Open-Source: PostgreSQL is an Object-Relational Database Management System (ORDBMS). This allows PostgreSQL to provide both Object-Oriented and Relational Database functionality. It is a free-to-use Open-Source ORDBMS.
• Prominent User Base: PostgreSQL users include prominent names like Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, and Yahoo, to name a few.
• Multiversion Concurrency Control: To manage concurrent requests, PostgreSQL features a multi-version concurrency control which gives each transaction a “snapshot” of the database, allowing changes to be made without affecting other transactions. This largely eliminates the need for read locks, and ensures the database maintains ACID principles. 
• Reliability and Standards Compliance: PostgreSQL’s write-ahead logging makes it a fault-tolerant database. Its large base of open source contributors lends it a built-in community support network. PostgreSQL is ACID compliant, and has full support for foreign keys, joins, views, triggers, and stored procedures, in many different languages. 

A Supportive Community: PostgreSQL offers a dedicated community that is always available to you. Private, third-party support services are also available. The community updates the PostgreSQL platform via the PostgreSQL Global Development Group.

How to Perform PostgreSQL Convert DateTime to Date?

Extracting Date From a Given Timestamp Column

Suppose you have a timestamp column in your PostgreSQL table of customers that contains records of when they successfully completed a transaction on your website. Your timestamp columns may or may not contain timezone information. Let’s assume that your system has recorded DateTime information in the format “YYYY/MM/DD/HH24:MI:ss” and your sales manager wants information about the daily sales made during the month of October.

Using the DATE() function in PostgreSQL you can extract date values from your timestamp column. A simple SQL query does the job to yield the date values from “customer_transactions” that contain timestamp values.

SELECT DATE(customer_transaction) FROM customers;

PostgreSQL Convert DateTime to Date From a Specific Timestamp

One other way using which you can extract date values from a specific timestamp comes through the use “::” operator followed by the “DATE” keyword. 

With this method, you can either perform a specific Postgres convert timestamp to date or convert the current timestamp (using the now() function) to date.

For example, let’s assume you have a timestamp ‘2022-07-07 07:37:33’ from which you need to extract the date field. The following SQL query in PostgreSQL helps you do so:

SELECT '2022-07-07 07:37:33'::TIMESTAMP::DATE;

Output:

    date    
------------
 2022-07-07
(1 row)

Or, assume that you want to extract today’s date from the current timestamp. The now() function in PostgreSQL returns the current date and time with timezone and you can use the following SQL query to perform PostgreSQL convert DateTime to date operation.

SELECT NOW()::DATE;

Output:

    now     
------------
 2022-06-22
(1 row)

PostgreSQL relational database offers a variety of in-built functions like TO_CHAR(), EXTRACT(), and DATE_PART() to help you perform PostgreSQL convert DateTime to date operation. Have a look at those PostgreSQL convert DateTime to date functions in the upcoming sections.

PostgreSQL Format Date Using TO_CHAR() Function

In PostgreSQL relational databases, the TO_CHAR() function converts various data types like timestamp, interval, integer, floating-point, or numeric value to a formatted string. It has a simple syntax as given below.

TO_CHAR(expression, format)

The expression argument takes in the data types like timestamp, interval, integer, floating-point, or a numeric value that you wish to convert to a string as an input. The format argument specifies the format of the resultant string, which can be provided using multiple options as

Format	Description
HH	Hour of the day (01-12)
HH12	Hour of the day in 12-hour format (01-12)
HH24	Hour of the day in 24-hour format (00-23)
MI	Minute of the hour (00–59)
SS	Second of the minute (00–59)
MS	Millisecond (000–999)
US	Microsecond (000000–999999)
AM, am, PM or pm	Meridiem indicator (without periods)
A.M., a.m., P.M. or p.m.	Meridiem indicator (without periods)
YYYY	Calendar year (4 or more digits)
YYY	Calendar year (last 3 digits)
YY	Calendar year (last 2 digits)
Y	Calendar year (last digit)
MONTH	Name of the month in uppercase
Month	Name of the month in capitalized form
month	Name of the month in lowercase
MON	Abbreviated month name in uppercase (first 3 characters)
Mon	Abbreviated month name in capitalized form (first 3 characters)
mon	Abbreviated month name in lowercase (first 3 characters)
MM	Month number (01-12)
DAY	Name of the day in uppercase
Day	Name of the day in capitalized form
DY	Abbreviated day name in uppercase (first 3 characters)
Dy	Abbreviated day name in capitalized form (first 3 characters)
dy	Abbreviated day name in lowercase (first 3 characters)
DDD	Calendar year day (001–366)
DD	Month day (01-31)
D	Weekday (Sunday (1) to Saturday (7))
W	Week number of the month (1–5)
WW	Week number of the year (1-53)
Q	Quarter of the year
RM	Name of the month in uppercase Roman numerals (I–XII; I=January)
TZ	The abbreviated timezone in uppercase (only supported in to_char)
tz	The abbreviated timezone in lowercase (only supported in to_char)
TZH	Time-zone hours
TZM	Time-zone minutes

Along with these formats, PostgreSQL also offers certain modifiers for PostgreSQL convert DateTime to date operation. These are:

Modifier	Description	Example
FM prefix	Fill mode (suppress leading zeroes and padding blanks)	FMMonth
TH suffix	List ordinal number suffix in uppercase	DDTH, e.g., 07th
th suffix	List ordinal number suffix in lowercase	DDth, e.g., 07th
FX prefix	Use fixed format global option	FX Month DD Day
TM prefix	Translation mode	TMMonth
SP suffix	Spell mode	DDSP

You can refer to usage notes for PostgreSQL format date using formats or modifiers in the PostgreSQL documentation.

Coming back to our problem of Postgres convert timestamp to date, you might, at this point, be wondering how to use these options. 

Here’s an example to help you understand. Let’s use the same example of the customer_transactions timestamp column which we had taken earlier. When you execute a SQL query like this,

SELECT
    customer_transaction,
    TO_CHAR(
        customer_transaction,
        'HH12:MI:SS'
    ) transaction_time
FROM
    customers;

The TO_CHAR() function in PostgreSQL returns a column transaction_time, by extracting time and converting it in 12-hour format from the customer_transaction timestamp column.

Here’s what the result would look like:

customer_transaction	transaction_time
2022-05-11 22:16:43	10:16:43
2022-05-11 22:18:37	10:18:37
2022-05-12 23:24:40	11:24:40
2022-05-12 20:19:57	08:19:57

Similarly, a SQL query like this will help you extract the date from your customer_transactions timestamp column.

SELECT
    customer_transaction,
    TO_CHAR(
        customer_transaction,
        ‘dd:mm:yyyy'
    ) transaction_date
FROM
    customers;

customer_transaction	transaction_time
2022-05-11 22:16:43	2022-05-11
2022-05-11 22:18:37	2022-05-11
2022-05-12 23:24:40	2022-05-12
2022-05-12 20:19:57	2022-05-12

PostgreSQL format date extraction can also be performed on the current timestamp, using TO_CHAR() in combination with the now() function.

SELECT TO_CHAR(NOW() :: DATE, 'dd-mm-yyyy');

Output:

  to_char   
------------
 23-06-2022
(1 row)

This SQL query will extract the date from the current timestamp in the format ‘dd-mm-yyyy’. If you wish to select only the day number, you can use the following SQL query.

SELECT TO_CHAR(NOW() :: DATE, 'dd');

 to_char 
---------
 23
(1 row)

You can have multiple date formats of your choice, using the different formats mentioned above in the format table. The final conversion of the current date into the format that you specify in the query will be displayed on the console with the help of the “SELECT” statement as these arguments are finally passed on to the “TO_CHAR” function. Next, in our PostgreSQL convert DateTime to date guide, we discuss the EXTRACT() function.

PostgreSQL Convert DateTime to Date Using EXTRACT() Function

In this section of PostgreSQL convert DateTime to Date, we discuss the PostgreSQL function called the EXTRACT() function. The EXTRACT() function in PostgreSQL helps in fetching date subfields such as year or hour from DateTime. Sometimes you might just want to extract a specific part of the date, let’s say, the calendar year, month, or day, and in those scenarios, you can use the EXTRACT() function.

PostgreSQL EXTRACT() function has the following syntax:

EXTRACT(field FROM source)

EXTRACT() function takes in two arguments, field and source. The field argument specifies the date part, i.e. month, decade, hour, to extract from the DateTime value. The source argument is a value of type TIMESTAMP or INTERVAL. 

Again, PostgreSQL offers a lot of options under the field argument. These are listed below:

Field Value	TIMESTAMP	Interval
CENTURY	Century	Number of centuries
DAY	Day of the month (1-31)	Number of days
DOY	Day of the year (1-366)	N/A
DOW	Day of the week (Sunday (0) to Saturday (6))	N/A
EPOCH	Time in number of seconds since 1970-01-01 00:00:00 UTC	Number of seconds in the interval
HOUR	Hour of the day (0-23)	Number of hours
MICROSECONDS	The seconds field, including fractional parts, multiplied by 1000000	The seconds field, including fractional parts, multiplied by 1000000
MILLENNIUM	The millennium	Number of millennium
MILLISECONDS	The seconds field, including fractional parts, multiplied by 1000	The seconds field, including fractional parts, multiplied by 1000
MINUTE	Minute of the hour (1-59)	Number of minutes
MONTH	Month of the year (1-12)	Number of months, modulo (0-11)
QUARTER	Quarter of the year	Number of quarters
SECOND	Second of the hour (1-59)	Number of seconds
TIMEZONE	Timezone offset from UTC, measured in seconds	N/A
TIMEZONE_HOUR	Hour component of TIMEZONE	N/A
TIMEZONE_MINUTE	Minute component of TIMEZONE	N/A
WEEK	Number of the ISO 8601 week-numbering week of the year	N/A
YEAR	Calendar year	Calendar year

To use the EXTRACT() function for PostgreSQL convert DateTime to date operation, we have listed some examples:

In PostgreSQL format date, to extract a month from a given timestamp,

SELECT EXTRACT(MONTH FROM TIMESTAMP '2022-05-12 18:36:49');

Output:

 extract 
---------
       5
(1 row)

To extract the year, month, and day from the current timestamp, i.e. using the EXTRACT() function in conjunction with now(),

SELECT
	EXTRACT(year  FROM now()) as year,
	EXTRACT(month FROM now()) as month,	
EXTRACT(day FROM now()) as day;

Output:

 year  | month | day 
------+-------+-----
 2022 |   6       |  23
(1 row)

And again, the final conversion of the current date into the format that you specify will be displayed on your PostgreSQL console with the help of the “SELECT” statement.

Postgres Convert DateTime to Date Using the DATE_PART() Function

In this section of PostgreSQL convert DateTime to Date, we discuss another PostgreSQL function called the DATE_PART(). The DATE_PART() PostgreSQL function is another alternative to the EXTRACT() function, which retrieves date subfields such as year or hour from DateTime. It has similar syntax and takes the same arguments of field and source as specified in the EXTRACT() function.

DATE_PART(field,source)

To illustrate how DATE_PART() function works, here are some examples.

SELECT DATE_PART('year',TIMESTAMP '2022-05-12');

 Output:

 date_part 
-----------
      2022
(1 row)

The above-mentioned SQL command extracts ‘year’ from the given timestamp and displays it as the output. To extract the date, month, and year, you can run the following DATE_PART() command.

SELECT DATE_PART('day', TIMESTAMP '2022-05-12 07:37:16') d, DATE_PART('month', TIMESTAMP '2022-05-12 07:37:16') m, DATE_PART('year', TIMESTAMP '2022-05-12 07:37:16') y;

Output:

 d  | m |  y   
----+---+------
 12 | 5 | 2022
(1 row)

This SQL query runs PostgreSQL DATE_PART() function thrice to get the day (d), month (m), and year (y) from the given timestamp one by one. The DATE_PART() function is inefficient when extracting date parts since you have to run the function thrice within the same query since these three entities cannot be extracted at once.

Postgres Convert DateTime to Date Using the CAST Operator

The CAST operator can be used to extract data from a timestamp. Let us look at an example to understand how exactly this operator works.

Syntax:

SELECT CAST('time_stamp' AS DATE);

Example:

SELECT CAST('2023-01-09 20:41:12.791354-08' AS DATE);
Output:
   date
-----------
2023-01-09
(1 row)

Related

• 4 Easy Ways to Convert Timestamp to Date PostgreSQL
• PostgreSQL Cluster Guide 101: Easy Set-Up and How to Deploy for High Availability
• 5 Easy Steps to Carry Out Export Data pgAdmin in PostgreSQL

Why Work With PostgreSQL Convert DateTime to Date Functions?

DateTime formats are essential to every business. Whether you are a financial analyst, who examines financial transactions, or a supply chain manager who maintains inventory records, DateTime formats help you record and identify every time-related element for your business transactions. This includes dates, hours, minutes, seconds, or even days of the week, months, and years when a transaction occurred or when a new record was created in your PostgreSQL database.

Usually, DateTime formats include three data types: DATE, TIME, and TIMESTAMP. These data types are commonly used in relational databases like PostgreSQL, and MySQL and programming languages like Python. Extracting the necessary pieces of information, for example, date from these DateTime formats is an important skill that can further improve your understanding of data and help you better in Data Analysis.

Conclusion

This guide presented six different ways to perform PostgreSQL convert DateTime to date operation. We learned about the available PostgreSQL functions that can be used to convert a timestamp to date, such as DATE(), TO_CHAR(), EXTRACT(), and DATE_PART() and CAST operator. While some functions, such as TO_CHAR(), can convert Postgres timestamps to date in less code, other functions, such as DATE_PART(), require extensive writing to perform the job.

If you are a PostgreSQL user, replicating data in a warehouse using ETL for Data Analysis can get demanding. For starters, this problem is exaggerated because they need extensive money and resources to hire data engineers and analysts to make sense of this data. 

Luckily, you can set up and start Data Replication from PostgreSQL to your favorite warehouse in a matter of minutes using Hevo. Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice, like Amazon Redshift, Snowflake, or  Google BigQuery, within minutes with just a few clicks. Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQ on PostgreSQL Convert DateTime to Date