Скачать или смотреть Converting 02-3月-21 to a Proper Date Format in PostgreSQL

Learn how to convert a string like `02-3月-21` into the date format `21-03-02 00:00:00` using PostgreSQL and Kettle.
---
This video is based on the question https://stackoverflow.com/q/67597719/ asked by the user 'Elsa' ( https://stackoverflow.com/u/9037708/ ) and on the answer https://stackoverflow.com/a/67598165/ provided by the user 'Mic Fung' ( https://stackoverflow.com/u/15569492/ ) 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: How to convert '02-3月 -21' to date 21-03-02 00:00:00 psql

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.
---
How to Convert '02-3月-21' to Date in PostgreSQL

In the world of data management, it's common to come across date formats that do not conform to standard practices. One such example is the date string 02-3月-21, which, thanks to the inclusion of the Chinese character for "month" (月), presents a challenge. Understanding how to convert this to a standard format like 21-03-02 00:00:00 in PostgreSQL can save you a lot of headaches, especially when you’re integrating data.

In this guide, we will explore a straightforward solution to transforming this date string into a more usable format using PostgreSQL.

The Problem

You start with a date represented as 02-3月-21, which is a blend of English numerals and a Chinese character. The goal is to convert this string into a valid timestamp that PostgreSQL understands, specifically:

Input: '02-3月-21'

Desired Output: 21-03-02 00:00:00

The Solution

To tackle this conversion efficiently, we can utilize PostgreSQL's built-in string manipulation functions. Here’s a step-by-step breakdown of the process:

Step 1: Remove the Chinese Character

The first step involves eliminating the 月 character from the string. This can be accomplished using the REPLACE function in PostgreSQL. Here’s how it looks in SQL:

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

This operation will transform the string into 02-3-21.

Step 2: Convert the Modified String to a Timestamp

Next, we’ll convert the modified string to a timestamp format. PostgreSQL provides the TO_TIMESTAMP function for this purpose. You can specify the format in which the string is structured, which in this case is DD-MM-YY. Here’s the full SQL command:

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

This will yield a timestamp: 2021-03-02 00:00:00+ 00.

Step 3: Formatting the Output

If you’re interested in specifically formatting the output to match YY-MM-DD HH24:MI:SS, you can use the TO_CHAR function. It translates the timestamp back into a string according to the specified format:

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

The final output of this command will give you what you set out to achieve: 21-03-02 00:00:00.

Summary

Converting a date string that includes non-standard characters may seem daunting at first, but by utilizing PostgreSQL’s powerful text manipulation functions, you can streamline the process.

Key Takeaways:

Use REPLACE to clean up the string.

Employ TO_TIMESTAMP for conversion to a timestamp.

Format the result with TO_CHAR for your desired output.

This method can be particularly useful not just in PostgreSQL, but also in data integration scenarios via tools like Kettle, where data cleansing and formatting are paramount for effective data management.

With these techniques at your disposal, you’ll be able to handle tricky date string formats with confidence. Happy querying!