PostgreSQL

PostgreSQL Convert Timestamp to Date

By now, we have realized that PostgreSQL provides us with a wide range of functions that can be used with a date. At times, these functions are used to convert a given string to a date, and sometimes, they are simply used to get the current system date. However, there are such situations in which we are provided with a timestamp, and we want to convert it to date. To facilitate this, PostgreSQL offers us different functions which are capable of converting the given timestamp to a date very easily. This article will focus on some of the most efficient methods of converting the given timestamp to a date in PostgreSQL in Windows 10.

Ways of Converting the Timestamp to Date in PostgreSQL in Windows 10:

By the word timestamp, we essentially mean the date and time combined. This value can correspond to the current date and time or any other random date and time. However, we are not concerned with the time part of this timestamp; rather, we only require its date part. In such a situation, we might want to truncate the time part from this timestamp while saving only its date part for further execution of queries or simply saving that date. In this regard, PostgreSQL presents us with different ways to convert the given timestamp to date. Four of the most commonly used methods of doing so are discussed below, using which you can easily convert any given timestamp to date very quickly.

Method # 1: Using the Now Function in PostgreSQL:

The “Now” function of PostgreSQL can be used to get the current timestamp, i.e., the current date and current time. When it is combined with the “date” keyword with the help of the “::” operator, then it can be used to convert the current timestamp to date. You are simply required to run the following query to visualize this:

# SELECT NOW()::date;

This query will get the current timestamp through the “Now” function, and the “::” operator followed by the “date” keyword will simply extract the current date from this timestamp while dropping the current time. Finally, the “SELECT” statement will display these results on the console as shown in the image below:

Method # 2: Using the To_Char Function with the Now Function in PostgreSQL:

The PostgreSQL query representation shown in the above example is used to get the current date from the current timestamp, but it always displays the date in the yyyy-mm-dd format in the output by default. If you want to customize the output format, i.e., you want to get the date from the timestamp in formats other than the default one, then you will have to take help from the following query:

# SELECT TO_CHAR(NOW() :: DATE, ‘dd-mm-yyyy’);

In this query, we have the same “Now” function for extracting the current timestamp; however, the “DATE” keyword is followed by a customized date format, i.e., dd-mm-yyyy, which is different from the default date format. You can also have any other date format of your choice over here. All of these arguments are finally passed on to the “TO_CHAR” function, which will perform the final conversion of the current date into the format that you will specify in this query, and all this will be displayed on the console with the help of the “SELECT” statement as shown in the image below:

Method # 3: Using the Extract Function in PostgreSQL:

Instead of extracting the whole date from the specified timestamp, you might just want a specific part of the date, i.e., year, month, or day to be displayed. In that case, you will need to execute a different PostgreSQL query which is as follows:

# SELECT EXTRACT(MONTH FROM TIMESTAMP ‘2021-03-22 10:25:15’) as month;

In this query, we wanted to extract the month from the specified timestamp. Therefore, we have passed the “MONTH” keyword as an argument to the “Extract” function followed by the “TIMESTAMP” keyword, which is proceeded by a random timestamp (you can also use the current timestamp over here if you want). Finally, the “as month” phrase will simply label our output for more clarity. You have the choice to skip this phrase if you wish. Again, the “SELECT” statement will be responsible for displaying the desired output on the console, as shown in the image below:

In the very same manner, you can also extract the year or day from the specified timestamp by making use of the “Extract” function of PostgreSQL simply by replacing the “MONTH” keyword in the arguments with “YEAR” or “DAY” keywords, respectively.

Method # 4: Using the Date_Part Function in PostgreSQL:

The “Date_Part” function of PostgreSQL can also be used to get the day, month, and year, i.e., the date from the given timestamp. To make the “Date_Part” function to perform this task, you will have to execute the following PostgreSQL query:

# SELECT date_part(‘day’, TIMESTAMP ‘2021-09-13 12:30:10’) d, date_part(‘month’, TIMESTAMP ‘2021-09-13 12:30:10’) m, date_part(‘year’, TIMESTAMP ‘2021-09-13 12:30:10’) y;

This query will run the “Date_Part” function of PostgreSQL thrice to get the day, month, and year from the given timestamp one by one. These three entities cannot be extracted at once using the “Date_Part” function; rather, you will have to run this function thrice within the same query in any case, which is why it is a relatively inefficient way of extracting the date from the given timestamp. However, you do have the liberty to change the output format in which you will get the date after the execution of this query simply by changing the order of this query. For example, you can get the year first, followed by the month and the day from the given timestamp. Again, you can also use the current timestamp over here.

Moreover, we have made use of the “d”, “m”, and “y” characters over here just to label these entities of the date in our output for an added level of clarity. The date thus extracted from the specified timestamp by running the query stated above is shown in the image below:

Conclusion:

This guide was based on the conversion of the given timestamp to date in PostgreSQL in Windows 10. We first tried to explain what exactly do we mean by a timestamp and why in the first place do we need to convert it to date. After that, we presented you with different methods one by one, using which you can easily convert any given timestamp to date in PostgreSQL. If we talk about the efficiency of these methods, then the first two methods are the best ones for achieving this goal. As far as the third method is concerned, then using that method, you can only extract a single entity of date from the given timestamp at a time, i.e., year, month, or day. Talking about the fourth method, since it executes the same function thrice, its computational cost is high, making it relatively less efficient. However, these methods can be conveniently used to extract the date from the given timestamp in PostgreSQL in Windows 10.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.