0

This is the issue: I am reading rows with a timestamp column from a table and I need to make sure that the database returns the timestamp with a fixed width. But I am encountering instances where the PostgreSQL database returns the timestamp without the trailing zeros.

The table I am working on contains UUID, Varchar, Smallint and Timestamp. I understand that I can use "to_char" as explained here, but what I am really looking for is a way tell PostgreSQL to always return the Timestamp in a given format, regardless of zeros.

Here is an example of the output from the Timestamp column I get, which contains a trimmed Timestamp:

"2014-09-26 17:17:45.999"
"2014-11-26 17:17:46"
"2015-03-26 17:17:46.001"
"2015-04-26 17:17:46.002"
"2015-05-26 17:17:46.003"

This will naturally cause a problem for me since the function that is reading from the database expects to get a timestamp in a given format.

Is there a way to make the PostgreSQL always return a timestamp with a fixed width on SELECT * FROM [table] queries?

Below you see how the fields are created:

"created_UTC" timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'),
"last_modified_UTC" timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')
  • 2
    Try the Postgres documentation on date formatting. – LowlyDBA Jan 26 '15 at 18:25
  • Doesn't the answer in the linked question say it all?: "Doesn't look like there is any such option." – ypercubeᵀᴹ Jan 27 '15 at 18:14
  • 1
    the function that is reading from the database expects to get a timestamp in a given format To me this means that the given function expect these to be text parameters, not timestamps. It would otherwise be content with the timestamp... – dezso Jan 27 '15 at 20:22
0

It seems to me that you need to set the Timestamp column to CURRENT_TIMESTAMP(3). This would set the column to return the time to the three digit precision you are looking for.

  • I added the field creation text for clarification. – Bam Jan 27 '15 at 17:51
0

After some back and forth, I managed to figure what was actually going on and how to fix it.

the function that is reading from the database expects to get a timestamp in a given format To me this means that the given function expect these to be text parameters, not timestamps. It would otherwise be content with the timestamp... – dezso Jan 27 at 20:22

This was helpful because it lead to me to understand that if you ask the JDBC for PostgreSQL to return a Timestamp as String, it will automatically trim the zeroes at the end.

Example:

ResultSet rs = {get rows from database}
rs.getString(TIMESTAMP_COLUMN);

will return a potentially shortened (trimmed) string with the last zeroes removed.
While:

ResultSet rs = {get rows from database}
rs.getTimestamp(TIMESTAMP_COLUMN);

will actually return a Java Timestamp object, with no trimming issues.

This is unexpected behavior and I'm not sure what the reasoning behind trimming strings can be, but I have at least figured out where the issue is and how to fix it.

Many thanks for the assistance. Huzzah!

  • 1
    There is no "trimming" involved. Text representation is derived from the internal numerical value according to formatting instructions. I wouldn't trust JDBC to do the right thing automatically. That can depend on locale and time zone settings of the session. To get a custom text representation, use to_char() as mentioned in the referenced answer. – Erwin Brandstetter Feb 1 '15 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.