Скачать или смотреть How to Format Date and Time in PostgreSQL like Oracle? 2020-12-17

Learn how to format date and time in PostgreSQL to achieve a cleaner output like Oracle does with simple SQL queries.
---
This video is based on the question https://stackoverflow.com/q/65346479/ asked by the user 'elbarna' ( https://stackoverflow.com/u/3970561/ ) and on the answer https://stackoverflow.com/a/65347295/ provided by the user 'Bjarni Ragnarsson' ( https://stackoverflow.com/u/11993679/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: A little help for a correct date + hour:minute like Oracle for Postgresql

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Formatting Date and Time in PostgreSQL like Oracle

In the world of databases, different systems have their ways of handling data types, especially when it comes to date and time. If you've transitioned from Oracle to PostgreSQL, you might be feeling a bit out of place—especially when it comes to date formatting. A common concern is getting PostgreSQL to present date and time in a format that is easy to read and aligns with what you're used to in Oracle. In this guide, we will walk you through how to format date and time in PostgreSQL similarly to what you would find in an Oracle database.

The Problem

You might be familiar with how Oracle displays its date and time, as shown in the following example:

[[See Video to Reveal this Text or Code Snippet]]

However, in PostgreSQL, you may see an output like this:

[[See Video to Reveal this Text or Code Snippet]]

This longer format with fractional seconds can be cumbersome and often unnecessary. You may prefer a cleaner display, such as:

[[See Video to Reveal this Text or Code Snippet]]

The goal is to present datetime values in a cleaner format without the extra millisecond detail.

The Solution

To achieve this desired format in PostgreSQL, you can utilize the to_char() function. This function allows you to format timestamps in a way that is straightforward and matches your specifications.

Step 1: Use the Correct SQL Query

You can use a simple SQL query to format the current date and time like so:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

to_char(): This function is used to convert a timestamp or date into a string with a specified format.

now(): This function retrieves the current date and time in PostgreSQL.

Format String:

YYYY: Four-digit year (e.g., 2020)

MM: Two-digit month (e.g., 12)

DD: Two-digit day of the month (e.g., 17)

HH24: Hour of the day in 24-hour format (e.g., 18)

MI: Minutes (e.g., 30)

When you run the provided query, it should yield output similar to:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Integrate it into Your Views

If you have a view you created and would like to update the output format, you can modify the SELECT statement like this:

[[See Video to Reveal this Text or Code Snippet]]

This way, your view will display the timestamp in the more readable format that you prefer.

Conclusion

Handling date and time formats can be a tedious task, especially when transitioning from one database system to another. Fortunately, PostgreSQL offers powerful tools such as to_char() to format your datetime values in a cleaner, more elegant manner. Now, you can effortlessly view your dates and times in the preferred style, just like you did in Oracle. Happy querying!