Скачать или смотреть How to Truncate Milliseconds in PostgreSQL from Three Digits to Two Digits

Learn how to format timestamps in PostgreSQL to display only two digits for the milliseconds part, perfect for cleaner output in your applications.
---
This video is based on the question https://stackoverflow.com/q/63173883/ asked by the user 'Mano' ( https://stackoverflow.com/u/5944884/ ) and on the answer https://stackoverflow.com/a/63174260/ provided by the user 'Stefanov.sm' ( https://stackoverflow.com/u/2302032/ ) 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: Postgres truncate the millisecond part from three to two digits

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.
---
Truncate Milliseconds in PostgreSQL: A Simple Guide

When working with timestamps in PostgreSQL, you might encounter a situation where you need to format the output to meet specific requirements. One common task is truncating the milliseconds part of a timestamp from three digits to just two. This guide will guide you through the process step by step, ensuring you understand each aspect of the solution.

The Problem

You have a timestamp formatted like this:

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

However, you need it to be displayed with only two digits for the milliseconds part, like so:

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

You might be using a query like:

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

But this query returns three digits for the milliseconds, and you want to change that. Let's explore how to achieve this.

The Solution

To format your timestamp correctly, you can use a combination of string manipulation and PostgreSQL functions. The suggested solution is straightforward and can be broken down into clear steps.

Step-by-Step Breakdown

Round the Microsecond Digits: Start by rounding your timestamp to only include two digits for milliseconds.

Convert to String: Use the to_char function to convert your formatted timestamp to a string.

Chop the String: Limit the result to the first 22 characters.

Concatenate the Meridiem Indicator: Finally, append the AM/PM indicator to your result.

The SQL Query

Here's the complete SQL query that accomplishes this formatting:

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

Explanation of the Query

input_timestamp::timestamp(2): This part rounds the input timestamp to two digits in the milliseconds.

to_char(..., 'YYYY-MM-DD HH:MI:SS.MS'): This converts the timestamp to the desired string format.

left(..., 22): This function truncates the string to ensure only the first 22 characters are kept (removing the extra milliseconds if they exist).

to_char(input_timestamp, 'FM AM'): Finally, this adds the AM/PM indicator to the formatted string without any additional spaces.

Conclusion

Formatting timestamps in PostgreSQL can be tricky, especially when dealing with milliseconds. However, by following these steps and using the provided SQL query, you can easily truncate the milliseconds from three digits to two digits. This not only makes your data cleaner but also simplifies the output for your applications.

Feel free to reach out if you have any questions or need further assistance with PostgreSQL or any other database-related topics!