PostgreSQL Convert DateTime to Date: 10 Practical Examples Simplified

on Data Cleaning, Database Management Systems, PostgreSQL, Relational Database, Tutorials • June 23rd, 2022 • Write for Hevo

PostgreSQL Convert Datetime To Date- Featured Image

DateTime variables store important information, such as the date, time, and timezone for when a transaction took place or when a new entry was made in your PostgreSQL tables. Date and time features are not only helpful 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 functions that can help you convert DateTime values or given timestamps in your PostgreSQL tables to dates easily. We will share 10 practical examples to help you better understand the methods for Postgres to convert timestamp to date.

Table of Contents

What Is PostgreSQL?

PostgreSQL Logo: PostgreSQL Convert DateTime to Date
Image Source: Software Engineering Daily

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.

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 took place 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. Being able to extract 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.

In the upcoming sections of the PostgreSQL convert DateTime to date guide, we explore various examples to help you clean your data, perform PostgreSQL format date and extract the required pieces of information from DateTime formats.

Replicate PostgreSQL Data in Minutes Using Hevo’s No-Code Data Pipeline

Hevo Data, a Fully-managed Data Pipeline platform, can help you automate, simplify & enrich your data replication process in a few clicks. With Hevo’s wide variety of connectors and blazing-fast Data Pipelines, you can extract & load data from 100+ Data Sources like PostgreSQL, MySQL, and MS SQL Server, straight into your Data Warehouse, or any Databases.

To further streamline and prepare your data for analysis, you can process and enrich raw granular data using Hevo’s robust & built-in Transformation Layer without writing a single line of code!

Get Started with Hevo for Free

Hevo is the fastest, easiest, and most reliable data replication platform that will save your engineering bandwidth and time multifold. Try our 14-day full access free trial today to experience an automated, hassle-free PostgreSQL Data Replication!

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

FormatDescription
HHHour of the day (01-12)
HH12Hour of the day in 12-hour format (01-12)
HH24Hour of the day in 24-hour format (00-23)
MIMinute of the hour (00–59)
SSSecond of the minute (00–59)
MSMillisecond (000–999)
USMicrosecond (000000–999999)
AM, am, PM or pmMeridiem indicator (without periods)
A.M., a.m., P.M. or p.m.Meridiem indicator (without periods)
YYYYCalendar year (4 or more digits)
YYYCalendar year (last 3 digits)
YYCalendar year (last 2 digits)
YCalendar year (last digit)
MONTHName of the month in uppercase
MonthName of the month in capitalized form
monthName of the month in lowercase
MONAbbreviated month name in uppercase (first 3 characters)
MonAbbreviated month name in capitalized form (first 3 characters)
monAbbreviated month name in lowercase (first 3 characters)
MMMonth number (01-12)
DAYName of the day in uppercase
DayName of the day in capitalized form
DYAbbreviated day name in uppercase (first 3 characters)
DyAbbreviated day name in capitalized form (first 3 characters)
dyAbbreviated day name in lowercase (first 3 characters)
DDDCalendar year day (001–366)
DDMonth day (01-31)
DWeekday (Sunday (1) to Saturday (7))
WWeek number of the month (1–5)
WWWeek number of the year (1-53)
QQuarter of the year
RMName of the month in uppercase Roman numerals (I–XII; I=January)
TZThe abbreviated timezone in uppercase (only supported in to_char)
tzThe abbreviated timezone in lowercase (only supported in to_char)
TZHTime-zone hours
TZMTime-zone minutes

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

ModifierDescriptionExample
FM prefixFill mode (suppress leading zeroes and padding blanks)FMMonth
TH suffixList ordinal number suffix in uppercaseDDTH, e.g., 07th
th suffixList ordinal number suffix in lowercaseDDth, e.g., 07th
FX prefixUse fixed format global optionFX Month DD Day
TM prefixTranslation modeTMMonth
SP suffixSpell 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_transactiontransaction_time
2022-05-11 22:16:4310:16:43
2022-05-11 22:18:3710:18:37
2022-05-12 23:24:4011:24:40
2022-05-12 20:19:5708: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_transactiontransaction_time
2022-05-11 22:16:432022-05-11
2022-05-11 22:18:372022-05-11
2022-05-12 23:24:402022-05-12
2022-05-12 20:19:572022-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.

What Makes Hevo’s ETL Process Best-In-Class

Providing a high-quality ETL solution can be a difficult task if you have a large volume of data. Hevo’s Automated, No-code ETL Platform empowers you with everything you need to have for a smooth data replication experience.

Check out what makes Hevo amazing:

  • Fully Managed: Hevo requires no management and maintenance as it is a fully automated platform.
  • Data Transformation: Hevo provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Faster Insight Generation: Hevo offers near real-time data replication so you have access to real-time insight generation and faster decision making. 
  • Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ Data Sources like PostgreSQL (with 40+ free sources) that can help you scale your data infrastructure as required.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-Day Free Trial!

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 ValueTIMESTAMPInterval
CENTURYCentury Number of centuries
DAYDay of the month (1-31)Number of days
DOYDay of the year (1-366)N/A
DOWDay of the week (Sunday (0) to Saturday (6))N/A
EPOCHTime in number of seconds since 1970-01-01 00:00:00 UTCNumber of seconds in the interval
HOURHour of the day (0-23)Number of hours
MICROSECONDSThe seconds field, including fractional parts, multiplied by 1000000 The seconds field, including fractional parts, multiplied by 1000000
MILLENNIUMThe millenniumNumber of millennium
MILLISECONDSThe seconds field, including fractional parts, multiplied by 1000The seconds field, including fractional parts, multiplied by 1000
MINUTEMinute of the hour (1-59)Number of minutes
MONTHMonth of the year (1-12)Number of months, modulo (0-11)
QUARTERQuarter of the yearNumber of quarters
SECONDSecond of the hour (1-59)Number of seconds
TIMEZONETimezone offset from UTC, measured in secondsN/A
TIMEZONE_HOURHour component of TIMEZONEN/A
TIMEZONE_MINUTEMinute component of TIMEZONEN/A
WEEKNumber of the ISO 8601 week-numbering week of the yearN/A
YEARCalendar 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.

Related

Conclusion

This guide presented five 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(). While some functions, such as TO_CHAR(), can do Postgres convert timestamp 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 into a warehouse using ETL for Data Analysis can get demanding. For starters, this problem is exaggerated by the fact that 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 Data with its strong integration with 100+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, allows you to not only export data from sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Give Hevo a try. Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about PostgreSQL convert DateTime to date operation in the comments section below. If you have any questions, do let us know. We’d be happy to help.

No-code Data Pipeline For PostgreSQL