Subject: Seconds precision in timestamp columns

From: "Luiz E. P. Fernandes" <[email-masked]>
Date: Fri, 16 Jul 2021 16:03:53 -0300

Hi,

In the dvdrental example database, there is a column named 
'payment_date' on the 'payment' table and columns 'rental_date' and 
'return_date' on the 'rental' table.  These columns appear on the 
information_schema.columns view with identical specifications: datatype 
= 'timestamp without timezone', datetime_precision = 6. However, a 
select on table 'payment' presents column 'payment_date' with 6 
fractional digits, while a select on table 'rental' presents columns 
'rental_date' and 'return_date' without fractional digits.

select payment_date from payment limit 1;
--> for example, payment_date: '2007-02-15 22:25:46.996577'

select rental_date, return_date from rental limit 1;
--> for example, rental_date: '2005-05-24 22:54:33'

This happens in PgAdmin and also in the query values returned by 
PQgetvalue(), with LibPq.
What defines this seconds precision?  Is it possible to find the number 
of fractional digits that will be returned in a datetime column, before 
calling PQgetvalue()?

Thanks in advance.

Luiz Fernandes

---

From: Tom Lane <[email-masked]>
Date: Sat, 17 Jul 2021 10:04:11 -0400

> 'rental_date' and 'return_date' without fractional digits.

Presumably this is just an artifact of the example data.  timestamptz_out
suppresses trailing fractional zeroes, regardless of the column's nominal
precision.  So there's no reason to think that one column is being treated
differently from the others.

			regards, tom lane