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 functions that can help you convert DateTime 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.
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.
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
Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.
Start for free now!
Get Started with Hevo for Free
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 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 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.
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 decide 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.
With a background in marketing research and campaign management at Hevo Data and myHQ Workspaces, Divyansh specializes in data analysis for optimizing marketing strategies. He has experience writing articles on diverse topics such as data integration and infrastructure by collaborating with thought leaders in the industry.