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?
- Key Features of PostgreSQL
- Why Work With PostgreSQL Convert DateTime to Date Functions?
- How to Perform PostgreSQL Convert DateTime 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.
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.
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
|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)|
|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)|
Along with these formats, PostgreSQL also offers certain modifiers for PostgreSQL convert DateTime to date operation. These are:
|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:
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;
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.
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:
|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.
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.
- 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
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.